CODEWITHSUNDEEP
vb.netazureazure-active-directoryazure-sql-databasemulti-factor-authentication
Keith Stein
Keith Stein

Reputation: 6724

Azure Sql Server MFA connection from .NET application

My goal is to allow users to connect to our Azure Sql Server using their Azure Active Directory credentials. I'm trying to follow the steps in this article, but I'm getting an error I can't sort out:
Connect to Azure SQL Database with Azure Multi-Factor Authentication

Below are the appropriate pieces of my code, which I largely copied from the example in the article (except my app is written in VB.NET so I had to translate). It requires the Microsoft.IdentityModel.Clients.ActiveDirectory assembly, which I got from NuGet.

Public Module DB
    Private ConnectionProvider As ActiveDirectoryAuthProvider

    'Gets run at application start    
    Public Sub SetProvider()
        ConnectionProvider = New ActiveDirectoryAuthProvider
        SqlAuthenticationProvider.SetProvider(SqlAuthenticationMethod.ActiveDirectoryInteractive, ConnectionProvider)
    End Sub
End Module

'I can't believe Microsoft doesn't just have this as a class that's already been written
Public Class ActiveDirectoryAuthProvider
    Inherits SqlAuthenticationProvider

    Private ReadOnly _clientId As String = "Our Client ID From The Azure Portal"
    Private ReadOnly _redirectUri As New Uri("A Valid URL")

    Public Overrides Async Function AcquireTokenAsync(parameters As SqlAuthenticationParameters) As Task(Of SqlAuthenticationToken)
        Dim authContext As New AuthenticationContext(parameters.Authority)
        authContext.CorrelationId = parameters.ConnectionId
        Dim result As AuthenticationResult

        Select Case parameters.AuthenticationMethod
            Case SqlAuthenticationMethod.ActiveDirectoryInteractive
                result = Await authContext.AcquireTokenAsync(parameters.Resource, _clientId, _redirectUri, New PlatformParameters(PromptBehavior.Auto), New UserIdentifier(parameters.UserId, UserIdentifierType.RequiredDisplayableId))
            Case SqlAuthenticationMethod.ActiveDirectoryIntegrated
                result = Await authContext.AcquireTokenAsync(parameters.Resource, _clientId, New UserCredential())
            Case SqlAuthenticationMethod.ActiveDirectoryPassword
                result = Await authContext.AcquireTokenAsync(parameters.Resource, _clientId, New UserPasswordCredential(parameters.UserId, parameters.Password))
            Case Else
                Throw New InvalidOperationException()
        End Select

        Return New SqlAuthenticationToken(result.AccessToken, result.ExpiresOn)
    End Function

    Public Overrides Function IsSupported(ByVal authenticationMethod As SqlAuthenticationMethod) As Boolean
        Return authenticationMethod = SqlAuthenticationMethod.ActiveDirectoryIntegrated OrElse authenticationMethod = SqlAuthenticationMethod.ActiveDirectoryInteractive OrElse authenticationMethod = SqlAuthenticationMethod.ActiveDirectoryPassword
    End Function
End Class

'And finally, I create new connections like this:
New SqlConnection($"Server=tcp:ourserver.database.windows.net,1433;Initial Catalog=OurDatabase;TrustServerCertificate=True;Pooling=False;Encrypt=True;Authentication=""Active Directory Interactive"";User ID={Environment.UserName}@OurDomain.com;")

Using this code, I do get the popup from Azure asking me to sign in when I run SqlConnection.Open. As soon as I've signed in however, I get the following exception:

Microsoft.IdentityModel.Clients.ActiveDirectory.AdalServiceException
AADSTS7000218: The request body must contain the following parameter: 'client_assertion' or 'client_secret'.

Any idea how I can fix that?

Upvotes: 3

Views: 2139

Answers (2)

Oleksandr Chuikov
Oleksandr Chuikov

Reputation: 111

I faced with this problem a bit earlier, spent a lot of time to figure out the problem and the only solution which helped was adding of "https://login.microsoftonline.com/common/oauth2/nativeclient" on "Authentication" tab to "Redirect URIs" like it was mentioned by Keith Stein enter image description here

Upvotes: 2

Keith Stein
Keith Stein

Reputation: 6724

So, in digging through every resource I could find, I came across this question: “error_description”:"AADSTS70002: The request body must contain the following parameter: 'client_secret or client_assertion'

The answer linked above points to the "Redirect URI" registered with the application in Azure as the cause of the issue.

The Microsoft article in my question states: "For this article, any valid value is fine for RedirectUri, because it isn't used here." The example they use is: "https://mywebserver.com/".

Contrary to the quote from Microsoft, the answer I linked above points out that Azure uses the Redirect URI to determine the type of application that is being registered. Changing the URI from my company's website to "https://login.microsoftonline.com/common/oauth2/nativeclient" fixes my problem. That URL is one of the default values Azure lets you pick from. It evidently indicates to Azure that you are registering a native app, not a web app. Once Azure knows this, it seems to stop demanding a "'client_assertion' or 'client_secret'", which I can only assume are things required for web app authentication.

Upvotes: 4

Related Questions