Search code examples
pythonsql-serverazureazure-sql-databasepyodbc

Connect to Azure SQL database via pyodbc

I use pyodbc to connect to my local SQL database which works without issue.

SQLSERVERLOCAL='Driver={SQL Server Native Client 11.0};Server=(localdb)\\v11.0;integrated security = true;DATABASE=eodba;'
cnxn = pyodbc.connect(SQLSERVERLOCAL)   #works

I tries to connect to the Azure SQL database with:

SQLSERVERAZURE='Driver={SQL Server Native Client 10.0};Server=tcp:mydatbase.database.windows.net,1433;Database=mydb;Uid=myuser@myerver;Pwd=mypass;Encrypt=yes;Connection Timeout=30;'
cnxn = pyodbc.connect(SQLSERVERAZURE)   #works not

which gives me the error:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

Solution

  • Besides the suggestions that provided by meet-bhagdev who recommended to use the pymssql driver that mentioned in link, to resolve the error:

    Data source name not found and no default driver specified (0) (SQLDriverConnect)')

    that was encountered, please update your connection string as below.

    conn = pyodbc.connect('DRIVER={SQL Server};SERVER=yoursqlAzureServer.database.windows.net,1433', user='yourName@yoursqlAzureServer', password='Password', database='DBName')