Warm tip: This article is reproduced from stackoverflow.com, please click
azure odbc pyodbc python-3.x sql-server

Azure server access issue despite fixing firewall settings

发布于 2020-03-27 10:17:56

I'm receiving an error when I try to connect to an Azure SQL server using pyodbc.

I found the connection parameters under 'Connection strings' for ODBC in the Azure portal. I have added my IP address in the firewall settings (and waited >1 hour) but this did not resolve the problem.

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()

I get the following error:

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.

Questioner
Jon Riege
Viewed
194
Leon Yue 2019-07-06 12:36
  1. Please ensure you have added client IP to firewall.

On the Azure SQL database overview, Set server firewall. enter image description here

Add client IP:

enter image description here

  1. 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()

This code example is provided by this documen: Create code to query your SQL database.

Updates:

The error has be solved..

Finally figured it out - it turns out the problem was that Jon123 was using SQL authentication instead of Active Directory authentication.

Hope this helps.