我尝试使用pyodbc连接到Azure SQL服务器时收到错误消息。
我在Azure门户的ODBC的“连接字符串”下找到了连接参数。我已经在防火墙设置中添加了我的IP地址(并等待了超过1个小时),但这不能解决问题。
import pyodbc
DRIVER = '{SQL Server}'
SERVER = 'tcp:[server name].database.windows.net'
PORT = '1433'
DATABASE = [database name]
USERNAME = [username]
PASSWORD = [password]
CONNECTION_STRING = f'DRIVER={DRIVER};PORT={PORT};SERVER={SERVER};DATABASE={DATABASE};UID={USERNAME};PWD={PASSWORD}'
cursor = pyodbc.connect(CONNECTION_STRING).cursor()
我收到以下错误:
ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
Cannot open server [server name] requested by the login. Client with IP address [my IP]
is not allowed to access the server. To enable access, use the Windows Azure
Management Portal or run sp_set_firewall_rule on the master database to create a
firewall rule for this IP address or address range. It may take up to five minutes
for this change to take effect. (40615) (SQLDriverConnect); [42000] [Microsoft]
[ODBC SQL Server Driver]Invalid connection string attribute (0); [42000] [Microsoft]
[ODBC SQL Server Driver][SQL Server]Cannot open server [server name] requested by the
login. Client with IP address [my IP] is not allowed to access the server. To enable
access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the
master database to create a firewall rule for this IP address or address range. It may
take up to five minutes for this change to take effect. (40615); [42000] [Microsoft]
[ODBC SQL Server Driver]Invalid connection string attribute (0)")
Update: I tried connecting using Visual Studio and it prompts me to create a new firewall rule. I choose 'Add my client IP' and click 'OK'. The prompt then immediately reappears. I tried clicking it a few times and the new rules do appear in the Azure portal, but I am still not able to connect through either Visual Studio or python.
Solution: I was using SQL authentication instead of Active Directory authentication. Solved the problem by adding AUTHENTICATION=ActiveDirectoryPassword to the connection string.
- Please ensure you have added client IP to firewall.
On the Azure SQL database overview, Set server firewall.
Add client IP:
- Please modify you code like this and try again:
import pyodbc
server = '<server>.database.windows.net'
database = '<database>'
username = '<username>'
password = '<password>'
driver= '{ODBC Driver 17 for SQL Server}'
cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
本文档提供了以下代码示例:创建代码以查询SQL数据库。
更新:
错误已解决。
最终弄清楚了-事实证明,问题是Jon123使用SQL身份验证而不是Active Directory身份验证。
希望这可以帮助。
感谢您的建议,但按照以下步骤操作,我会收到完全相同的错误消息。
@ Jon123很奇怪,此代码在我的计算机上运行良好。我在Windows和Ubantu中进行了测试。您的环境可能存在问题。如果在公司中,请在哪里运行此代码,请确保公司防火墙已打开端口1433。
确实很奇怪。我已经在本地防火墙中添加了出站规则,允许所有tcp连接到端口1433,但是不幸的是,这没有任何作用。我认为必须在本地或我不知道的Azure数据库中有一些由管理员控制的防火墙设置。
@ Jon123是否可以使用SSMS成功连接到Azure SQL数据库?
最终弄清楚了-原来问题是我使用的是SQL身份验证而不是Active Directory身份验证。谢谢您的关注!