So, there's a bit of PEBKAC in the code. Although it's using builder.Authentication = SqlAuthenticationMethod.ActiveDirectoryInteractive;, the class is actually attempting to use ActiveDirectoryIntegrated. So my AD class was never actually hit. Also, in the example code it would actually have never worked either, because the case statement exists for ActiveDirectoryIntegrated - I've stripped it out on my local copy.
I actually needed to use the proper ActiveDirectoryInteractive code to hook this up. Once I did, it was able to authenticate once against the system. And this allowed all the db connections to work without requiring additional browser checks.
Setup
static void Main(string[] args) { var provider = new ActiveDirectoryAuthProvider(); SqlAuthenticationProvider.SetProvider( SqlAuthenticationMethod.ActiveDirectoryInteractive, //SC.SqlAuthenticationMethod.ActiveDirectoryIntegrated, // Alternatives. //SC.SqlAuthenticationMethod.ActiveDirectoryPassword, provider); }
ActiveDirectoryAuthProvider
public class ActiveDirectoryAuthProvider : SqlAuthenticationProvider { private readonly string _clientId = "MyClientID"; private Uri _redirectURL { get; set; } = new Uri("http://localhost:8089"); private AD.AuthenticationContext AuthContext { get; set; } private TokenCache Cache { get; set; } public ActiveDirectoryAuthProvider() { Cache = new TokenCache(); } public override async TT.Task<SC.SqlAuthenticationToken> AcquireTokenAsync(SC.SqlAuthenticationParameters parameters) { var authContext = AuthContext ?? new AD.AuthenticationContext(parameters.Authority, Cache); authContext.CorrelationId = parameters.ConnectionId; AD.AuthenticationResult result; try { result = await authContext.AcquireTokenSilentAsync( parameters.Resource, _clientId); } catch (AdalSilentTokenAcquisitionException) { result = await authContext.AcquireTokenAsync( parameters.Resource, _clientId, _redirectURL, new AD.PlatformParameters(PromptBehavior.Auto, new CustomWebUi()), new UserIdentifier(parameters.UserId, UserIdentifierType.RequiredDisplayableId)); } var token = new SC.SqlAuthenticationToken(result.AccessToken, result.ExpiresOn); return token; } public override bool IsSupported(SC.SqlAuthenticationMethod authenticationMethod) { return authenticationMethod == SC.SqlAuthenticationMethod.ActiveDirectoryInteractive; } }
There are a few things different here:
- I've added an in-memory Token Cache
- I've moved the AuthContext to a property on the class to leave it in memory between runs
- I've set the
_redirectURL property = http://localhost:8089 - I've added a silent check for the token, before reverting
Finally, I've created my own implementation of the ICustomWebUi that handles loading the browser login and the response:
CustomWebUi
internal class CustomWebUi : ICustomWebUi { public async Task<Uri> AcquireAuthorizationCodeAsync(Uri authorizationUri, Uri redirectUri) { using (var listener = new SingleMessageTcpListener(redirectUri.Port)) { Uri authCode = null; var listenerTask = listener.ListenToSingleRequestAndRespondAsync(u => { authCode = u; return @" <html> <body> <p>Successfully Authenticated, you may now close this window</p> </body> </html>"; }, System.Threading.CancellationToken.None); var ps = new ProcessStartInfo(authorizationUri.ToString()) { UseShellExecute = true, Verb = "open" }; Process.Start(ps); await listenerTask; return authCode; } } }
Because I've set the redirect back to localhost, and this code lives inside a console application, I need to listen on the port for the response and capture it in the app, then display a value to the browser to indicate it all worked.
To listen to the port, I used a listener class cribbed from the MS Github:
SingleMessageTcpListener
/// <summary> /// This object is responsible for listening to a single TCP request, on localhost:port, /// extracting the uri, parsing /// </summary> /// <remarks> /// The underlying TCP listener might capture multiple requests, but only the first one is handled. /// /// Cribbed this class from https://github.com/AzureAD/microsoft-authentication-library-for-dotnet/blob/9e0f57b53edfdcf027cbff401d3ca6c02e95ef1b/tests/devapps/NetCoreTestApp/Experimental/SingleMessageTcpListener.cs /// </remarks> internal class SingleMessageTcpListener : IDisposable { private readonly int _port; private readonly System.Net.Sockets.TcpListener _tcpListener; public SingleMessageTcpListener(int port) { if (port < 1 || port == 80) { throw new ArgumentOutOfRangeException("Expected a valid port number, > 0, not 80"); } _port = port; _tcpListener = new System.Net.Sockets.TcpListener(IPAddress.Loopback, _port); } public async Task ListenToSingleRequestAndRespondAsync( Func<Uri, string> responseProducer, CancellationToken cancellationToken) { cancellationToken.Register(() => _tcpListener.Stop()); _tcpListener.Start(); TcpClient tcpClient = null; try { tcpClient = await AcceptTcpClientAsync(cancellationToken) .ConfigureAwait(false); await ExtractUriAndRespondAsync(tcpClient, responseProducer, cancellationToken).ConfigureAwait(false); } finally { tcpClient?.Close(); } } /// <summary> /// AcceptTcpClientAsync does not natively support cancellation, so use this wrapper. Make sure /// the cancellation token is registered to stop the listener. /// </summary> /// <remarks>See https://stackoverflow.com/questions/19220957/tcplistener-how-to-stop-listening-while-awaiting-accepttcpclientasync</remarks> private async Task<TcpClient> AcceptTcpClientAsync(CancellationToken token) { try { return await _tcpListener.AcceptTcpClientAsync().ConfigureAwait(false); } catch (Exception ex) when (token.IsCancellationRequested) { throw new OperationCanceledException("Cancellation was requested while awaiting TCP client connection.", ex); } } private async Task ExtractUriAndRespondAsync( TcpClient tcpClient, Func<Uri, string> responseProducer, CancellationToken cancellationToken) { cancellationToken.ThrowIfCancellationRequested(); string httpRequest = await GetTcpResponseAsync(tcpClient, cancellationToken).ConfigureAwait(false); Uri uri = ExtractUriFromHttpRequest(httpRequest); // write an "OK, please close the browser message" await WriteResponseAsync(responseProducer(uri), tcpClient.GetStream(), cancellationToken) .ConfigureAwait(false); } private Uri ExtractUriFromHttpRequest(string httpRequest) { string regexp = @"GET \/\?(.*) HTTP"; string getQuery = null; Regex r1 = new Regex(regexp); Match match = r1.Match(httpRequest); if (!match.Success) { throw new InvalidOperationException("Not a GET query"); } getQuery = match.Groups[1].Value; UriBuilder uriBuilder = new UriBuilder(); uriBuilder.Query = getQuery; uriBuilder.Port = _port; return uriBuilder.Uri; } private static async Task<string> GetTcpResponseAsync(TcpClient client, CancellationToken cancellationToken) { NetworkStream networkStream = client.GetStream(); byte[] readBuffer = new byte[1024]; StringBuilder stringBuilder = new StringBuilder(); int numberOfBytesRead = 0; // Incoming message may be larger than the buffer size. do { numberOfBytesRead = await networkStream.ReadAsync(readBuffer, 0, readBuffer.Length, cancellationToken) .ConfigureAwait(false); string s = Encoding.ASCII.GetString(readBuffer, 0, numberOfBytesRead); stringBuilder.Append(s); } while (networkStream.DataAvailable); return stringBuilder.ToString(); } private async Task WriteResponseAsync( string message, NetworkStream stream, CancellationToken cancellationToken) { string fullResponse = $"HTTP/1.1 200 OK\r\n\r\n{message}"; var response = Encoding.ASCII.GetBytes(fullResponse); await stream.WriteAsync(response, 0, response.Length, cancellationToken).ConfigureAwait(false); await stream.FlushAsync(cancellationToken).ConfigureAwait(false); } public void Dispose() { _tcpListener?.Stop(); } }
With all this in place, the browser opens when connecting to the first database on a resource, and the token is reused between connections.
Windows logins are not supported in this version of SQL Server.