Connecting to SQL Server named instance from Linux using pyodbc

Question

I'm currently trying to connect to a SQL Server (that I don't have visibility into, but have credentials for) using PyODBC. The code that I have works on my Windows desktop, but does not work when moved onto my RedHat Linux machine. I need it on Linux in support of a project.

Here's what I have:

server = 'tcp:myserver\inst1'
database = 'mydatabase'
username = 'myusername'
password = 'mypassword'

cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=' + server + ';DATABASE=' + database + ';UID=' + username + ';PWD=' + password)

And here is the error I'm getting:

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

The one difference between the Windows version and Linux version is the driver portion. Windows uses '{SQL Server}' while the Linux version uses '{ODBC Driver 13 for SQL Server}'.

In my /etc/odbcinst.ini file, I have the following information:

[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.1
UsageCount=1

Anyone have any suggestions?

Darlenadarlene

Have you followed the instructions on installing the ODBC driver here? – Allin

Have you tried the machine's FQDN for the server name? – Psilomelane

Make sure your connection string is correct. Also, try to check the firewall for SQL server whether will you be able access it from your linux machine. – Nip

I got this error when I supplied the port incorrectly. The port should be separated from the server with a comma, e.g. tcp:myserver\inst1,12345 – Immitigable

Answer 1

Unlike the Windows versions of Microsoft's ODBC Drivers for SQL Server, the Linux versions of those drivers are unable to resolve SQL Server instance names. So on a Windows client we can use the following (provided that the SQL Browser service is running on the server)

cnxn = pyodbc.connect(
    "Driver=ODBC Driver 17 for SQL Server;"
    r"Server=myserver\SQLEXPRESS;"
    # and so on
)

but that won't work on Linux. However we can use the sqlserverport module (which I maintain) to retrieve the port number from the SQL Browser service:

import pyodbc
import sqlserverport

servername = "myserver"
serverspec = f"{servername},{sqlserverport.lookup(servername, 'SQLEXPRESS')}"
conn = pyodbc.connect(
    "Driver=ODBC Driver 17 for SQL Server;"
    f"Server={serverspec};"
    # and so on

Capricecapricious

Answer 2

Use the driver path instead of the driver name. In your example take the full /opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.1

Zircon

This is not necessary if unixODBC is configured correctly (with a valid entry in the odbcinst.ini file that unixODBC is using). It also means that if you upgrade to a newer release of the same driver you would have to edit the connection strings in all of your applications. – Capricecapricious

Answer 3

use IP address and port number instead of name/instancename. execute this query to have the real port number:

SELECT DISTINCT local_net_address, local_tcp_port FROM sys.dm_exec_connections

and then datasrc=N'192.168.1.112,61423'

Runt