Is it possible to use Azure AD on Ubuntu for connecting to Azure SQL using sqlalchemy and Python?

Question

Is it possible to use Azure AD on Ubuntu for connecting to Azure SQL?

That is, it is possible to use trusted_connection=True in sqlalchemy in Python?

# Creating engine
engine = sqlalchemy.create_engine('mssql://*server_name*/*database_name*?trusted_connection=yes')

On Azure you can create a linux VM with a managed identity which allows you to connect to Azure services using Azure AD. In their documentation I can find examples of how to connect to various Azure services using this, however, I see no examples of connecting to a Azure SQL database.

The closest thing I can find is this, which is horribly convoluted.

Answer 1

Here is a doc - Tutorial: Use a Windows VM system-assigned managed identity to access Azure SQL, you need to get the access token with the MSI and use the token to call Azure SQL.

However, it looks like the doc is for Windows VM from the doc structure, I am not sure if it is suitable for Linux VM, you can have a try. If not work, it indicates not support the Linux VM.

Answer 2

Unfortunately the only option currently available (Jan 2020) to use a managed identity is to use the .NET libraries in System.Data.SqlClient or Microsoft.Data.SqlClient. The good news is they are cross platform so you can call them from Linux however to bridge the Python gap you will need to call the .NET libraries from Python.

First to get started on using the .NET Core runtime on Linux.

The key is to use the access token to create your connection string.

var conn = (System.Data.SqlClient.SqlConnection)Database.GetDbConnection();
conn.AccessToken = (new Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").Result;

See full tutorial here https://learn.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi

Alternatively you can use the System.Data.SqlClient object or the new cross platform Microsoft.Data.SqlClient to achieve something similar.

Write-Verbose "Create SQL connectionstring"
$conn = New-Object System.Data.SqlClient.SQLConnection 
$DatabaseName = 'Master'
$conn.ConnectionString = "Data Source=$SQLServerName.database.windows.net;Initial Catalog=$DatabaseName;Connect Timeout=30"
$conn.AccessToken = $($SPNToken)
$conn

Write-Verbose "Connect to database and execute SQL script"
$conn.Open() 
$query = 'select @@version'
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $conn)     
$Result = $command.ExecuteScalar()
$Result
$conn.Close() 

The above example is using the cross platform .NET core libraries. To call them from Python one can try to use the pythonnet library.

https://github.com/pythonnet/pythonnet