40

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?

meet-bhagdev
  • 2,608
  • 18
  • 22
Montoya
  • 2,819
  • 3
  • 37
  • 65
  • If you happen to not be on Ubuntu, or some other "supported" distro, you can try with the `freetds` driver. Here's an example: https://x-team.com/blog/using-ms-sql-dynamic-ports-in-php-7-with-odbc/ Sometmes also passing the server port is helpful. – Tomasz Gandor Nov 07 '19 at 20:14

7 Answers7

45

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

Jason
  • 1,974
  • 24
  • 19
  • 11
    you save my life – GiovaniSalazar Jun 16 '19 at 05:06
  • 4
    That worked, before doing that I followed https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017 to install odbc driver. – Sachin G. Aug 02 '19 at 07:24
  • 13
    just use "{ODBC Driver 17 for SQL Server}" would be fine – ws_ Jul 31 '20 at 03:13
  • 3
    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 @ws_ – Sedat Kestepe Dec 28 '20 at 13:57
  • Do not to forget to allow the web app ip address to the sql server in Azure – Julio S. Aug 13 '21 at 15:51
31

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

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
meet-bhagdev
  • 2,608
  • 18
  • 22
  • What Django adapter are you using? In the settings.py, you need to specify Driver: ODBC Driver 13 for SQL Server – meet-bhagdev Nov 08 '16 at 21:00
  • 1
    Thanks, I finally solved my problem -- needed to downgrade pyodbc-azure to 1.9 from 1.10 – Hack-R Nov 08 '16 at 21:08
  • 4
    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: https://msdn.microsoft.com/en-us/library/hh568454(v=sql.110).aspx – Alexander Amelkin Jan 24 '17 at 12:25
  • Thanks Alexander! That is correct. This was a temporary workaround, people should follow the steps in the link you mentioned. – meet-bhagdev Jan 24 '17 at 22:38
  • 6
    that sh script is broken! – thang Feb 01 '18 at 03:47
  • Changing from `Driver={SQL Server}` to `Driver={ODBC Driver 13 for SQL Server}` works for me, thanks! – mikebridge Feb 23 '18 at 00:38
  • I used this link to install SQL Driver 17 for Ubuntu 18.04 and it worked! https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017#microsoft-odbc-driver-131-for-sql-server – Sincole Brans Oct 05 '19 at 15:52
14

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}
Mohideen bin Mohammed
  • 18,813
  • 10
  • 112
  • 118
  • 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} – STEM FabLab Jan 29 '21 at 14:04
  • you save my life – GiovaniSalazar May 14 '21 at 04:57
  • ODBC library driver path changes with updates. you can use ```find /opt/microsoft/msodbcsql17/lib64 -name libmsodbcsql*``` to get it's path. – Benoni Aug 28 '21 at 13:09
1

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

Installing FreeTDS

Connecting to SQL-Azure using FreeTDS

Niv Cohen
  • 1,078
  • 2
  • 11
  • 21
1

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.

Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
Chandan Kumar
  • 21
  • 3
0

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.

Casivio
  • 333
  • 7
  • 15
0

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
Peter Trcka
  • 1,279
  • 1
  • 16
  • 21