英文:
c# - The 'ClientID' option must be provided - Azure API - Connect to AzureSQL using managed identity
问题
I have built an API as an Azure Web app that will be hosted through Azure API Management Service. I need the app to connect to an AzureSQL database using a system assigned managed identity.
Connection string:
Server=xxx;Initial Catalog=xxx;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication="Active Directory Managed Identity";
But I am getting the error The 'ClientID' option must be provided.
I am using dapper instead of EF, and following the below tutorial (coding aspect):
https://learn.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database?tabs=windowsclient%2Cef%2Cdotnet
I am using a system assigned identity, with the Contributor role assigned to it.
So I am not sure why I am being asked for a clientid?
Does this refer to client id you get when adding an identity provider?
Or am I barking up the wrong tree? Any ideas welcome!!!
英文:
I have built an API as an Azure Web app that will be hosted through Azure API Management Service. I need the app to connect to an AzureSQL database using a system assigned managed identity.
var credential = new Azure.Identity.DefaultAzureCredential(); // system-assigned identity
// Get token for Azure SQL Database
var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net/.default" }));
// Add the token to the SQL connection
var conn = new SqlConnection(_connStringRules);
conn.AccessToken = token.Token;
conn.Open();
Connection string:
Server=xxx;Initial Catalog=xxx;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication="Active Directory Managed Identity";
But I am getting the error The 'ClientID' option must be provided
I am using dapper instead of EF, and following the below tutorial (coding aspect)
https://learn.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database?tabs=windowsclient%2Cef%2Cdotnet
I am using a system assigned identity, with the Contributor role assigned to it
So I am not sure why I am being asked for a clientid?
Does this refer to client id you get when adding an identity provider?
Or am I barking up the wrong tree? Any ideas welcome!!!
答案1
得分: 0
I'll provide translations for the text you've provided:
"要连接 Azure SQL Server 从 Azure Web API,使用系统分配的托管标识进行身份验证,请在 Appsetting.json 中以以下格式提供连接字符串:
"ConnectionStrings": {
"QuotesDatabase": "Server=tcp:<servername>.database.windows.net,1433; Database=<databasename>;"
}
使用以下代码进行连接:
var connectionString = Configuration.GetConnectionString("<connectionstringname>");
services.AddTransient(a =>{
var sqlConnection = new SqlConnection(connectionString);
var credential = new DefaultAzureCredential();
var token = credential
.GetToken(new Azure.Core.TokenRequestContext(
new[] { "https://database.windows.net/.default" }));
sqlConnection.AccessToken = token.Token;
return sqlConnection;
}
我按你的要求将代码部分保留原文不翻译。
我已按照你的要求将 "admin" 设置为 SQL Server 的管理员。
选择管理员帐户以进行 Azure 服务身份验证以检索令牌凭证。
供参考的图像:
在 Azure 应用服务的系统分配管理标识中启用 ON 状态。
登录到 SQL Server,将用户添加到数据库并分配用户角色。
create user [<appName>] from external provider;
alter role db_datareader add member [<appName>];
alter role db_datawriter add member [<appName>];
数据库已成功连接到应用程序。
供参考的图像:
"
英文:
To connect Azure sql server from Azure web API with system assigned managed identity authentication give connection string in below format in Appsetting.json
"ConnectionStrings": {
"QuotesDatabase": "Server=tcp:<servename>.database.windows.net,1433; Database=<databasename>;" }
Use below code for connection.
var connectionString = Configuration.GetConnectionString("<connectionstringname>");
services.AddTransient(a =>{
var sqlConnection = new SqlConnection(connectionString);
var credential = new DefaultAzureCredential();
var token = credential
.GetToken(new Azure.Core.TokenRequestContext(
new[] { "https://database.windows.net/.default" }));
sqlConnection.AccessToken = token.Token;
return sqlConnection;
I set admin as you want to the sql server.
choose administrator account for azure service authentication to retrieve the token credentials.
Image for reference:
Enable system assigned manage identity in on state of Azure app service.
Login to sql server with administrator add user to the database and assign role to the user
create user [<appName>] from external provider;
alter role db_datareader add member [<appName>];
alter role db_datawriter add member [<appName>];
The database successfully connected to the app.
Image for reference:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论