Securing Azure SQL Databases with managed identities just got easier

Udgivet den 25 april, 2019

Security Software Engineer, Cloud & AI Security Green Team

We are happy to share the second preview release of the Azure Services App Authentication library, version 1.2.0. This release enables simple and seamless authentication to Azure SQL Database for existing .NET applications with no code changes – only configuration changes! Up until this release, developers who wanted their existing SQL applications to use managed identities and AAD-based authentication were required to make code changes to retrieve and set the access token used for authentication. As many developers are familiar with the App Authentication library, they opted to use it to retrieve the access token, as below:

SqlConnection connection = new SqlConnection(connectionString);
connection.AccessToken = await (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/")
connection.Open();

Implementing and testing these code changes were often not straightforward. This was compounded further with EntityFramework where these code changes were more difficult and error-prone for developers, as evidenced by the many external questions we see on StackOverflow. However, in .NET Framework 4.7.2, new functionality for SqlClient was added that enables developers to register a SQL authentication provider via an application's configuration. We've already done the work of implementing this SQL authentication provider for developers, so now the only actions they need to do are a few simple configuration changes to register our provider, as shown below.

With this release of the App Authentication library, we hope to enable many developers to try out our new functionality in existing SQL-backed solutions and gain the security benefits that the App Authentication library and managed identities afford. For more details and to try out this new functionality, please check out our new sample.

Before, using a connection string containing credentials:

<connectionStrings>
   <!-- using connection string containing credentials -->
   <add name="MyDbConnection" connectionString="Data Source=tcp:[SQL Server Name].database.windows.net;Initial Catalog=[SQL DB name];UID=[UID];Password=[pwd]" providerName="System.Data.SqlClient" />
</connectionStrings>

After, registering App Authentication’s SqlAppAuthenticationProvider:

<configSections>
   ...
   <!-- Change #1: Register the new SqlAuthenticationProvider configuration section -->
   <section name="SqlAuthenticationProviders" type="System.Data.SqlClient.SqlAuthenticationProviderConfigurationSection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
</configSections>
<connectionStrings>
   <!-- Change #2: Update the connection string to remove credentials and reference the registered SQL authentication provider from change #3 -->
   <add name="MyDbConnection" connectionString="Data Source=tcp:[SQL Server Name].database.windows.net;Initial Catalog=[SQL DB Name];UID=AnyString;Authentication=Active Directory Interactive" providerName="System.Data.SqlClient" />
</connectionStrings>
<!-- Change #3: Add the new SqlAuthenticationProvider configuration section, registering the built-in authentication provider in AppAuth library -->
<SqlAuthenticationProviders>
   <providers>
     <add name="Active Directory Interactive" type="Microsoft.Azure.Services.AppAuthentication.SqlAppAuthenticationProvider, Microsoft.Azure.Services.AppAuthentication" />
   </providers>
</SqlAuthenticationProviders>

We hope you enjoy trying out our new SQL authentication provider functionality in the App Authentication library! For any questions or concerns that you may have, please reach out to us on StackOverflow.