
Steps to connect Azure SQL with Azure Active Directory
- Create an Azure SQL Database
- Configure it to use Azure AD by setting up an admin
-
Connect with SSMS to your database using Azure Active Directory - Universal with MFA authentication
-
For Local Development
- Go to Visual Studio > Tools > Options > Azure Service Authentication (Login with your AD Account)
- Run the below script to add your email id which is an Azure AD Identity as a user in SQL Server
CREATE USER [your.name@domain.com] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [your.name@domain.com]; ALTER ROLE db_datawriter ADD MEMBER [your.name@domain.com]; ALTER ROLE db_ddladmin ADD MEMBER [your.name@domain.com]; GO
-
When App runs in Azure Context
- Create an App Service and enable System Assigned Managed Identity
- Run the below script to add your email id which is an Azure AD Identity as a user in SQL Server
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
-
To get the Access Token for Azure AD Identity, install NuGet package -
Microsoft.Azure.Services.AppAuthentication
-
Create a class like below:
public class AzureSqlAuthTokenService { public string GetToken(string connectionString) { AzureServiceTokenProvider provider = new AzureServiceTokenProvider(); var token = provider.GetAccessTokenAsync("https://database.windows.net/").Result; return token; } }
-
Finally write the ADO.NET Code and set the AccessToken property.
using (SqlConnection con = new SqlConnection(_configuration.GetConnectionString("SQLConnectionString"))) { string connectionStringForToken_Local = "RunAs=Developer; DeveloperTool=VisualStudio"; string connectionStringForToken_Azure = "RunAs=App"; con.AccessToken = new AzureSqlAuthTokenService().GetToken(connectionStringForToken_Local); SqlCommand cmd = new SqlCommand("SELECT * FROM Customer", con); cmd.CommandType = CommandType.Text; con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { customer.Add(rdr["CustomerName"].ToString()); } con.Close(); }