Connect to Azure SQL Database in a Jupyter Notebook using Python

4 minute read

Jupyter Notebooks, commonly used by Data Scientists and Students, allow you to run code, such as Python and PowerShell, inside a Notebook format and display the output inside the notebook; this is useful for teaching a subject or displaying up-to-date information.

I am not a python or Jupyter expert, so this article will be brief on how I was able to connect to an Azure SQL Database using Azure Active Directory authentication and run a query.

To run a Jupyter Notebook, you can install Anaconda and then use that to download Juypter to run the notebooks from a locally (or server) hosted web-based interface.

However, today I will be using Visual Studio Code with the Jupyter extension on a windows endpoint.

Make sure you install:

Note: Jupyter notebook extensions end in ‘*.ipynb’.

Once all the prerequisites are installed, it’s time to create the Notebook.

  1. Open Visual Studio Code
  2. Click File, New File
  3. Select Jupyter Notebook
  4. Press + Code (to add a Code snippet)
  5. First, we need to import the pyodbc library:

    import pyodbc

Then we need to add the snippet to connect to the SQL database (this can be in a separate Codeblock or the same code block, as long as the import is run before the SQL connection is made - make sure you update the server and database variables, to match your environment!):

#Connection to SQL database

server = '' 
database = 'DBNAME' 
username = '[email protected]' 
password = 'password' 

connection = pyodbc.connect('Driver={ODBC Driver 18 for SQL Server};Server='+server+',1433;Database='+database+';Uid='+username+';Pwd='+password+';Encrypt=yes;TrustServerCertificate=no;Connection Timeout=180;Authentication=ActiveDirectoryInteractive')
cursor = connection.cursor()

The ‘Authentication=ActiveDirectoryInteractive’ parameter as part of the Connection string will prompt an interactive Azure Active Directory prompt to display and ask for credentials to be logged in; this includes MFA support. Using this method, the username and password variables are simply placeholders.

If you want to hardcode credentials into the Notebook (not recommended), you can remove the ‘Authentication=ActiveDirectoryInteractive’ section and enter the credentials into the username and password field.

Now that we have connected to the database, let us run a test query to obtain the SQL version:

#Sample select query
cursor.execute("SELECT @@version;")
row = cursor.fetchone()
while row:
row = cursor.fetchone()

Jupyter python SQL connection

Congratulations, you have successfully connected to an Azure SQL database and ran a query against the database.

If the connection to SQL appears to be stalling, check to make sure the Azure authentication window, isn’t hidden behind another window.

A GIST has been created, with the code as well, in case issues are copied from the website.



Leave a comment