CODEWITHSUNDEEP
pythonsql-serverdatabasepyodbc
aniltilanthe
aniltilanthe

Reputation: 5245

Unable to connect to remote SQL database using pyodbc

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.

Connecting using HeidiSQL

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.

Upvotes: 0

Views: 1984

Answers (1)

aniltilanthe
aniltilanthe

Reputation: 5245

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>;')

Upvotes: 1

Related Questions