Windows Bash - Python 3.5.2 pyodbc "Can't open lib 'ODBC Driver 13 for SQL Server'"

[MaximoTrinidad]

Please use the following bug reporting template to help produce actionable and reproducible issues. Please try to ensure that the reproduction is minimal so that the team can go through more bugs!

• A brief description
  I have installed all necessary components so I can do "import pyodbc" in python without any errors.
  I can execute the same code in Windows Python but I'm getting an error loading the ODBC driver after import pyodbc.

• Expected results
  No errors executing the code.

• Actual results (with terminal output if applicable)
  root@MTrinidadLT2:/mnt/c/Users/mtrinidad# python3
  Python 3.5.2 (default, Nov 17 2016, 17:05:23)
  [GCC 5.4.0 20160609] on linux
  Type "help", "copyright", "credits" or "license" for more information.

import pyodbc
cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=mtrinidadlt2\MSSQL2K16A;PORT=1443;DATABASE=sampledb1;UID=sa;PWD=xxxxxx')
Traceback (most recent call last):
File "", line 1, in
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 13 for SQL Server' : file not found (0) (SQLDriverConnect)")

• Your Windows build number
  Windows 10 Pro Insider Preview Build 15007.rs_prerelease.170170-1846

• Steps / All commands required to reproduce the error from a brand new installation
  After installing pyodbc, open Python3.
  Type the following code:
  import pyodbc
  cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=mtrinidadlt2\MSSQL2K16A;PORT=1443;DATABASE=sampledb1;UID=sa;PWD=xxxxxxx')

• Strace of the failing command

cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=mtrinidadlt2\MSSQL2K16A;PORT=1443;DATABASE=sampledb1;UID=sa;PWD=$Adm1n!')
Traceback (most recent call last):
File "", line 1, in
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 13 for SQL Server' : file not found (0) (SQLDriverConnect)")

• Required packages and commands to install
  sudo apt install python3-pip
  sudo pip3 install --upgrade pip
  sudo pip3 install --upgrade setuptools
  sudo pip3 install pylint
  sudo apt install unixodbc-dev
  sudo apt-get install unixodbc-bin
  sudo pip3 install pyodbc

See our contributing instructions for assistance.

[aseering]

Hi @MaximoTrinidad , thanks for the report! Question: I notice that you've detailed all of the steps to install unixodbc. Have you installed the SQL Server ODBC driver for Linux? If so, could you include the steps that you followed to configure it? (If not, could you give that a try?)

[MaximoTrinidad]

Hi @aseering,

I went ahead and install the msodbcsql driver. But I'm still getting the same error message.

-- MSSQL ODBC load for Ubuntu:
sudo su
sh -c 'echo "deb [arch=amd64] https://apt-mo.trafficmanager.net/repos/mssql-ubuntu-test/ xenial main" > /etc/apt/sources.list.d/mssqlpreview.list'
sudo apt-key adv --keyserver apt-mo.trafficmanager.net --recv-keys 417A0893
apt-get update
apt-get install msodbcsql

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

Any other ideas!
:)

[MaximoTrinidad]

@aseering ,

Question... Do I need to have SQL Server installed for pyodbc to work?

:)

[aseering]

Hi @MaximoTrinidad -- no, you don't need to have SQL Server itself installed. But you do need to install the SQL Server ODBC driver. It looks like the relevant documentation can be found here:

https://msdn.microsoft.com/en-us/library/hh568454(v=sql.110).aspx

Try following the Ubuntu 16.04 instructions. If you run into trouble, I would suggest trying to follow the instructions in an Ubuntu 16.04 VM. If it still doesn't work in a VM, then that's not a WSL problem; I'd suggest asking for help in the SQL Server MSDN forums. (You can try asking for help here too, but people there will know more about SQL Server than I do :-) )

If it does work in a VM but not in WSL, then post back here with the same level of detail as in your post above (but with the steps that work in Ubuntu and not in WSL); that's something that folks here can help with.

[aseering]

Incidentally, if you just want to connect to a SQL Server database and you don't need ODBC in particular, I would just use pymssql. That's Python's standard DBAPI driver for SQL Server. You're trying to access SQL Server through Python's ODBC compatibility layer, which I personally find to be slower and harder to set up. But if your app requires ODBC, then ODBC should certainly still work.

[MaximoTrinidad]

Interesting! Forgive me for missing some information about my environment.

I already got the Python PYODBC working in both Windows Python (both physical and VM) as well as Ubuntu 16.04 physical system.

So, in this scenario, I got WSL on a physical machine with the latest Windows 10 build 15007 (and I did rebuild my WSL).  I did the same steps to make pyodbc work on my other installations: Ubuntu and Windows which works great.

My problem is with WSL.  I thought that if I can do an "import pyodbc" in python3 then I was good to go. But with WSL, this is not the case.  I just need someone to duplicate my steps and verify if I'm missing something.

I'm interested to make WSL work so I can demo using Python in a cross-platform environments.

By the way, THANKS for telling about "pymssql". I was going the "pyodbc" way because of the samples provided by Microsoft for using Python for SQL Server vNext.

I appreciate the help!
:)

[aseering]

Regarding import pyodbc -- that is actually not sufficient even on regular Linux. I can confirm that your steps above do not work on a stock Ubuntu Linux system without installing additional packages and/or performing additional setup.

ODBC is not a SQL Server library. It is a generic wrapper library that can connect to many different databases (Oracle, MySQL, PostgreSQL, etc) but only if their specific drivers have been separately installed and configured. So you do always need to separately configure an ODBC driver for the specific database that you plan to use.

This is actually true on all platforms -- Windows, WSL, Linux, Mac, Solaris, etc. It's a basic property of how ODBC works. But if you, for example, install some SQL Server-related packages on your machine, one of those packages might do that configuration for you.

[MaximoTrinidad]

Awesome! And, I appreciate telling me about 'pymssql'.

It turned out, as of now I got 'pyodbc' working on Ubuntu 16.04.1 and Windows 10 with Python 3.5.2 but not on WSL.

Now, I got install 'pymssql'. It's working in Windows 10 and WSL like a charm with the only issue that I can't connect different SQL instances on the same machine (I already posted an issue on 'pymssql' on it).

So, I'm thinking using 'pymssql' for my presentation while I figured out what's wrong with 'pyodbc' in WSL.

Thanks for all the explanation!
:)

[MaximoTrinidad]

Very disappointing!! They closed my ticket without a quick answer on how to solved my 'pymssql' how to connected SQL Server instances ("computername\instance99") issue. And, my organization doesn't allow me to access Google forums. So, I got to wait until I get home to find a quick answer or it may be none.

@aseering, have you use 'pymssql' in a multiple SQL instance (on the same system) scenario. I just need to know how to build the string correctly.

About, the current issue with 'pyodbc' in WSL, I have a VM where I'm resetting bash in order the duplicate the steps and verify if I will get the same errors.

Thanks again!

[aseering]

Hi @MaximoTrinidad -- regarding pymssql -- I haven't used that configuration before, but have you looked through their documentation? One of their examples looks like it describes what you're trying to do:

http://pymssql.org/en/latest/pymssql_examples.html#connecting-using-windows-authentication

One thought: Are you using a raw string and/or escaping the backslash properly?

[MaximoTrinidad]

@aseering, In there samples, there are example of an instance server ("computername\instance99"), just simply "computername" which works. And, Yes! I'm using raw string "computername\instance1".  

[MaximoTrinidad]

@aseering,

As I venture thru trial-and-error. I was finally able to use 'pyodbc' in WSL by installing the 'msodbcsql' using 'sudo su':

sudo su
sudo apt install msodbcsql

At the same time, I realized (and have to accept) that, from WSL (or Linux) environment, I can only query to a local single instance SQL Server and not there are multiple instances.

Windows WSL (or Linux Ubuntu distro) to Windows - using Python either "import pyodbc" or "import pymsql":
Server = 'computername' - Good
Server = 'computername\instance' - not possible (will fail).

But, in Windows to Windows, in Python you can query multiple SQL instances:
Server = 'computername' - Good
Server = 'comutername\instance99' - Good

So, finally made pyodbc to work and connect to a SQL Server local instance. And, this is enough for me for my upcoming presentation and blog post.

Once again, Thanks @aseering!

This issues can be closed for now.

See images:

[gordthompson]

@aseering

[pymssql is] Python's standard DBAPI driver for SQL Server.

Not really. As far as I know, the Python Software Foundation has never officially endorsed pymssql.

However, pyodbc is officially supported by Microsoft.

As for this issue, the saga continues here.

[kenorb]

Re:

Can't open lib X: file not found

Solution is to create odbcinst.ini and define path to your SQL ODBC driver.

Follow-up: microsoft/homebrew-mssql-release/issues/3 & Microsoft/homebrew-mssql-preview/issues/4