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

Question

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=self.config.get("Sql", "Password"),
                        database=self.config.get("Sql", "Catalog"))

I am getting an error while excuting this line. The error:

pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found (0) (SQLDriverConnect)")

Can't figure why this is happening, Any idea?

Manila

If you happen to not be on Ubuntu, or some other "supported" distro, you can try with the freetds driver. Here's an example: x-team.com/blog/using-ms-sql-dynamic-ports-in-php-7-with-odbc Sometmes also passing the server port is helpful. – Asur

Answer 1

Update - December 2022

The current installation instructions for the ODBC driver are here

---

I also recommend you install the ODBC Driver and then try to use pyodbc. I am assuming you are on an Ubuntu 15.04+ machine.

To install the ODBC Driver follow the following instructions:

sudo su
wget https://gallery.technet.microsoft.com/ODBC-Driver-13-for-Ubuntu-b87369f0/file/154097/2/installodbc.sh
sh installodbc.sh

Once you do that, install pyodbc using pip and try the following script:

import pyodbc
server = 'tcp:myserver.database.windows.net'
database = 'mydb'
username = 'myusername'
password = 'mypassword'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute("SELECT @@version;")
row = cursor.fetchone()
while row:
    print row
    row = cursor.fetchone()

Let me know how that goes.

Cheers,
Meet

Dandiprat

What Django adapter are you using? In the settings.py, you need to specify Driver: ODBC Driver 13 for SQL Server – Dandiprat

Thanks, I finally solved my problem -- needed to downgrade pyodbc-azure to 1.9 from 1.10 – Razorback

Note that Microsoft's installodbc.sh installs unixodbc from source. This may not be what you want taking in account that unixodbc 2.3.1 is available from Ubuntu repositories starting with at least Xenial. The procedure for installing ODBC driver for many OSes is given here: msdn.microsoft.com/en-us/library/hh568454(v=sql.110).aspx – Hettie

Thanks Alexander! That is correct. This was a temporary workaround, people should follow the steps in the link you mentioned. – Dandiprat

that sh script is broken! – Shellyshelman

Changing from Driver={SQL Server} to Driver={ODBC Driver 13 for SQL Server} works for me, thanks! – Picro

I used this link to install SQL Driver 17 for Ubuntu 18.04 and it worked! learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/… – Greenhouse

Answer 2

replace DRIVER={SQL Server} with DRIVER={/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.0.so.1.1}

Stem

That worked, before doing that I followed learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/… to install odbc driver. – Eu

just use "{ODBC Driver 17 for SQL Server}" would be fine – Drabbet

I ended up here, updating this value in my SQLAlchemy connection URL: engine = create_engine("mssql+pyodbc://" + uname + ":" + pword + "@" + server + "/" + dbname + "?driver=ODBC+Driver+17+for+SQL+Server") Thanks and thanks @Drabbet – Registered

Do not to forget to allow the web app ip address to the sql server in Azure – Litman

{ODBC Driver 17..} did not work, no matter what. Adjusting this very method to a specific driver lib release you have installed fixed everything right away. – Thymelaeaceous

Answer 3

Download Dependencies depends on your platform, (for other OS Download your Dependencies)

This example for Ubuntu:

# sudo su 
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

#Download appropriate package for the OS version
#Choose only ONE of the following, corresponding to your OS version

#Ubuntu 14.04
# curl https://packages.microsoft.com/config/ubuntu/14.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

#Ubuntu 16.04
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

# #Ubuntu 18.04
# curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

# #Ubuntu 18.10
# curl https://packages.microsoft.com/config/ubuntu/18.10/prod.list > /etc/apt/sources.list.d/mssql-release.list

# #Ubuntu 19.04
# curl https://packages.microsoft.com/config/ubuntu/19.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

exit
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install msodbcsql17
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo apt-get install unixodbc-dev

and then change,

DRIVER={/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.0.so.1.1} 

DRIVER={ODBC Driver 17 for SQL Server}

Baran

These instructions worked for me (running Ubuntu 20.04 under Windows Subsystem for Linux (WSL2)) however I only I needed to set DRIVER={ODBC Driver 17 for SQL Server}. I received an error if I tried DRIVER={/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.0.so.1.1} – Twoply

you save my life – Illtimed

ODBC library driver path changes with updates. you can use find /opt/microsoft/msodbcsql17/lib64 -name libmsodbcsql* to get it's path. – Brubaker

Answer 4

Check those links. It solved my problems which were kind of similar.

Installing FreeTDS

Connecting to SQL-Azure using FreeTDS

Upshaw

Answer 5

If you are using an offline REHL server, then follow the below method to setup connection to Microsoft SQL Server.

Download UNIXODBC & MSSQLTools packages—e.g., unixODBC-2.3.7-1.rh.x86_64.rpm/mssql-tools-17.9.1.1-1.x86_64.rpm—from https://packages.microsoft.com/rhel/, as per your REHL version.

Place downloaded files on the REHL server via winscp or any ssh client.

Install these two files in sequence given below:

yum localinstall unixODBC-2.3.7-1.rh.x86_64.rpm
yum localinstall mssql-tools-17.9.1.1-1.x86_64.rpm)

Go to the installation folder, and copy the path as shown in e.g.,

/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.9.so.1.1

Put this path in code:

driverpath = r"/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.9.so.1.1"

Your problem will get solved.

Iggy

Answer 6

I see that this post has an accepted answer but it did not work for me running Python 3.8 on Ubuntu 20.04. I tried several things but I think that ultimately, it was a security issue having to do with the openSSL package. Here are the steps I recommend.

Check the openSSL version. Do this in the Linux console

openssl version

The problematic version was 1.1.1f on my system. If that is the version you have, update it with these commands

wget https://www.openssl.org/source/openssl-1.1.1p.tar.gz -O openssl-1.1.1p.tar.gz
tar -zxvf openssl-1.1.1p.tar.gz
cd openssl-1.1.1p
./config
make
sudo make install
sudo ldconfig
openssl version

Then you have to edit /etc/ssl/openssl.cnf . Put this in the beginning

openssl_conf = default_conf

And this at the end

[ default_conf ]

ssl_conf = ssl_sect

[ssl_sect]

system_default = system_default_sect

[system_default_sect]
MinProtocol = TLSv1.2
CipherString = DEFAULT:@SECLEVEL=0

Like I said, that worked for me. Hopefully it can help someone else too.

Intense

Answer 7

Hi I am using this snippet, which searches and return lastest available version of ODBC Driver, or raises an error:

def select_driver():
    """Find least version of: ODBC Driver for SQL Server."""
    drv = sorted([drv for drv in pyodbc.drivers() if "ODBC Driver " in drv and " for SQL Server" in drv])
    if len(drv) == 0:
        raise Exception("No 'ODBC Driver XX for SQL Server' found.")
    return drv[-1]

print(select_driver())  # ODBC Driver 17 for SQL Server

Mcatee

Answer 8

For ubuntu 23.04 above . Mine was ubuntu 23.10 and this worked

First check the version of your ubuntu : lsb_release -a

And use this shell file and run it :

curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc

curl https://packages.microsoft.com/config/ubuntu/23.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list

sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools18
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo apt-get install -y unixodbc-dev

save the content in a file named setup_mssql.sh and run it by typing this in command.

 ./setup_mssql.sh 

And after that replace the connection string with "driver": "{ODBC Driver 18 for SQL Server}"

Carman