如何解决与SQL Server的连接问题 [英] how to solve the connection problem with SQL Server

问题描述

我正在尝试使用 pyodbc 和django连接到我的SQL Server 2016数据库.

I am trying to connect to my SQL Server 2016 Database using pyodbc with django.

在SQL配置管理器中，我将所有网络配置都设置为 Enabled

In the SQL configuration manager i have all the network configurations as Enabled

• 共享内存
• 命名管道
• TCP/IP

防火墙已关闭

我尝试使用 localhost ，它工作正常，但是当我尝试连接到同一网络上的服务器时，它不起作用并显示以下错误:

I tried using localhost and it worked fine but when I tried to connect to a server on the same network it did not work and displayed the below error:

/connect/

OperationalError at /connect/

('08001'，'[08001] [Microsoft] [SQLServer Native Client 11.0]命名管道提供程序:无法打开连接到SQL Server [53].(53)(SQLDriverConnect);[08001][Microsoft] [SQL Server Native Client 11.0]登录超时已过期(0)；[08001] [Microsoft] [SQL Server Native Client 11.0]与网络相关或建立连接时发生了特定于实例的错误到SQL Server.找不到服务器或无法访问服务器.检查是否实例名称正确，并且如果将SQL Server配置为允许远程连接.有关更多信息，请参见SQL Server联机丛书.(53)')

('08001', '[08001] [Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [53]. (53) (SQLDriverConnect); [08001] [Microsoft][SQL Server Native Client 11.0]Login timeout expired (0); [08001] [Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (53)')

我试图检查服务器上的ODBC驱动程序，它显示了以下驱动程序:

I tried to check the ODBC Driver on the server and it displays these drivers:

• 用于SQL Server的ODBC驱动程序13
• SQL Server
• SQL Server本机客户端11.0

我尝试了两个，但都没有成功.

and I tried both of them but no success.

views.py

from django.shortcuts import render
import pyodbc
# from .models import Artist
# Create your views here.

def connect(request):
    conn = pyodbc.connect('Driver={ODBC Driver for SQL Server};'
                      'Server=AB-INT-SQL;'
                      'Database=testDB;'
                      'Trusted_Connection=yes;')

    cursor = conn.cursor()
    c = cursor.execute('SELECT * FROM Artist')

    return render (request,'connect.html',{"c":c})

connect.html

{% for row in c %}
    {{ row }}
{% endfor %}

推荐答案

如果服务器上仅安装了1个SQL实例，只需使用 telnet HOSTNAME 1433 进行快速检查，以确认SQL Server接受了默认端口号上的通信.

If there is only 1 SQL instance installed on the server, just do quick check with telnet HOSTNAME 1433 to confirm SQL Server accepting the communication on default port number.

如果 telnet 无法正常工作，请在防火墙中(通过防火墙高级设置)使用端口号为 1433和1434 的防火墙添加新规则，即使已关闭防火墙.仍然不起作用，请重新启动SQL Service，然后查看一条消息的SQL错误日志(以下消息必须在重新启动时间之后出现)

if telnet doesn't work, add new rule in fire-wall (via Firewall advanced settings) with port numbers 1433 and 1434 even though have turn-off the firewall. Still doesn't work, restart SQL Service and then have a look at SQL error log for a message (following message must appear after restart time)

服务器正在监听['any'ipv4 1433].

Server is listening on [ 'any' ipv4 1433].

除此之外(一旦进行telnet测试即可)，我相信您需要在连接字符串中使用 driver 作为"Driver = {SQL Server Native Client 11.0};" 您可能已经尝试过了.

Aside from this (once telnet test works), i believe, you need use the driver in connection string as "Driver={SQL Server Native Client 11.0};" which you may have already tried.

SQL错误日志屏幕截图

SQL Error log screenshot

这篇关于如何解决与SQL Server的连接问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！