Unable to connect to remote SQL database using pyodbc

Question

our organization has a remote SQL database which I am trying to connect to using pyodbc.

Below is the test code:-

import pyodbc
                      
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=<name>.org;DATABASE=<dbname>;UID=<username>;PWD=<pwd>;PORT=<port>;')

However, I am unable to connect and get the below error when I try to connect using pyodbc.

pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.(17) (SQLDriverConnect); [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (53); [08001] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute (0)')

I also tried using another tool to connect to remote database (HeidiSQL) and this work correctly. I am able to connect to the database.

But I do not understand why I am unable to connect using pyodbc. I also tried using the IP address instead of the server name.

so I also tried the below in pyodbc connect:-

SERVER=<name>.org,<port>

SERVER=<IP address>,<port>

SERVER=<IP address>

I also updated the ODBC SQL Server Driver.

I also tried with MySQL ODBC 3.51 driver :-

cnxn = pyodbc.connect('DRIVER={MySQL ODBC 3.51 Driver};SERVER=<name>.org;DATABASE=<dbname>;UID=<username>;PWD=<pwd>;PORT=<port>;')

but then I get error :

pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

When I try with SQL Server Native Client 11.0 Driver I get error :

cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER=<name>.o`rg;DATABASE=<dbname>;UID=<username>;PWD=<pwd>;PORT=<port>;')

pyodbc.OperationalError: ('08001', '[08001] [Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [53]. (53) (SQLDriverConnect); [08001] [Microsoft][SQL Server Native Client 11.0]Login timeout expired (0); [08001] [Microsoft][SQL Server Native Client 11.0]Invalid connection string attribute (0); [08001] [Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (53)')

I also used this guide Microsoft guide connecting to SQL using pyodbc

Since I am using docker container for the python application, I hope that this would work to connect with the database.

Could someone please suggest what could be the issue OR how could I solve this? Any suggestions are most welcome.

I don't understand German, so can't be sure, but that image suggests you are (successfully) connecting to a MySQL instance, yet your code is attempting to connect to a SQL Server instance; the technologies are completely different. — Larnu
"Since we are using docker container, I hope that this would work." SQL Server in Docker, or the Python app in Docker? Also; what happens if you ping the SQL Server address? — gvee
@gvee It works fine when I try to ping with: ping <name>.org . I get reply like reply from IP address. and the python app in Docker, I want to connect this to the MySQL database. — aniltilanthe
Check the list returned by pyodbc.drivers() to see what ODBC drivers are available to your Python app. — Gord Thompson

aniltilanthe aniltilanthe · Accepted Answer · 2021-03-15T17:32:54

As commented/answered by @Gord Thompson and @larnu, @gvee

I checked list returned by pyodbc.drivers()

import pyodbc
print(pyodbc.drivers() )

Where it returned

['SQL Server', 'SQL Server Native Client 11.0', 'SQL Server Native Client RDA 11.0', 'ODBC Driver 17 for SQL Server', 'PostgreSQL ANSI(x64)', 'PostgreSQL Unicode(x64)', 'Amazon Redshift (x64)', 'Microsoft Access Driver (*.mdb, .accdb)', 'Microsoft Excel Driver (.xls, *.xlsx, *.xlsm, .xlsb)', 'Microsoft Access Text Driver (.txt, *.csv)', 'MySQL ODBC 8.0 ANSI Driver', 'MySQL ODBC 8.0 Unicode Driver']

I could connect with "MySQL ODBC 8.0 Unicode Driver"

cnxn = pyodbc.connect('DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=<name>.org;DATABASE=<dbname>;UID=<username>;PWD=<pwd>;PORT=<port>;')

Unable to connect to remote SQL database using pyodbc

1 Answers