How do I programmatically check whether a given AD Group Managed Service Account can connect to SQL Server when using Integrated Security?

Question

I'll provide some background for my problem prior to jumping into what's going on to hopefully provide some insight into what I'm trying to do. Then I'll go into the actual issue I'm having.

Background

I'm currently developing a C# application that configures Windows Services, deploys SQL Server databases, IIS web sites, and MSMQ queues to set up an enterprise web application. In 99% of cases, the web application is installed on one Windows Server machine (2012 and later), while the database are deployed onto a separate Windows Server machine (2012 and later) responsible for running SQL Server (2012 and later). Since this is an enterprise application, I've been looking into running the services and IIS app pool under a Group Managed Service Account (GMSA). For those curious souls that stumble across this question, GMSAs are a special kind of domain account that can be used to run services and IIS App Pools across multiple Windows Servers have their passwords fully managed by Active Directory (password strength/length, how often the password is changed, etc...). So, for the domain administrator/IT department managing this application for our customers, using a GMSA solves some major headaches. For more on GMSAs, see: https://learn.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2012-R2-and-2012/jj128431(v=ws.11)

Task at hand

Given that I want to allow our customers to run the application using GMSAs, I have to do the following:

1. Test to make sure the GMSA is configured for use on the local machine
2. Test to make sure the GMSA can access SQL Server
3. Configure SQL Server permissions for the GMSA
4. Deploy and run the Windows Services and IIS App Pool as the GMSA

What I've tried

By running the following Powershell commandlet, I know that the GMSA is setup correctly on the IIS Web Server and SQL Server machines. It returns true if the machine account can access the GMSA's password.

Test-ADServiceAccount -Identity myGMSA_svc

Currently, I'm stuck trying to figure out #2. For normal domain user accounts, I can impersonate the user by importing the Win32 API's LogonUser function, creating a token handle and using WindowsIdentity.Impersonate:

public const int LOGON32_LOGON_INTERACTIVE = 2;
public const int LOGON32_PROVIDER_DEFAULT = 0;


[DllImport("advapi32.dll", SetLastError = true, CharSet = CharSet.Unicode)]
public static extern bool LogonUser(String lpszUsername, String lpszDomain, String lpszPassword, int dwLogonType, int dwLogonProvider, out SafeTokenHandle phToken);

public SafeHandle CreateSafeHandle(string domain, string username, string password)
{
    SafeTokenHandle safeTokenHandle;
    bool logonSuccess = LogonUser(username, domain, password, LOGON32.LOGON32_LOGON_INTERACTIVE, LOGON32.LOGON32_PROVIDER_DEFAULT, out safeTokenHandle);

    if (!logonSuccess)
    {
        int win32Error = Marshal.GetLastWin32Error();
        throw new Win32Exception(win32Error);
    }

    return safeTokenHandle;
}

private WindowsImpersonationContext ImpersonateUser(string domain, string username, string password)
{
    try
    {
        _impersonatedUserToken = _safeHandleFactory.CreateSafeHandle(domain, username, password);
        return WindowsIdentity.Impersonate(_impersonatedUserToken.DangerousGetHandle());
    }
    catch (Win32Exception e)
    {
        Log.Error(e);
        throw new Exception(e.Message, e);
    }
}


public bool CanConnect(string domain, string username, string password, string connectionString)
{
    try
    {
        using (WindowsImpersonationContext impersonatedUser = ImpersonateUser(domain, username, password))
        {
            var builder = new SqlConnectionStringBuilder(connectionString) { InitialCatalog = "master" };
            using (var connection = new SqlConnection(builder.ToString()))
            {
                connection.Open();
                using (var command = new SqlCommand(SqlProductVersionQuery, connection))
                using (var query = command.ExecuteReader())
                {
                    var databaseVersionStr = query.GetFieldValue<string>(0);
                    var databaseVersion = new Version(databaseVersionStr);
                }
            }
        }
        return true;
    }
    catch
    {
        return false;
    }
}

I've tried using the following modifications to the LogonUser call:

LogonUser(username, domain, password, LOGON32_LOGON_SERVICE, LOGON32.LOGON32_PROVIDER_DEFAULT, out safeTokenHandle);
LogonUser(username, domain, password, LOGON32_LOGON_SERVICE, LOGON32_PROVIDER_WINNT50, out safeTokenHandle);

In either case though, I get the following error message:

System.ComponentModel.Win32Exception (0x80004005): The user name or password is incorrect

This leads me to my actual question:

How do I check whether the GMSA can connect to SQL Server when using Integrated Security?

Answer 1

If I'm correct what you need is to check whether a given user has permission for a SQL Instance / Database. If that is the case you can do it via the following SQL query.

USE [master]

SELECT name 
FROM [sys].[server_principals]
WHERE name = N'domainName\userName';

You can come up with an inline function to accept the username as a parameter and return a boolean indicating the status.