Cannot connect to the Azure SQL Server with python

Question

I tried several times to connect to Azure SQL Database.

I used the following code:

import pyodbc
sqlConnection = pyodbc.connect(
                " Driver={ODBC Driver 17 for SQL Server};"
                "Server=tcp:mftaccountinghost.database.windows.net,1433;"
                "Database=mft_accounting;Uid=localhost;Pwd=#####;"
                "Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;");

cursor = sqlConnection.cursor()
cursor.execute("select * from dbo.error_bills_catch")
for row in cursor:
    print(cursor)


firstColumn = row[0]


cursor.close()

sqlConnection.close()

The error I get is following:pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection (0) (SQLDriverConnect)')

Can anyone maybe help?

This is my connection string: Driver={ODBC Driver 13 for SQL Server};Server=tcp:mftaccountinghost.database.windows.net,1433;Database=mft_accounting;Uid=localhost;Pwd={your_password_here};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;

I use MacOs and tried several time to reinstall the driver, but still without result.

Thank you.

connection string isn't correct, review this: learn.microsoft.com/en-us/azure/sql-database/… — Tim Mylott
– Tim Mylott, Commented Feb 25, 2020 at 16:07
Have you add your IP to firewall's allowed list in Azure portal? It seems to be a connection error. — Jack Jia
– Jack Jia, Commented Feb 25, 2020 at 19:06

Jack Jia · Accepted Answer · 2020-02-26 16:23:51Z

2

The error message Client unable to establish connection indicates a problem with the network.

The possible reasons for this are as follows:

Wrong server address.
Your local network issue. For example, your local network may ban traffics over port 1433.
Your IP was not add to Azure SQL firewall allowed list. Check tutorial: Use the Azure portal to manage server-level IP firewall rules

Update

I checked in my mac, and got a success. Here are my steps:

Install ODBC driver

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
brew install msodbcsql mssql-tools

Install pyodbc module in python

pip install pyodbc

Add my IP to allowed list in Azure Portal
Script:

import pyodbc

cnxn = pyodbc.connect(
    "Driver={ODBC Driver 17 for SQL Server};"
    "Server=tcp:jackdemo.database.windows.net,1433;"
    "Database=jackdemo;"
    "Uid=jack;"
    "Pwd=************;"
    "Encrypt=yes;"
    "TrustServerCertificate=no;"
    "Connection Timeout=30;")

cursor = cnxn.cursor()
cursor.execute("select * from Users")
row = cursor.fetchone()
while row:
    print(str(row[0]) + " " + str(row[1]))
    row = cursor.fetchone()

Result:

I successfully got all the users from table Users:

edited Feb 26, 2020 at 16:23

answered Feb 26, 2020 at 1:53

Jack Jia

5,5391 gold badge14 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jack Jia Over a year ago

@NikolayOleynikov Check my update. I got a success. You can take my steps as a reference.

Nikolay Oleynikov Over a year ago

I cleaned all the drivers and reinstalled the brew and then repeated your steps. It worked out. Thank you very much! :)

Martijn Pieters · Accepted Answer · 2020-09-19 07:10:15Z

1

Please make sure you have created a SQL login with permission to connect to the database because "localhost" may not be a valid login. You can try creating a contained database user as shown below and used that contained login to connect to the database. Have you created that login named localhost?

CREATE USER yourlogin WITH PASSWORD = 'Yh-EhGFjh+';
GO
exec sp_addRoleMember 'db_datareader', 'yourlogin'; 
GO

Make sure you have created a firewall rule as explained on this documentation and the server name and database names are correct (you have not misspelled them).

You should also verify you have installed the recommended Python driver from here.

Using that driver try the following lines of code.

import pyodbc
server = 'mftaccountinghost.database.windows.net'
database = 'mft_accounting'
username ='TheLoginYouCreatedAbove'
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()
cursor.execute("SELECT * FROM dbo.error_bills_catch")
row = cursor.fetchone()
while row:
    print (str(row[0]) + " " + str(row[1]))
    row = cursor.fetchone()

edited Sep 19, 2020 at 7:10

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

answered Feb 25, 2020 at 21:37

Alberto Morillo

15.8k3 gold badges29 silver badges32 bronze badges

2 Comments

Nikolay Oleynikov Over a year ago

tried your recommendation, still getting the same error. I guess I did not install the driver correctly, could you please recommend me the way to check if the driver is installed correctly? thx

Alberto Morillo Over a year ago

@NikolayOleynikov I use Windows and on Windows we have Driver Verifier learn.microsoft.com/en-us/windows-hardware/drivers/devtest/… but on MAC I don't know. I have never used a MAC.

Collectives™ on Stack Overflow

Cannot connect to the Azure SQL Server with python

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related