9

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?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
sdberts
  • 85
  • 1
  • 1
  • 3
  • Have you followed the instructions on installing the ODBC driver [here](https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server#microsoft-odbc-driver-13-for-sql-server)? – Phonolog Apr 13 '18 at 06:57
  • Have you tried the machine's FQDN for the server name? – FlipperPA Apr 13 '18 at 19:54
  • 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. – deathstroke Oct 22 '19 at 05:37
  • 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` – Will Keeling Oct 16 '20 at 15:41

3 Answers3

0

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
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
-1

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

JeromeE
  • 449
  • 4
  • 6
  • 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. – Gord Thompson Apr 09 '22 at 22:41
-1

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'

jjdesign
  • 344
  • 2
  • 8