Windows上的pymssql可以连接到本地SQL Server，但不能连接到Azure SQL [英] pymssql on Windows can connect to local SQL Server but not to Azure SQL

问题描述

尝试使用pymssql连接到Azure SQL DB(v12)， 我可以使用pymssql连接查询并写入我的本地MSSQL实例.

Trying to connect to an Azure SQL DB (v12) using pymssql, I am able to connect query and write to my local MSSQL instance using pymssql.

我收到"Adaptive Server连接失败"失败的错误，我的研究似乎指向FreeTDS，但是我没有在计算机上安装FreeTDS.

I am getting an "Adaptive Server connection failed" failed error and my research seems to point to FreeTDS, but I have not installed FreeTDS on my machine.

使用tsql实用工具，我可以连接到Azure SQL实例

Using the tsql utility, i am able to connect to the Azure SQL instance

为什么我无法使用pymssql连接?

Why would I fail to connect using pymssql?

pymssql连接字符串

pymssql connection string

import pymssql
conn = pymssql.connect(server='<severname>.database.windows.net',user='mickey@in4live', password='<pass>', database='tesdb')
cursor = conn.cursor()
cursor.execute("select 1")

输出

conn = pymssql.connect(server ='.database.windows.net'，user ='mickey @'，database ='tesdb') pymssql.connect中的文件"pymssql.pyx"，行641(pymssql.c:10824) pymssql.OperationalError:(20002，'DB-Lib错误消息20002，严重性9:\ nAdaptive Server连接失败(:1433)\ n'

conn = pymssql.connect(server='.database.windows.net',user='mickey@', database='tesdb') File "pymssql.pyx", line 641, in pymssql.connect (pymssql.c:10824) pymssql.OperationalError: (20002, 'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (:1433)\n'

但是

tsql -H <servername>.database.windows.net -p 1433 -U mickey@<servername> -P <pass>

连接成功

推荐答案

使用pip install pymssql安装的pymssql的默认Windows构建是静态链接到FreeTDS，因此pymssql二进制文件(.pyd文件)包括建立未加密连接所需的FreeTDS组件.这足以连接到不需要安全连接的许多(大多数?)本地SQL Server安装.

The default Windows builds of pymssql that get installed using pip install pymssql are statically linked to FreeTDS, so the pymssql binaries (.pyd files) include the required FreeTDS components to establish unencrypted connections. This is sufficient for connecting to many (most?) local SQL Server installs where secure connections are not required.

但是，与Azure SQL数据库的所有连接都需要加密(参考:

However, all connections to Azure SQL Database require encryption (ref: here), so a basic pip install pymssql on Windows will not be able to connect to an Azure SQL database. Instead, we need to install pymssql with SSL support as described in the following MSDN article

步骤1:为pymssql Python开发配置开发环境

其中步骤1"是指Microsoft的Azure教程的第一步

where "Step 1" refers to the first step in Microsoft's tutorial for Azure

使用Python连接到SQL数据库

这篇关于Windows上的pymssql可以连接到本地SQL Server，但不能连接到Azure SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！