英文:
Azure Synapse - Connect to Azure SQL from Notebook using Managed identity
问题
我尝试从 Azure Synapse 笔记本使用托管标识连接到 Azure SQL 数据库 xxx.database.windows.net。使用 msssaprkutils.credentitals.gettoken，我们可以获取特定资源（如存储帐户）的访问令牌。但似乎不可能将其用于连接到其他资源。
我们有哪些选项可以使用 Synapse 笔记本中的托管标识连接到其他服务？
英文:
I tried to connect to Azure SQL database xxx.database.windows.net from azure synapse note book using managed identity. Using the msssaprkutils.credentitals.gettoken, we can get the access token for specific resources like storage account. But it seems not possible to use the same for other resources.
What options do we have to connect to other services using the managed identity from Synapse notebook.
答案1
得分: 1
连接 Azure SQL 数据库并使用托管标识进行身份验证在 Synapse 笔记本中，以管理员身份登录 SQL 数据库，使用以下代码创建 Synapse 工作区的用户并添加 db_owner 角色：
CREATE USER [<synapseWorkspace>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER [<synapseWorkspace>];
创建 Azure SQL 数据库的托管标识身份验证的链接服务：
在 Synapse 笔记本中使用上述链接服务执行以下代码：
server = 'dbservere.database.windows.net'
Port = 1433
Database = "db"
jdbcUrl = f"jdbc:sqlserver://{server}:{Port};databaseName={Database};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30"
token=TokenLibrary.getConnectionString("AzureSqlDatabase1")
query = "(SELECT * FROM students) as tb"
conn_Prop = {
"driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver",
"accessToken" : token
}
df = spark.read.jdbc(url=jdbcUrl, table=query, properties=conn_Prop)
display(df)
这将成功使用托管标识身份验证连接到数据库。
英文:
To connect Azure SQL database with managed identity authentication in synapse notebook login as administrator into sql database create a user of synapse workspace and add db_owner role using below code:
CREATE USER [<synapseWorkspace>] FROM EXTERNAL PROVIDER
ALTER ROLE db_owner ADD MEMBER [<synapseWorkspace>];
Create linked service of Azure SQL database with managed Identity authentication:
Execute below code in synapse notebook by using above linked service:
server = 'dbservere.database.windows.net'
Port = 1433
Database = "db"
jdbcUrl = f"jdbc:sqlserver://{server}:{Port};databaseName={Database};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30"
token=TokenLibrary.getConnectionString("AzureSqlDatabase1")
query = "(SELECT * FROM students)as tb"
conn_Prop = {
"driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver",
"accessToken" : token
}
df = spark.read.jdbc(url=jdbcUrl, table=query, properties=conn_Prop)
display(df)
It will connect database successfully with managed identity authentication.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论