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?

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