In case you need to move your web app from on prem to Azure, need to configure managed identity between Azure App Service and Azure SQL data base and do not know where to start.
Disclaimer: You don’t really need to use managed identity to connect to an Azure SQL data base, but it will release you from the need to use a password and write it somewhere and having to hide it. So let’s start!
Go to your App service in Azure portal, click on Identity. You will see this:
Turn the status to ON and click save
You have just created an Azure user that will represent your application!
Please remember that Azure users names need to be unique – this is why it’s best practice to append a timestamp to the name of your App Service.
That user will be called the same as your application. On the last screen shot it is VicaDevTest. Now you need to create a matching user in the data base, like this:
create user VicaDevTest from external provider
please note that the user name in the data base matches exactly the name of the App Service.
GRANT EXECUTE TO VicaDevTest
>GRANT SELECT TO VicaDevTest
>GRANT UPDATE TO VicaDevTest
Now you need a connection string you can use form your code, as is explained
You need to update to .NET Framework 4.7.2.
And update your web.config as follows
<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>
That’s it. You have your connection string to the data base and are all set to use it.
Word of caution – do not try this locally, this only works in Azure. If you want to test your app locally you need to use good old SQL user with his password.
In my web.config I have 2 lines for connection string : one with SQL user and password to test on my machine and another line with managed identity to upload to Azure and test there. One of those lines is always commented out.
I hope this helps you out,
Vica
