Connect to Azure SQL database using managed identity C# .Net 6.0
Let's discuss the connecting .net 6.0 c# application to azure SQL serve using MSI
.NET 6.0 applications are more flexible and require less work than other.NET frameworks, such as.NET Core 3.1,.NET 4.8, and so on.
Connect .Net Core 3.1 or the Lower version Application to azure SQL
you will be using the following package
Install-Package Azure.Identity
Install-Package System.Data.SqlClient
Connect .Net 6.0 Application to Azure SQL:
you will be using only following the NuGet package with the new connection string
dotnet add package Microsoft.Data.SqlClient
if you are using system-assigned identity MSI then uses the following connections string and code.(Local Machine)
using Microsoft.Data.SqlClient;
...
// Uncomment one of the two lines depending on the identity type
SqlConnection connection = new SqlConnection(
"Server=tcp:<server-name>.database.windows.net;Database=<database-name>;
Authentication=Active Directory Default;TrustServerCertificate=True"
); // system-assigned identity
// Open the SQL connection
connection.Open();
If you are using a user-assigned identity then use the following connecting string or code (Local Machine)
using Microsoft.Data.SqlClient;
...
SqlConnection connection = new SqlConnection(
"Server=tcp:<server-name>.database.windows.net;Database=<database-name>;
Authentication=Active Directory Default;
User Id=<client-id-of-user-assigned-identity>;TrustServerCertificate=True");
// user-assigned identity
// Open the SQL connection
connection.Open();
Set up your dev environment and Visual Studio
- Visual Studio for Windows is integrated with Azure AD authentication. To enable development and debugging in Visual Studio, add your Azure AD user in Visual Studio by selecting File > Account Settings from the menu, and selecting Sign-in or Add.
- To set the Azure AD user for Azure service authentication, select Tools > Options from the menu, then select Azure Service Authentication > Account Selection. Select the Azure AD user you added and select OK
To run your application on Azure App Service, you may need to use the connection string below
Since Microsoft.Data.SqlClient 2.1.0, the driver supports authentication to Azure SQL Database, and Azure SQL Managed Instance by acquiring access tokens via managed identity. To use this authentication, specify either Active Directory Managed Identity or Active Directory MSI in the connection string, and no password is required. You can't set the Credential property of SqlConnection in this mode either.
Below connection string for system-assigned identity :
// For system-assigned managed identity
// Use your own server and database.
string ConnectionString1 = @"Server=demo.database.windows.net;
Authentication=Active Directory Managed Identity; Database=employeedb";
using (SqlConnection conn = new SqlConnection(ConnectionString1)) {
conn.Open();
}
string ConnectionString2 = @"Server=demo.database.windows.net;
Authentication=Active Directory MSI; Database=employeedb";
using (SqlConnection conn = new SqlConnection(ConnectionString2)) {
conn.Open();
}
For the other pre-steps of SQL server configuration, see here
