Azure SQL Server using Managed Identities

ServiceStack.OrmLite SQL Server
1

Azure App Services have what is called a managed identity, it is an Azure Active Directory (AAD) object assigned directly to an App Service. You can then grant this AAD object permissions into Azure SQL Databases or Azure SQL Managed Instance Databases. When using the AAD permissions, you need to request an access token from AAD and assign it to the connection.

Here is some example code, where I do such a thing.

        public static SqlConnection GetSqlConnection(string connectionString, bool useAADAuthentication, string activeDiretoryTenant)
    {
        var connection = new SqlConnection(connectionString);

        if (useAADAuthentication)
            connection.AccessToken = new AzureServiceTokenProvider().GetAccessTokenAsync("https://database.windows.net/", activeDiretoryTenant).Result;

        return connection;
    }

Now with ORMLite, I would like to see using this AAD authentication method become an option. Can you provide guidance on how this can be achieved? My company uses named connections, so it would be vital for it to be available there as well.

2

I think I found a solution. The below code, overrides the CreateConnection method. It first looks at the connection string for the “User ID”, if it does not find it, the provider knows to request an access token from Azure Active Directory. This works for us, as our local dev machines require sql authentication, as we run SQL server in a Linux Docker container. However, if that is not the case, you could in your startup, differentiate between environments and what provider you want to use.

public class SqlServerAzureOrmLiteDialectProvider : SqlServer2017OrmLiteDialectProvider
{
    public new static SqlServerAzureOrmLiteDialectProvider Instance = new SqlServerAzureOrmLiteDialectProvider();

    public override IDbConnection CreateConnection(string connectionString, Dictionary<string, string> options)
    {
        var isFullConnectionString = connectionString.Contains(";");

        var usesSqlAuthentication = connectionString.ToLower().Contains("user id");

        if (!isFullConnectionString)
        {
            var filePath = connectionString;

            var filePathWithExt = filePath.EndsWithIgnoreCase(".mdf")
                ? filePath
                : filePath + ".mdf";

            var fileName = Path.GetFileName(filePathWithExt);
            var dbName = fileName.Substring(0, fileName.Length - ".mdf".Length);

            connectionString = $@"Data Source=.\SQLEXPRESS;AttachDbFilename={filePathWithExt};Initial Catalog={dbName};Integrated Security=True;User Instance=True;";
        }

        if (options != null)
        {
            foreach (var option in options)
            {
                if (option.Key.ToLower() == "read only")
                {
                    if (option.Value.ToLower() == "true")
                    {
                        connectionString += "Mode = Read Only;";
                    }
                    continue;
                }
                connectionString += option.Key + "=" + option.Value + ";";
            }
        }


        var connection = new SqlConnection(connectionString);

        if (!usesSqlAuthentication)
            connection.AccessToken = new AzureServiceTokenProvider().GetAccessTokenAsync("https://database.windows.net/").Result;

        return connection;
    }
}
1 Like