是否可以在Ubuntu上使用Azure AD使用sqlalchemy和Python连接到Azure SQL? [英] Is it possible to use Azure AD on Ubuntu for connecting to Azure SQL using sqlalchemy and Python?

问题描述

是否可以在Ubuntu上使用Azure AD连接到Azure SQL?

也就是说，可以在Python的sqlalchemy中使用 trusted_connection = True 吗?

 #创建引擎engine = sqlalchemy.create_engine('mssql://* server_name */* database_name *?trusted_connection = yes') 

在Azure上，您可以创建具有托管身份的Linux VM，该Linux VM允许您使用Azure AD连接到Azure服务.在他们的文档中，我可以找到示例关于如何使用此方法连接到各种Azure服务的信息，但是，我看不到连接到Azure SQL数据库的示例.

我能找到的最接近的东西是

在此处查看完整教程 https://docs.microsoft.com/zh-CN/azure/app-service/app-service-web-tutorial-connect-msi

或者，您可以使用System.Data.SqlClient对象或新的跨平台Microsoft.Data.SqlClient实现类似的目的.

 写入详细信息创建SQL连接字符串"$ conn =新对象System.Data.SqlClient.SQLConnection$ DatabaseName ='主'$ conn.ConnectionString =数据源= $ SQLServerName.database.windows.net;初始目录= $ DatabaseName;连接超时= 30"$ conn.AccessToken = $($ SPNToken)$ conn写详细信息连接到数据库并执行SQL脚本"$ conn.Open()$ query ='选择@@ version'$ command =新对象-TypeName System.Data.SqlClient.SqlCommand($ query，$ conn)$ Result = $ command.ExecuteScalar()$结果$ conn.Close() 

上面的示例使用跨平台的.NET核心库.要从Python调用它们，可以尝试使用pythonnet库.

https://github.com/pythonnet/pythonnet

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.

解决方案

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://docs.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

这篇关于是否可以在Ubuntu上使用Azure AD使用sqlalchemy和Python连接到Azure SQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！