Not able to connect to SQL Server database through Azure Active Directory-CodePudding

I am new to Azure AD and I am trying to connect to a SQL Server database through AAD in a C#, .NET Core, EF application. I am not sure if this is the correct way to connect to SQL Server database through AAD, but below is the code I have written in DbContext:

public static void TestConnection1(IConfiguration config)
{    
    ClientCredential cc = new ClientCredential(clientId, "secretValue");
    var context = new AuthenticationContext("https://login.microsoftonline.com/"   tenantId);
    var result = context.AcquireTokenAsync("https://management.azure.com", cc);
    var accessToken = result.Result.AccessToken;

    var sqlConnectionString = GetConnectionString(config);

    using var connection = new SqlConnection(sqlConnectionString)
    {
        AccessToken = accessToken
    };
    
    connection.Open();   
}

I am getting this error:

System.Data.SqlClient.SqlException (0x80131904): Login failed for user ''. Incorrect or invalid token.

at System.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod(Exception e)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)

Is this the correct way to connect to connect through AAD?

I have an app registration with clientId, tenant, etc

I also tried to use DefaultAzureCredential to create a token but which url to we have to enter below?

Also, where do I need to enter the clientId, tenant etc so that the token will be generated?

var defaultCredential = new DefaultAzureCredential();
var token = defaultCredential.GetToken(new TokenRequestContext(new [] { "???????"})).Token;

Can anyone please help me out ? Thanks in advance.

CodePudding user response：

In my humble opinion, we don't need to set AAD authentication for Azure database in our code. AAD authentication is one of the solution for signing in database in Azure portal. When we want to access Azure database in Azure portal, we need to enter username/password or using AAD authentication.

And if we need to connect to the Azure database in our code, we usually use the connection string which is always set in appsetting.json file. At the same time, we also need to set firewall for the application IP address so that your app can visit the database. This sample showed how to use username/password to query database, but since you used EF core, you only need to obtain the connection string.

And you may worried that the connection string is hardcode in your app, then you may choose to store the connection string into Azure key vault. Then the code snippet DefaultAzureCredential can be used to get secrets stored in Azure key vault.

Finally, you may want to publish your app to Azure APP service, then just follow this tutorial. After publishing, pls don't forget to add the IP address into the database firewall.

CodePudding user response：

You are requesting th wrong scope, it should be database.windows.net - but just use Microsoft.Data.SqlClient with suitable connection string instead:

https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication?view=sql-server-ver16