Authenticate with Azure AD

[rdagumampan]

https://dba.stackexchange.com/questions/184598/unable-to-connect-using-azure-ad-service-principal-on-sql-server https://michaelcollier.wordpress.com/2016/11/03/connect-to-azure-sql-database-by-using-azure-ad-authentication/ https://techcommunity.microsoft.com/t5/azure-sql-blog/azure-ad-service-principal-authentication-to-sql-db-code-sample/ba-p/481467

Managed Identity vs Service Principal https://stackoverflow.com/questions/61322079/difference-between-service-principal-and-managed-identities-in-azure https://docs.microsoft.com/en-us/azure/active-directory/develop/app-objects-and-service-principals#service-principal-object

Token lifetime https://docs.microsoft.com/en-us/azure/active-directory/develop/active-directory-configurable-token-lifetimes https://docs.microsoft.com/en-us/azure/active-directory/develop/access-tokens#access-token-lifetime

[pmelander]

Hi @rdagumampan, This issue can be closed as it was resolved by #267. Please let me know if you need some step by step instructions on how to setup AAD authentication for Azure SQL server. I’d be happy to help out.

[rdagumampan]

@pmelander, thanks again for reaching out. When I look at the docs, it doesnt seem to be straight forward. If user has to use Service Principal, there seems to be some change to be made in the way connection is established. Feel free to pick this up if this needs code changes.

var sqlConnectionString = "Data Source=tcp:[servername].database.windows.net,1433;Initial Catalog=[databasename];Persist Security Info=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False";

string clientId = "[service principal application id]";
string aadTenantId ="[azure active directory id]";

string AadInstance = "https://login.windows.net/{0}";
string ResourceId = "https://database.windows.net/";

var authenticationContext = new AuthenticationContext(string.Format(AadInstance, aadTenantId));
var clientCredential = new ClientCredential(clientId, "[the password to get]");

var authenticationResult = authenticationContext.AcquireTokenAsync(ResourceId, clientCredential).Result;

using (var conn = new SqlConnection(sqlConnectionString))
{
    conn.AccessToken = authenticationResult.AccessToken;
    conn.Open();

    using (var cmd = new SqlCommand("SELECT SUSER_SNAME()", conn))
    {
        var result = cmd.ExecuteScalar();
        result.Dump();
    }
}

[pmelander]

I think you run into this problem as you not using Authentication=Active Directory Default in your connection string. When using this mode the SqlClient will acquire a Token in the most appropriate manner for your client and it simplifies the connection more in line with the connection in SqlDataServices.CreateConnection that simply returns a new connection without worrying about acquiring a Token.

var sqlConnectionString = "Data Source=tcp:[servername].database.windows.net,1433;Initial Catalog=[databasename];Connect Timeout=30;Encrypt=True;Authentication=Active Directory Default";

using (var conn = new SqlConnection(sqlConnectionString))
{
    conn.Open();
    using (var cmd = new SqlCommand("SELECT SUSER_SNAME()", conn))
    {
        var result = cmd.ExecuteScalar();
        result.Dump();
    }
}

Although token authentication can be used when authenticating with AAD, using Active Directory Managed Identity simplifies authentication, extending login solutions to the client environment, Visual Studio Code, Visual Studio, Azure CLI etc.

With this authentication mode, the driver acquires a token by passing "DefaultAzureCredential" from the Azure Identity library to acquire an access token. This mode attempts to use these credential types to acquire an access token in the following order:

EnvironmentCredential Enables authentication to Azure Active Directory using client and secret, or username and password, details configured in the following environment variables: AZURE_TENANT_ID, AZURE_CLIENT_ID, AZURE_CLIENT_SECRET, AZURE_CLIENT_CERTIFICATE_PATH, AZURE_USERNAME, AZURE_PASSWORD (More details) ManagedIdentityCredential Attempts authentication to Azure Active Directory using a managed identity that has been assigned to the deployment environment. "Client Id" of "User Assigned Managed Identity" is read from the "User Id" connection property. SharedTokenCacheCredential Authenticates using tokens in the local cache shared between Microsoft applications. VisualStudioCredential Enables authentication to Azure Active Directory using data from Visual Studio VisualStudioCodeCredential Enables authentication to Azure Active Directory using data from Visual Studio Code. AzureCliCredential Enables authentication to Azure Active Directory using Azure CLI to obtain an access token.

[pmelander]

@rdagumampan Here is a step by step instruction on how to setup AAD MSI for an Azure web service using ARM template deployment.

Enabling Managed Service Identity

Add the identity section to the App service or Function app deployment (ARM) template and set type to SystemAssigned.

resource site_resource 'Microsoft.Web/sites@2020-06-01' = {
  name: appName
  location: location
  kind: 'appservice'
  tags: resourceTags
  identity: {
    type: 'SystemAssigned'
  }

Once the web application has been created in Azure, query the identity information from the resource using Azure CLI:

az resource show -a <WebAppName> -g <ResourceGroupName> --resource-type Microsoft.Web/sites --query identity

We should get an output like this:

{
  "principalId": "f76495ad-d682-xxxx-xxxx-bc70710ebf0e",
  "tenantId": "8305b292-c023-xxxx-xxxx-a042eb5bceb5",
  "type": null
}

Creating SQL Users

Azure SQL Database does not support creating logins or users from service principals created from Managed Service Identity. The only way to provide access to one is to add it to an AAD group, and then grant access to the group to the database.

We can use the Azure CLI to add our MSI to the appropriate group, we're passing the principalId from the previous query.

az ad group member add -g <Group Name> --member-id <PrincipalId>

With the service principal name added to the group, we can add the group as a database user and add it to the db_datareader and db_datawriter roles and grant execute to schemas in MSSMS (sql management studio)

DECLARE @PrincipalName NVARCHAR(max), @SchemaName NVARCHAR(max), @sql NVARCHAR(max)
SELECT @PrincipalName = '<Group Name>', @SchemaName = '<SchemaName>'

IF NOT EXISTS (
  SELECT 0 FROM [sys].[database_principals]
  WHERE [name] = @PrincipalName)
BEGIN
  SELECT @sql = 'CREATE USER ['+ @PrincipalName + '] FROM EXTERNAL PROVIDER'
  EXEC(@sql);

  SELECT @sql = 'ALTER ROLE db_datareader ADD MEMBER [' + @PrincipalName + ']'
  EXEC(@sql);

  SELECT @sql = 'ALTER ROLE db_datawriter ADD MEMBER [' + @PrincipalName + ']'
  EXEC(@sql);
END

SELECT @sql = 'GRANT EXECUTE ON SCHEMA::' + @SchemaName + ' TO [' + @PrincipalName + ']'
EXEC(@sql)

[rdagumampan]

@pmelander I think there are two use cases here and you described one where it works like "Integrated Security" mode. From what I read, a Service Principal can be Application or Managed Identity. It is when users uses Application where users need to acquired token for the connection and renew it.

[rdagumampan]

Closed. Released in v1.3.10. https://github.com/rdagumampan/yuniql/releases/tag/v1.3.10