The only way I have found to login using Active Directory and MFA and cache the token is to use @Alberto's method
I did also find another way which would ask for login credentials every time which is to use this connection string:
OdbcConnection con = new OdbcConnection("Driver={ODBC Driver 17 for SQL Server};SERVER=tcp:myserver.database.windows.net;DATABASE=MyDb;Authentication=ActiveDirectoryInteractive;[email protected]")
Improving the code posted by @alberto. I must say for something so fundamental in the modern world this is unbelievably undocumented. Anyway here's the improved Provider code.
This code also requires you to target .Net Framework 4.7.2 or greater
Firstly follow @alberto's code.. I did find one extra unmentioned step is that you need to also configure a Platform for your app in azure on the authentication tab to look like:
Add these two classes to your project:
ActiveDirectoryAuthProvider
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Microsoft.IdentityModel.Clients.ActiveDirectory; using System.Data.SqlClient; namespace SQLAzureConnectivity { public class ActiveDirectoryAuthProvider : SqlAuthenticationProvider { private string _clientId { get; set; } private Uri _redirectURL { get; set; } = new Uri("https://login.microsoftonline.com/common/oauth2/nativeclient"); public ActiveDirectoryAuthProvider(string clientId) { _clientId = clientId; } //https://learn.microsoft.com/en-us/azure/sql-database/active-directory-interactive-connect-azure-sql-db#c-code-example public override async Task<System.Data.SqlClient.SqlAuthenticationToken> AcquireTokenAsync(System.Data.SqlClient.SqlAuthenticationParameters parameters) { AuthenticationContext authContext = new AuthenticationContext(parameters.Authority, new FilesBasedAdalV3TokenCache(".\\Token.dat")); authContext.CorrelationId = parameters.ConnectionId; AuthenticationResult result = null; switch (parameters.AuthenticationMethod) { case System.Data.SqlClient.SqlAuthenticationMethod.ActiveDirectoryInteractive: Console.WriteLine("In method 'AcquireTokenAsync', case_0 == '.ActiveDirectoryInteractive'."); try { result = await authContext.AcquireTokenSilentAsync(parameters.Resource, _clientId); } catch (AdalException adalException) { if (adalException.ErrorCode == AdalError.FailedToAcquireTokenSilently || adalException.ErrorCode == AdalError.InteractionRequired) { result = await authContext.AcquireTokenAsync(parameters.Resource, _clientId, _redirectURL, new PlatformParameters(PromptBehavior.Auto)); //result = await authContext.AcquireTokenAsync(parameters.Resource, _clientId, _redirectURL, new PlatformParameters(PromptBehavior.Auto), new UserIdentifier(parameters.UserId, UserIdentifierType.RequiredDisplayableId)); } } break; case System.Data.SqlClient.SqlAuthenticationMethod.ActiveDirectoryIntegrated: Console.WriteLine("In method 'AcquireTokenAsync', case_1 == '.ActiveDirectoryIntegrated'."); result = await authContext.AcquireTokenAsync(parameters.Resource, _clientId, new UserCredential()); break; case System.Data.SqlClient.SqlAuthenticationMethod.ActiveDirectoryPassword: Console.WriteLine("In method 'AcquireTokenAsync', case_2 == '.ActiveDirectoryPassword'."); result = await authContext.AcquireTokenAsync(parameters.Resource, _clientId, new UserPasswordCredential(parameters.UserId, parameters.Password)); break; default: throw new InvalidOperationException(); } return new System.Data.SqlClient.SqlAuthenticationToken(result.AccessToken, result.ExpiresOn); } public override bool IsSupported(System.Data.SqlClient.SqlAuthenticationMethod authenticationMethod) { return authenticationMethod == System.Data.SqlClient.SqlAuthenticationMethod.ActiveDirectoryIntegrated || authenticationMethod == System.Data.SqlClient.SqlAuthenticationMethod.ActiveDirectoryInteractive || authenticationMethod == System.Data.SqlClient.SqlAuthenticationMethod.ActiveDirectoryPassword; } } }
FilesBasedAdalV3TokenCache
using Microsoft.IdentityModel.Clients.ActiveDirectory; using System.IO; using System.Security.Cryptography; namespace SQLAzureConnectivity { // This is a simple persistent cache implementation for an ADAL V3 desktop application public class FilesBasedAdalV3TokenCache : TokenCache { public string CacheFilePath { get; } private static readonly object FileLock = new object(); // Initializes the cache against a local file. // If the file is already present, it loads its content in the ADAL cache public FilesBasedAdalV3TokenCache(string filePath) { CacheFilePath = filePath; this.AfterAccess = AfterAccessNotification; this.BeforeAccess = BeforeAccessNotification; lock (FileLock) { this.DeserializeAdalV3(ReadFromFileIfExists(CacheFilePath)); } } // Empties the persistent store. public override void Clear() { base.Clear(); File.Delete(CacheFilePath); } // Triggered right before ADAL needs to access the cache. // Reload the cache from the persistent store in case it changed since the last access. void BeforeAccessNotification(TokenCacheNotificationArgs args) { lock (FileLock) { this.DeserializeAdalV3(ReadFromFileIfExists(CacheFilePath)); } } // Triggered right after ADAL accessed the cache. void AfterAccessNotification(TokenCacheNotificationArgs args) { // if the access operation resulted in a cache update if (this.HasStateChanged) { lock (FileLock) { // reflect changes in the persistent store WriteToFileIfNotNull(CacheFilePath, this.SerializeAdalV3()); // once the write operation took place, restore the HasStateChanged bit to false this.HasStateChanged = false; } } } /// <summary> /// Read the content of a file if it exists /// </summary> /// <param name="path">File path</param> /// <returns>Content of the file (in bytes)</returns> private byte[] ReadFromFileIfExists(string path) { byte[] protectedBytes = (!string.IsNullOrEmpty(path) && File.Exists(path)) ? File.ReadAllBytes(path) : null; byte[] unprotectedBytes = (protectedBytes != null) ? ProtectedData.Unprotect(protectedBytes, null, DataProtectionScope.CurrentUser) : null; return unprotectedBytes; } /// <summary> /// Writes a blob of bytes to a file. If the blob is <c>null</c>, deletes the file /// </summary> /// <param name="path">path to the file to write</param> /// <param name="blob">Blob of bytes to write</param> private static void WriteToFileIfNotNull(string path, byte[] blob) { if (blob != null) { byte[] protectedBytes = ProtectedData.Protect(blob, null, DataProtectionScope.CurrentUser); File.WriteAllBytes(path, protectedBytes); } else { File.Delete(path); } } } }
Then before using a SQLConnection write these two lines:
var provider = new ActiveDirectoryAuthProvider("ClientID from the Azure app you set up earlier"); SqlAuthenticationProvider.SetProvider(SqlAuthenticationMethod.ActiveDirectoryInteractive, provider);
References:
