Cannot connect to the Azure SQL Server with python

1
votes

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.

2
pythonazure-sql-databasepyodbc
connection string isn't correct, review this: docs.microsoft.com/en-us/azure/sql-database/… - Tim Mylott
running this code gives the same error - Nikolay Oleynikov
Have you add your IP to firewall's allowed list in Azure portal? It seems to be a connection error. - Jack Jia

2 Answers

2
votes

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

The possible reasons for this are as follows:

  1. Wrong server address.

  2. Your local network issue. For example, your local network may ban traffics over port 1433.

  3. 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:

  1. 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
  1. Install pyodbc module in python
pip install pyodbc

  1. Add my IP to allowed list in Azure Portal

  2. 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:

enter image description here

1
votes

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