How to connect Kentico to Azure SQL database using managed identity

Question

I am using Kentico 13 and trying to connect to the Azure SQL database using managed identity in my localhost environment using the below connection string.

"Server=tcp:<MyServer>.database.windows.net,1433;Initial Catalog=<MyDB>;Authentication=Active Directory Device Code Flow;"

I am getting the below error

CMS.DataEngine.ApplicationInitException: 'Keyword not supported: 'authentication'.'

How can I get past this error or is there any other way to connect to the database using managed Identity?

Answer 1

I would try searching on Google or MSDN pages as this is more related to Azure SQL and not Kentico. E.g. I found this: Tutorial: Use a Windows VM system-assigned managed identity to access Azure SQL or Managed identities in Azure AD for Azure SQL and as it looks like, you need to use Azure AD. Local identities are probably not supported. I would maybe ask Azure SQL support.

Answer 2

Looks like there is a way to customize creating the database context in Kentico.

https://docs.xperience.io/custom-development/customizing-providers/custom-data-provider-example

I followed the steps mentioned in the above article and then modified the DataConnection class, CreateNativeConnection method as below

protected override IDbConnection CreateNativeConnection()
{
    var conn = new SqlConnection(ConnectionString);
    var credential = new Azure.Identity.DefaultAzureCredential();
    var token = credential.GetTokenAsync(new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net//.default" }),default);
    return conn;
}