C#: Check If User Has Access To Server Without Attempting Connection

Question

Good afternoon,

I am currently facing an issue where I have a list of server names, and I need to verify that a windows authenticated user has permissions to the specified server names without attempting to establish a connection to the specified server, before I present the list of servers to them. So for example:

Servers: A, B, C, D

Joe has permissions to A and D but not B and C so Joe should only see A and D in his server list.

How should I be tackling this issue? Should I be pulling from Active Directory? I know how to pull my user's identity, but where to pull the server information and find out if a user has permissions is a completely different story. Any documentation, code samples, articles, etc. are helpful.

Notes About The Work Environment

1. All of the servers in the list are database servers running SQL Server 2008 R2 and higher.
2. This is a government environment where there are heavy restrictions.
3. I am unable to modify Active Directory in any way.
4. I can query Active Directory and SQL Server all day, provided the user has permissions.
5. Using third party libraries and tools are not authorized.

Side Note About The Server List

This list is stored in a database, however I don't think this really helps with the permissions side. I've already solved the issue of checking permissions against the individual databases with the following SQL query:

SELECT name
FROM sys.databases
WHERE HAS_DBACCESS(name) = 1
ORDER BY name

Thank you for your help!

-Jamie

Conclusion

Essentially, I have found no possible way to query the AD groups on the remote server without attempting to establish a connection, thus the execution of all samples found will flag the users account with IA. For others with a similar issue that don't have to worry about IA dropping a hammer on users, I have included a few solutions below you can try to meet your needs (all of the following will work when implemented correctly).

Solutions

1. Query the server in active directory and retrieve the groups from the server, if this fails 9 times out of ten the exception message will be 'Access is denied.'. If it succeeds, proceed to pull the current user's groups and compare to the groups pulled from the server. The selected answer to this post combined with this post and/or this post will get you where you need to be with this solution.

2. If you have access to the server already through impersonation or other means (SQL Server Auth) then you can use the following to see if the member has any server roles assigned:

   SELECT IS_SRVROLEMEMBER('public')

3. You could also use the Login class available in the Microsoft.SqlServer.Smo assembly using the Microsoft.SqlServer.Management.Smo namespace. However, you may or may not have issues moving the Microsoft.SqlServer.SqlClrProvider namespace from the GAC to the BIN. More information about this can be found at this StackOverflow post, and at this Microsoft Connect thread which states the following:

Client applications should not be using the assemblies from the Program Files folders unless they are from the specific SDK folders (such as "C:\Program Files (x86)\Microsoft SQL Server\130\SDK")

4. You could even do a basic connection test wrapped in a try catch to see if the connection will work.

   using (SqlConnection conn = new SqlConnection(connString)) { try { conn.Open(); conn.Close(); } catch (Exception e) { Console.Write($"Connection test failed: {e.Message}"); } }

There are a small variety of ways to achieve the overall goal, it just depends on your particular situation and how you want to approach it. For me, none of the solutions will work since in each scenario the test will attempt a connection to the server in question which will flag the user due to lack of permissions.

Answer 1

If you have Active Directory implemented then you should be giving users access rights to things like servers via AD groups anyways or else that creates a management nightmare. Imagine if John Smith joins your company as a sys admin, are you going to go to every server and explicitly assign him rights? Much easier to just create a server admin AD group then assign it to the server (or dictate what AD groups exists on servers and permission levels by group policy.

Why this also helps you is that when you develop applications, you can use the built in AD role provider to serve up things like this. Here is a simple example of grabbing a users groups by AD user Name

    using System.DirectoryServices.AccountManagement;

    public List<string> GetGroupNames(string userName)
    {
        List<string> result = new List<string>();
        using (PrincipalContext pc = new PrincipalContext(ContextType.Domain, "YOURDOMAINHERE"))
        {
            using (PrincipalSearchResult<Principal> src = UserPrincipal.FindByIdentity(pc, userName).GetGroups(pc))
            {
                src.ToList().ForEach(sr => result.Add(sr.SamAccountName));
            }
        }
        return result;
    }

EDIT: So if you absolutely refuse to use active directory groups to manage permissions on servers and buying a tool is out of the question, here is a class that will iterate through all of your local machine groups and give you a list of users within those groups. You could do something like have it run as a scheduled task on the server (or win service) and save it's results back to a DB so you can query or build a UI to pull and monitor this info at any time. This doesn't reach out and grab sql server permissions as you said you already have that.

public class MachinePermissions
{
    string machineName { get; set; }
    public List<LocalGroup> localGroups { get; set; }

    public List<string> GetGroupMembers(string sGroupName)
    {
        List<String> myItems = new List<String>();
        GroupPrincipal oGroupPrincipal = GetGroup(sGroupName);
        PrincipalSearchResult<Principal> oPrincipalSearchResult = oGroupPrincipal.GetMembers();
        foreach (Principal oResult in oPrincipalSearchResult)
        {
            myItems.Add(oResult.Name);
        }
        return myItems;
    }

    private GroupPrincipal GetGroup(string sGroupName)
    {
        PrincipalContext oPrincipalContext = GetPrincipalContext();
        GroupPrincipal oGroupPrincipal = GroupPrincipal.FindByIdentity(oPrincipalContext, sGroupName);
        return oGroupPrincipal;
    }

    private PrincipalContext GetPrincipalContext()
    {
        PrincipalContext oPrincipalContext = new PrincipalContext(ContextType.Machine);
        return oPrincipalContext;
    }

    public MachinePermissions()
    {
        machineName = Environment.MachineName;
        PrincipalContext ctx = new PrincipalContext(ContextType.Machine, Environment.MachineName);
        GroupPrincipal gp = new GroupPrincipal(ctx);
        gp.Name = "*";
        PrincipalSearcher ps = new PrincipalSearcher();
        ps.QueryFilter = gp;
        PrincipalSearchResult<Principal> result = ps.FindAll();
        if(result.Count() > 0)
        {
            localGroups = new List<LocalGroup>();
            foreach (Principal p in result)
            {
                LocalGroup g = new LocalGroup();
                g.groupName = p.Name;
                g.users = GetGroupMembers(g.groupName);
                localGroups.Add(g);
            }
        }
    }
}

public class LocalGroup
{
    public string groupName { get; set; }
    public List<String> users { get; set; }
}

Answer 2

You can create AD group for accessing each database, then add users to them. In your app you can add list of groups and check if user Is member of them.

It's common practice and allow to create secure scenarios for different access right for different users. You only set permissions for group once and all members can benefit from access rights.