Cannot connect to Azure SQL database using AAD from C#

Question

Our devops guys has recently enabled AAD authentication in our Azure SQL Servers and added my identity (say [email protected]) as a dbo to MyDatabase. I can successfully connect to the database using SSMS and now I want to connect using the same identity from my code:

using System;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Microsoft.Azure.Services.AppAuthentication;

namespace AzureADAuth {
    class Program {
        static async Task Main(string[] args) {
            var azureServiceTokenProvider = new AzureServiceTokenProvider();
            string accessToken = await azureServiceTokenProvider.GetAccessTokenAsync("https://management.azure.com/");

            Principal principal = azureServiceTokenProvider.PrincipalUsed;
            Console.WriteLine($"AppId: {principal.AppId}");
            Console.WriteLine($"IsAuthenticated: {principal.IsAuthenticated}");
            Console.WriteLine($"TenantId: {principal.TenantId}");
            Console.WriteLine($"Type: {principal.Type}");
            Console.WriteLine($"UserPrincipalName: {principal.UserPrincipalName}");

            using (var connection = new SqlConnection("Data Source=########.database.windows.net; Initial Catalog=MyDatabase;")) {
                connection.AccessToken = accessToken;
                await connection.OpenAsync();

                var command = new SqlCommand("select CURRENT_USER", connection);
                using (SqlDataReader reader = await command.ExecuteReaderAsync()) {
                    await reader.ReadAsync();
                    Console.WriteLine(reader.GetValue(0));
                }
            }

            Console.ReadKey();
        }
    }
}

Unfortunately, it does not work. Here is the output:

AppId:
IsAuthenticated: True
TenantId: ########-####-####-bc14-789b44d11a3c
Type: User
UserPrincipalName: [email protected]

Unhandled Exception: System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions,
SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, Sq
lConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTr
ansientFaultHandling)

AzureServiceTokenProvider successfully grabs my identity from Azure CLI but the SQL login fails. What am I missing?

P.S.

I'm targeting .NET full framework 4.7; Microsoft.Azure.Services.AppAuthentication package is the latest, 1.0.1; my machine is not joined to any domain (not sure if it's important as I don't need AAD integrated auth, only token based)

Why use Token auth, instead of "Active Directory - Integrated" or "Active Directory - Password" like SSMS uses? — David Browne - Microsoft
– David Browne - Microsoft, Commented May 10, 2018 at 13:30
My final goal after the test is switching to Managed Service Identities so as I understand neither Password nor Integrated auth options will work. — UserControl
– UserControl, Commented May 10, 2018 at 13:37

Jatin Parmar · Accepted Answer · 2018-05-11 06:01:13Z

2

Try with database.windows.net instead of management.azure.com option,

string accessToken = await azureServiceTokenProvider.GetAccessTokenAsync("https://database.windows.net/");

edited May 11, 2018 at 6:01

answered May 10, 2018 at 14:05

Jatin Parmar

6394 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

UserControl Over a year ago

Thank you so much!

Garry Tran · Accepted Answer · 2018-09-26 20:55:53Z

1

For anybody who has this issue - I scoured multiple resources where users had this exact issue without a solution that worked. However, was able to finally resolve my issue by adding the 'tenant' of where my SQL database resided. Since I had multiple Azure subscriptions.

In the above code to grab the token:

return provider.GetAccessTokenAsync("https://database.windows.net/");

I needed to add the tenant as well:

return provider.GetAccessTokenAsync("https://database.windows.net/", "hosttenant.onmicrosoft.com");

edited Sep 26, 2018 at 20:55

answered Sep 26, 2018 at 18:11

Garry Tran

112 bronze badges

Collectives™ on Stack Overflow

Cannot connect to Azure SQL database using AAD from C#

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related