Connecting to Azure SQL Server via Jupyter (issues!)

0
votes

I'm trying to connect to an Azure SQL Server instance from a laptop with Jupyter installed. Fairly new at this but having issues which are probably simple to resolve.

I've installed 64bit Python 3.7, Jupyter and AMD64 v17 pyodbc via pip. However when I'm trying to connect via Jupyter I just get either connection or driver issues, not quite sure.

Below is the code I'm trying to run to connect and return a simple top 10 rows query.

import pyodbc
server = 'xxxsqlserver.database.windows.net'
database = 'xxx.dbo.table'
username = 'user'
password = 'password'
driver='{ODBC Driver 17 for SQL Server}'
cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute("SELECT TOP 10 * FROM xxx.dbo.table")
row = cursor.fetchone()
while row:
    print (str(row[0]) + " " + str(row[1]))
    row = cursor.fetchone()

Below is the error message I get:

InterfaceError Traceback (most recent call last) in 5 password = 'password' 6 driver='{ODBC Driver 17 for SQL Server}' ----> 7 cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) 8 cursor = cnxn.cursor() 9 cursor.execute("SELECT TOP 10 * FROM xxx.dbo.table")

InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

If I change the driver to 13 I get a different error

InterfaceError: ('28000', '[28000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Login failed for user \'user\'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Cannot open database "xxx.dbo.table" requested by the login. The login failed. (4060); [28000] [Microsoft][ODBC Driver 13 for SQL Server]Invalid connection string attribute (0); [28000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Login failed for user \'user\'. (18456); [28000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Cannot open database "xxx.dbo.table" requested by the login. The login failed. (4060); [28000] [Microsoft][ODBC Driver 13 for SQL Server]Invalid connection string attribute (0)')

Feels like the driver isn't doing something quite right or I've got a misalignment somewhere. Note I didn't install any version 13 drivers but do have SSMS installed which put some in, and this connects to the Azure platform just fine.

Any ideas?

Thanks.

2
pythonazurejupyter

2 Answers

0
votes

Hi there from the looks of things, this may be because your ODBC drivers are not installed properly. It is mostly likely because your are not formatting the string properly. So, here is a comprehensive list of what you need to do:

  1. Install the correct ODBC drivers to connect with SQLServer

This depends on your operating system of course, if you have MacOS, you can go ahead and install it using homebrew like so:

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
brew install msodbcsql17 mssql-tools

If you do not know what homebrew is, please take a look at the homebrew official website. If this is not your operating system, then take a look at OS specific instructions in this guide.

  1. Once you have done this, make sure to follow the instructions at the end of the installation, where they tell you what changes you need to make to your dotfiles in order to get ODBC working properly.

  2. Make sure to get the connection string properly. The connection string is located in the "Connection Strings" section of your SQLServer database, and under ODBC:

Connection String Image

  1. Once you have the connection string, use fstrings or the .format() function on a string to replace the section of the string that says Pwd={your_password_here}

This should do the trick. However, if it does not then you need to check whether you have the correct username, password and table. If you are sure that they are correct, then you might want to contact Support, since they might have changed the requirements for drivers to be a specific version for your database.

0
votes

I've also encountered the same error while connecting to Azure SQL database. The error disappeared once the driver name was changed from DRIVER={ODBC Driver 17 for SQL Server} to DRIVER={SQL Server}, for example:

import pyodbc
server = '<sql-server-name>.database.windows.net'
database = '<database>'
username = '<username>'
password = '<password>'
driver='{SQL Server}'

cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute("SELECT TOP 10 * FROM dbo.allOrders")
row = cursor.fetchone()
while row:
    print (str(row[0]) + " " + str(row[1]))
    row = cursor.fetchone()