Connect to sql server from linux environment using putty private key, sqlalchemy and pyodbc

Question

Hi I am trying to read a dataframe from sql server from a linux(ubuntu server). The file(gscm.ppk) has the admin keys in it

I have used the following format to connect to sql server:

import urllib.parse
import pandas as pd
import sqlalchemy as sa

params = urllib.parse.quote_plus(
    "DRIVER={SQL Server Native Client 11.0};"
    "SERVER=dagger;"
    "DATABASE=test;"
    "UID=ubuntu;"
)
engine = sa.create_engine("mssql+pyodbc:///?odbc_connect={}".format(params))
df = pd.read_sql("SELECT * FROM ROBALS", engine)

I keep getting this error:

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('01000', "[01000] [unixODBC] 
[Driver Manager]Can't open lib 'SQL Server' : file not found (0) 
(SQLDriverConnect)")
(Background on this error at: https://sqlalche.me/e/14/dbapi)

I have downloaded the driver that goes with Ubuntu 20.04

Answer 1

Your code looks mostly fine except that Driver parameter looks like it's being ignored/ isn't quite right. I couldn't tell if it had to do with white spacing in code, but if I run this code on my instance I'll get a similar error, but it will refer to the right instance.

I updated Driver to be the path, per this answer and I didn't have a lookup failure, so something like the below, if you installed the odbcsql18 drivers would probably work as a substitute.

"DRIVER=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.3.so.1.1;"

I've installed these newer drivers on a container using

curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/debian/11/prod.list > /etc/apt/sources.list.d/mssql-release.list
apt-get update && ACCEPT_EULA=Y apt install unixodbc jq msodbcsql18 mssql-tools18 unixodbc-dev -y