29

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,443
  • 17
  • 21
Montoya
  • 2,479
  • 3
  • 27
  • 53
  • 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

4 Answers4

37

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

Jason
  • 1,712
  • 20
  • 18
  • 8
    you save my life – GiovaniSalazar Jun 16 '19 at 05:06
  • 4
    That worked, before doing that I followed https://docs.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
  • 6
    just use "{ODBC Driver 17 for SQL Server}" would be fine – ws_ Jul 31 '20 at 03:13
  • 2
    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
26

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

meet-bhagdev
  • 2,443
  • 17
  • 21
  • 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
  • 5
    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://docs.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
12

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
  • 16,635
  • 8
  • 97
  • 110
  • 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
  • 773
  • 1
  • 7
  • 19