ProgrammingError connecting to Azure using python pypyodbc

Question

I am trying to connect to an Azure (mssql server) using Python and pypyodbc, but I am getting an error messsage that reads "pypyodbc.ProgrammingError: ('', 'SQL_ERROR')"

I have tried many different ways to make the connection, but it gives the same error everytime. Using Tableau for instance, I can get into the DB, so my credentials do work.

pypyodbc.connect("DRIVER={SQL Server};server='tcp:mssql-server',Database='analytics',uid='me@analytics',pw='secret'")

or

pypyodbc.connect('Driver={SQL Server};Server=tcp:mssqlserver;Database=analytics;UID=me@analytics;PWD=secret')

or

pypyodbc.connect('Driver={SQL Server};Server=tcp:mssqlserver;Database=analytics;Uid=me@analytics;Pwd=secret;')

And quite a few others that look like this, but with different quote types, different capitals etcetera, but it doesn't even matter which credentials I put in there, it just gives a programming error no matter what I do. Do I need to install other packages to make pypyodbc work maybe? I checked the package using 'dir' and that shows the entire content including the connect function.

Can anyone see what crucial part I am missing?

What type of mssql you used on Azure? Azure SQL Database or SQL Server on Azure VM? And please try to use Driver={ODBC Driver 13 for SQL Server}; instead of Driver={SQL Server};, and try to add these parameters Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30; to your current connection string. Any update, please feel free to let me know. — Peter Pan
– Peter Pan, Commented Feb 7, 2017 at 7:08
Last night I changed and added a few more things by following the instructions on this [page] (github.com/lionheart/django-pyodbc/wiki/…. After installing FreeTDS and unixodbc, the error message changed to and operational error: Can't open lib 'SQL Server' : file not found"). When I change the Driver to what you suggest I get the message Can't open lib 'ODBC Driver 13 for SQL Server' : file not found"). Could it be that I need to change a path somewhere to direct it to the correct driver? — edgar piet
– edgar piet, Commented Feb 7, 2017 at 7:39
Ok, in the end I fixed it differently. I could not get pypyodbc to work, so I took a look at pymssql to connect. I already tried this module before, but it failed to install and I couldn't figure out why until I found this. Downgrading to an older version of FreeTDS through Brew unlink allowed me to install pymssql and connect to Azure. — edgar piet
– edgar piet, Commented Feb 7, 2017 at 9:17

mondieki · Accepted Answer · 2017-03-07 16:27:20Z

1

Do you have msodbcsql installed? Install the ODBC driver, depending on your OS:

For MacOS

ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
brew tap microsoft/msodbcsql https://github.com/Microsoft/homebrew-msodbcsql
brew update
brew install msodbcsql
sudo pip install pyodbc

For Linux

sudo su
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql.list
exit
sudo apt-get update
sudo apt-get install msodbcsql mssql-tools unixodbc-dev-utf16
sudo pip install pyodbc

Get rid off the single quotes, replace SQL Server with ODBC Driver 13 for SQL and you should have this:

pypyodbc.connect("DRIVER={ODBC Driver 13 for SQL Server};SERVER=tcp:mssqlserver;DATABASE=analytics;UID=me@analytics;PWD=secret;TrustServerCertificate=no;Connection Timeout=60")

In the event of this error pypyodbc.OperationalError: (u'HYT00', u'[HYT00] [unixODBC][Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired'), check your code, it's probably a typo.

I'm positive this will fix your issue. If not, you can try out pyodbc instead. It works just as fine.

import pyodbc

server = 'tcp:myserver.database.windows.net'
database = 'myDB'
username = 'username'
password = 'Secretpassword'
connectObj = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

Cheers!

answered Mar 7, 2017 at 16:27

mondieki

1,9894 gold badges18 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mondieki Over a year ago

pip install pyodbc works on Ubuntu provided you ran this command sudo apt install unixodbc-dev

Collectives™ on Stack Overflow

ProgrammingError connecting to Azure using python pypyodbc

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related