Secure Azure SQL database connection using a managed identity

Question

We are currently hosting our Sitecore 9.1 initial release on premises, but want to move the complete solution into Azure. We want to use public services and not put our solution in an ASE. To secure our database as much as possible we want to use SQL connection with managed identity as described in this blog post. https://docs.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi

Does anybody know if this is possible with Sitecore? Most important is to secure the xDB databases (shards).

Answer 1

No, this is with t he current version, 9.1 and below, not possible. There is piece of code required to get an accesstoken, which is not available within Sitecore. You will have to come up with another solution. We worked with the new public vNets on the app Services and firewall rules increase access security for these databases.

The actual code that is needed:

public MyDatabaseContext(SqlConnection conn) : base(conn, true)
{
    conn.ConnectionString = WebConfigurationManager.ConnectionStrings["MyDbConnection"].ConnectionString;
    // DataSource != LocalDB means app is running in Azure with the SQLDB connection string you configured
    if(conn.DataSource != "(localdb)\\MSSQLLocalDB")
        --> conn.AccessToken = (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").Result; <--

    Database.SetInitializer<MyDatabaseContext>(null);
}

Answer 2

After reading this Microsoft article, I did some further investigation on this topic https://azure.microsoft.com/nb-no/blog/securing-azure-sql-databases-with-managed-identities-just-got-easier/

With version 1.2 of Azure Services App Authentication library it is possible to add AAD managed identity authentication to SQL databases without changing code. This library requires .NET Framework 4.7.2 or higher, so it will not work with Sitecore 9.1.

I tested it with Sitecore 9.3, thinking I could share my findings. I haven't deeply tested it, but managed to have a clean install of Sitecore up and running using MI for ALMOST all Sitecore database connections. I had to upgrade one library though Sitecore 9.3 comes with version 1.0.1 of "Azure Services App Authentication" library. It must be updated at least version 1.2.

It does NOT work for connection to the xDB collection databases. The problem was the "Microsoft.Azure.SqlDatabase.ElasticScale.Client" library. Unfortunately Sitecore relies on an old version of this library that does not support AAD authentication.