question

sluu-2125 avatar image
0 Votes"
sluu-2125 asked ·

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

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://docs.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..




windows-uwpazure-ad-multi-factor-authentication
· 2
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi, by didn't seem to work, do you mean that you cannot connect to the database according to the sample code? Does the program give an error message? And can you share the code of your database connection for us to analyze (do not include sensitive information)?

0 Votes 0 · ·
sluu-2125 avatar image sluu-2125 RichardZhang-MSFT ·

Yes i could not connect to my database basing of the sample code. I've changed all the correct fields but have squiggley lines underneath some of my code. It doesnt seem to recognize my system.data.sqlclient reference.

I added the code above

0 Votes 0 · ·
RichardZhang-MSFT avatar image
2 Votes"
RichardZhang-MSFT answered ·

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


· 5 · Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I've attempted to install system.data.sqlclient with the different options it gives but still my code doesn't think its there.
I've tried to package manager as well as the nuget manager directrly from visual studio.
It looks to install correctly but doesn't seem to actually be there.

0 Votes 0 · ·

Hi, Is the minimum version of your application above 16299? Have you tried rebuilding the project after installing the nuget package?

0 Votes 0 · ·
sluu-2125 avatar image sluu-2125 RichardZhang-MSFT ·

Yes, my application is set at minimum version 16299 and the Target is 18362.
After messing with those I have done build/rebuild/cleans. Close out of the application, reopen.

0 Votes 0 · ·
Show more comments
MarileeTurscak avatar image
0 Votes"
MarileeTurscak answered ·

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"

· 1 · Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Tried multiple installing options and still same result.

0 Votes 0 · ·
msmall avatar image
1 Vote"
msmall answered ·

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.
    


· 2 · Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Because im not able to use system.data.sqlclient or microsoft.data.sqlclient my code is not liking "SqlAuthenticationProvider" gives me an error.

type or namespace name 'SqlAuthenticationProvider' does not exist in the namespace 'system.data.sqlclient' (are you missing an assembly reference).



0 Votes 0 · ·

You must reference Microsoft.Data.Sqlclient to get SqlAuthenticationProvider.

0 Votes 0 · ·