Pyodbc: Login Timeout Error

sql-server python-3.x ubuntu-16.04 pyodbc
Krishnang K Dalal picture Krishnang K Dalal · Apr 26, 2018 · Viewed 28.8k times · Source

I am trying to connect to MS SQL Server using pyodbc from a remote machine running Ubuntu 16.04.

import pyodbc 

conn = pyodbc.connect(r'DRIVER=ODBC Driver 17 for SQL Server; SERVER=xxxTest-SRV; PORT=51333; DATABASE=TestDB; UID=xxxx; PWD=xxxx;')

I'm getting following error:

pyodbc.OperationalError: ('HYT00', '[HYT00] [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

I tried using the server IP in the connection string but still no luck.

However, I am able to connect to using sqlcmd from the terminal
Following works:

sqlcmd -S xxxTest-SRV, 51333 -d TestDB -U xxxx -P xxxx

I didn't find any issue that gave an answer to my problem.

odbcinst.ini 

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-17.1.so.1.1
UsageCount=1

There's always seems to be an issue connecting to MS SQL Server using pyodbc from a linux machine. Is there a way to connect to SQL Server from Python. I'll appreciate your help in solving this error. Thank you.

[UPDATE]

As per the below answer, I updated the connection string. But, now I get following error:

pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.0.so.1.1' : file not found (0) (SQLDriverConnect)")

My odbcinst.ini file driver definition:

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.0.so.1.1
UsageCount=1

It has always been a nightmare to connect to MS SQL Server from a Linux machine. Can you please tell which pyodbc, unixODBC and Driver version is the most stable?

I have installed the driver following this Microsoft instructions. My pyodbc version is 4.0.23

Answer

Gord Thompson picture Gord Thompson · Apr 26, 2018

Microsoft's ODBC drivers for SQL Server do not use a PORT= parameter. The port number, if any, is appended to the server name/IP with a comma, e.g.,

SERVER=xxxTest-SRV,51333;

Related questions

Linux python3 - Can't open lib 'SQL Server'

I am trying to connect to an Microsoft Azure SQL server database. This is how i am trying to connect: conn = pyodbc.connect('DRIVER={SQL Server};SERVER=%s' % (self.config.get("Sql", "DataSource")), user= self.config.get("Sql", "UserId"), password=…

Read More
('HY000', 'The SQL contains 21 parameter markers, but 1 parameters were supplied')

I'm trying to read a csv file and upload in SQL. Here is the code. I'm getting this error "pypyodbc.ProgrammingError: ('HY000', 'The SQL contains 21 parameter markers, but 1 parameters were supplied')" My csv file has 21 columns. Do you know …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More