Unable to connect to Azure SQL Database via Python ODBC Driver Issue

0
votes

I am trying to connect to azure sql database. But somehow I keep getting the following error:

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

I have ODBC Driver 17 installed.

here is my code: 

import pyodbc
server = 'mftaccountinghost.database.windows.net'
database = 'mft_accounting'
username = 'localhost'
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.expense_zoho")
row = cursor.fetchone()
while row:
    print (str(row[0]) + " " + str(row[1]))
    row = cursor.fetchone()

Here is my connection string on Azure SQL Database Connection String

Does anyone have an idea why the error pops up?

Thanks.

2
odbcazure-sql-databasepyodbc
did you enable connections from wherever you are connecting on the Azure SQL firewall? – 4c74356b41
Does the connection string in the screenshot works for you? If so, then why aren't you including the other connection string parameters like Encrypt=yes when you build it in your Python code? – Gord Thompson
@4c74356b41 yes, I did – Nikolay Oleynikov
@GordThompson I used the whole string, it didn’t work either – Nikolay Oleynikov

2 Answers

0
votes

Please make sure you have created a SQL login with permission to connect to the database. 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).

Try to connect using database tools like SSMS.

0
votes

Make sure you have install the right Python SQL Driver.

Please reference this tutorial: Quickstart: Use Python to query an Azure SQL database.

Here's my example pyodbc code and it works well in my window.

import pyodbc
server = 'sqlserverleon.database.windows.net'
database = 'Mydatabase'
username ='ServerAdmin'
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 TEST6")
row = cursor.fetchone()
while row:
    print (str(row[0]) + " " + str(row[1]))
    row = cursor.fetchone()

Hope this helps.