1

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?

edgar piet
  • 67
  • 3
  • 11
  • 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 Feb 07 '17 at 07:08
  • Last night I changed and added a few more things by following the instructions on this [page] (https://github.com/lionheart/django-pyodbc/wiki/Mac-setup-to-connect-to-a-MS-SQL-Server. 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 Feb 07 '17 at 07:39
  • And it's Azure SQL DB v12 – edgar piet Feb 07 '17 at 07:46
  • 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](http://stackoverflow.com/questions/37771434/mac-pip-install-pymssql-error). Downgrading to an older version of FreeTDS through Brew unlink allowed me to install pymssql and connect to Azure. – edgar piet Feb 07 '17 at 09:17

1 Answers1

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!

mondieki
  • 1,771
  • 3
  • 16
  • 24