How to get UWP to connect to an Azure SQL database with MFA

Question

Hello All,

I am trying to connect my UWP app to an azure sql database with mfa. I do not want to expose my username and password into the connection string and would like to keep it hidden.

I've attempted to use the ADInteractive class tutorial on the microsoft website but it didn't seem to work.
https://learn.microsoft.com/en-in/azure/sql-database/active-directory-interactive-connect-azure-sql-db

Also part of the issue may be because I've installed the nuget package (Microsoft.IdentityModel.Clients.ActiveDirectory) but it doesnt allow me to view it in object browser.
I have tried to clean/rebuild/build installed it several different ways with no luck.

I have also tried multiple target versions like Win10 1903/1809/1803...

Any help would be appreciated.
If I am doing something incorrectly or if its even possible to do what Im atempting.

using System;

// Reference to Azure AD authentication assembly
using Microsoft.IdentityModel.Clients.ActiveDirectory;

using DA = System.Data;
using SC = System.Data.SqlClient;
using AD = Microsoft.IdentityModel.Clients.ActiveDirectory;
using TX = System.Text;
using TT = System.Threading.Tasks;

namespace ADInteractive5
{
class Program
{
// ASSIGN YOUR VALUES TO THESE STATIC FIELDS !!
static public string Az_SQLDB_svrName = "";
static public string AzureAD_UserID = "";
static public string Initial_DatabaseName = "";
// Some scenarios do not need values for the following two fields:
static public readonly string ClientApplicationID = "";
static public readonly Uri RedirectUri = new Uri("");

    public static void Main(string[] args)  
    {  
        var provider = new ActiveDirectoryAuthProvider();  

        **SC.SqlAuthenticationProvider**.SetProvider(**SC.SqlAuthenticationMethod**.ActiveDirectoryInteractive,provider);  

        Program.Connection();  
    }  

I also can't go to the reference and it tells me it can't be viewed in the object browser.
also whatever is in bold is telling me that it doesn't exist in the namespace..

Answer 1

Hello,​

Welcome to our Microsoft Q&A platform!

It doesnt seem to recognize my system.data.sqlclient reference.

The default UWP project does not include System.Data.Sqlclient. Please search and install System.Data.Sqlclient in nuget package manager, here is the address of this package.

Thanks

Answer 2

Try doing it through the CLI:

dotnet add package System.Data.SqlClient  

Or:

install-package System.Data.SqlClient   

If that doesn't work, maybe try from the menu - Project > Add Reference > Assemblies > System.Data

You may also need to change the class library from ".net framework" if you're using ".net standard"

Answer 3

Hello:
I have an App for the Microsoft Store that you advise me for database with MSa account

Answer 4

I've found the following bits of useful information:

1. Use Microsoft.Data.SqlClient instead of System.Data.SqlClient
2. The class ActiveDirectoryAuthProvider is defined at the bottom of the sample code:

   public class ActiveDirectoryAuthProvider : SC.SqlAuthenticationProvider  
       {  
           // Program._ more static values that you set!  
           private readonly string _clientId = Program.ClientApplicationID;  
           private readonly Uri _redirectUri = Program.RedirectUri;  
   
           public override async TT.Task  
               AcquireTokenAsync(SC.SqlAuthenticationParameters parameters)  
           {  
               AD.AuthenticationContext authContext =  
                   new AD.AuthenticationContext(parameters.Authority);  
               authContext.CorrelationId = parameters.ConnectionId;  
               AD.AuthenticationResult result;  
   
               switch (parameters.AuthenticationMethod)  
               {  
                   case SC.SqlAuthenticationMethod.ActiveDirectoryInteractive:  
                       Console.WriteLine("In method 'AcquireTokenAsync', case_0 == '.ActiveDirectoryInteractive'.");  
   
                       result = await authContext.AcquireTokenAsync(  
                           parameters.Resource,  // "https://database.windows.net/"  
                           _clientId,  
                           _redirectUri,  
                           new AD.PlatformParameters(AD.PromptBehavior.Auto),  
                           new AD.UserIdentifier(  
                               parameters.UserId,  
                               AD.UserIdentifierType.RequiredDisplayableId));  
                       break;  
   
                   case SC.SqlAuthenticationMethod.ActiveDirectoryIntegrated:  
                       Console.WriteLine("In method 'AcquireTokenAsync', case_1 == '.ActiveDirectoryIntegrated'.");  
   
                       result = await authContext.AcquireTokenAsync(  
                           parameters.Resource,  
                           _clientId,  
                           new AD.UserCredential());  
                       break;  
   
                   case SC.SqlAuthenticationMethod.ActiveDirectoryPassword:  
                       Console.WriteLine("In method 'AcquireTokenAsync', case_2 == '.ActiveDirectoryPassword'.");  
   
                       result = await authContext.AcquireTokenAsync(  
                           parameters.Resource,  
                           _clientId,  
                           new AD.UserPasswordCredential(  
                               parameters.UserId,  
                               parameters.Password));  
                       break;  
   
                   default: throw new InvalidOperationException();  
               }  
               return new SC.SqlAuthenticationToken(result.AccessToken, result.ExpiresOn);  
           }  
   
           public override bool IsSupported(SC.SqlAuthenticationMethod authenticationMethod)  
           {  
               return authenticationMethod == SC.SqlAuthenticationMethod.ActiveDirectoryIntegrated  
                   || authenticationMethod == SC.SqlAuthenticationMethod.ActiveDirectoryInteractive  
                   || authenticationMethod == SC.SqlAuthenticationMethod.ActiveDirectoryPassword;  
           }  
       } // EOClass ActiveDirectoryAuthProvider.