A managed identity from Azure Active Directory (Azure AD) allows your app to easily access other Azure AD-protected resources such as Azure Key Vault or Azure SQL. The identity is managed by the Azure platform and does not require you to provision or rotate any secrets.
We have two types of Managed Identities:
You can read about Managed Identity for App Service and Azure Functions here.
Below are the steps for the configuration:
var conn = (System.Data.SqlClient.SqlConnection)Database.Connection; var credential = new Azure.Identity.DefaultAzureCredential(); var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net/.default" })); conn.AccessToken = token.Token;
If you are using User-assigned Identity you would need to tweak the code as below. You can read about DefaultAzureCredential() here.
string userAssignedClientId = ""; //Give Client ID of User Managed Identity var conn = new SqlConnection(connectionString); var credential = new Azure.Identity.DefaultAzureCredential(new DefaultAzureCredentialOptions { ManagedIdentityClientId = userAssignedClientId }); var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net/.default"})); conn.AccessToken = token.Token;
Server=tcp:<AzSQLDBName>.database.windows.net,1433;Initial Catalog=<DBName>
CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [<identity-name>]; ALTER ROLE db_datawriter ADD MEMBER [<identity-name>]; ALTER ROLE db_ddladmin ADD MEMBER [<identity-name>]; GO
SELECT DP1.name AS DatabaseRoleName, isnull (DP2.name, 'No members') AS DatabaseUserName; FROM sys.database_role_members AS DRM RIGHT OUTER JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id; LEFT OUTER JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id; WHERE DP1.type = 'R' ORDER BY DP1.name;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.