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.
- 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()
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.
Thank you for your suggestion, but I get the exact same error message following these steps.
@Jon123 that's very strange, this code works very well on my computer. I tested in Windows and Ubantu. Maybe there's something wrong with you environment. Where do you run this code, if in company, please make sure you company firewall has opened port 1433.
Very strange indeed. I've added an outbound rule to my local firewall allowing all tcp connections to port 1433, but unfortunately this had no effect. I think there must be some administrator-controlled firewall setting either locally or at the Azure database that I'm not aware of.
@Jon123 Can you connect to the Azure SQL database with SSMS successfully?
Finally figured it out - it turns out the problem was that I was using SQL authentication instead of Active Directory authentication. Thank you for looking into it though!