Azure function - connect to Azure SQL database using Active Directory Interactive authentication

Question

I'm using SqlClient in my Azure function. Currently when I try to create a new instance passing the connection string, I receive the following message

Keyword not supported: 'authentication'

My connection string is

server=tcp:mydbserver.database.windows.net;database=mydb;UID=AnyString;Authentication=Active Directory Interactive

My azure function has 'Identity' setting enabled.

My other .NET apps running as AppService are working excellent connecting to the same DB, but they use EntityFramework

I have tried to remove Authentication=Active Directory Interactive and also add the following line to the connection

 connection.AccessToken = new Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider().GetAccessTokenAsync("https://database.windows.net/").Result;

But I'm just getting different error messages like Login failed for user '' or Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Thanks!

Answer 1

I got success with the following steps:

1. Provision an Azure Active Directory administrator for your Azure SQL Database server.

Note: Please ensure that you set a user as the AAD administrator for your Azure SQL Server.

2. Connect to your Azure SQL Database with the AAD account in step 1. You can use SSMS or just connect to your database from the portal.

3. Add service principal and assign a role

CREATE USER [Azure_AD_principal_name] FROM EXTERNAL PROVIDER;
EXEC sp_addrolemember 'db_datareader', 'Azure_AD_principal_name';

4. Code sample

string sqlResource = "https://database.windows.net/";
string tenantId = "e4c9ab4e-****-****-****-230ba2a757fb";
string serverName = "{server}.database.windows.net";
string databaseName = "{database}";

var token = new AzureServiceTokenProvider().GetAccessTokenAsync(sqlResource, tenantId).ConfigureAwait(false).GetAwaiter().GetResult();

string sqlConnectionString = String.Format("Data Source=tcp:{0},1433;Initial Catalog={1};Persist Security Info=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False", serverName, databaseName);


using (SqlConnection connection = new SqlConnection(sqlConnectionString))
{
    connection.AccessToken = token;
    using (SqlCommand command = new SqlCommand("SELECT Distinct TABLE_NAME FROM information_schema.TABLES", connection))
    {
        connection.Open();
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader[0]);
            }
        }
    }
}

Answer 2

I believe the connection.AccessToken property is the only way of doing this in .NET Core as per this conversation.

As for the error you are seeing with this, is probably because a user for the managed identity has not been created in the database. Follow the steps in this section for that.

Here is the SQL Script for reference

CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
ALTER ROLE db_ddladmin ADD MEMBER [<identity-name>];
GO

where <identity-name> is the name of the managed identity configured in Azure AD.