Using SQL CLR for Active Directory Integration

A little background:

Where I work development of Business Intelligence solutions is spread around our Enterprise. Many of the Divisions have technology staff we call System Integrators that know both the Division’s business processes and how to create technical solutions to meet the changing needs of each Division. As a Database Administrator I work in the centralized Information Technology division and I primarily have two responsibilities with regard to Business Intelligence:

  1. Ensure the organization has the hardware and software to meet the needs of the Divisions
  2. Provide consulting and training to the Division’s Systems Integrators so they can implement BI solutions at the Division level

We primarily have a single server dedicated to SQL Server Reporting Services. It is shared among many of the Divisions and security on that server is always a concern for our Systems Integrators. Many of the Divisions have reports they only want their own staff to see as well as reports they wish to share among other Divisions or even Enterprise wide. I’ll discuss how we organized that security model to empower the responsible staff and not burden central IT with a continuous stream of change requests in another post.

The meat of the post:

I’m often asked by Systems Integrators for statistics on report utilization. Obviously, being the DBA I can query the ReportServer database to find information about all the reports. I could implement some views to allow the Systems Integrators to view those same statistics but it is impossible to prevent them from viewing statistics on reports they do not have permission to view. Fortunately, the ReportServer database stores which users are granted which roles on every folder and report in the Catalog, PolicyUserRole, and Users tables. Using those tables we can find which users have access to which reports and create a stored procedure to return that information based on the user passed to that stored procedure.

The trouble with using a query without Active Directory integration is that users can be a member of a Windows group. So you need to know not only the user’s account name but all groups he may be a member of to find all the reports he has access to. My first thought to solve this problem was to create a Linked Server to Active Directory using the ADsDSOObject provider which is documented on MSDN. What I quickly found was the memberOf property is returned from AD as an array which we all know SQL Server cannot accept.

Enter SQL CLR:

All I needed to do was convert that pesky array to a comma delimited string so I can use it in T-SQL. I created a little CLR Stored Procedure that accepts a windows login and returns a comma delimited string of all groups the user is a member of. Once you have that comma separated list you can query the Catalog table joined on PolicyUserRole and Users to find all the reports a user or groups he’s a member of to report on only those reports he has access to. Problem solved.

Here’s the code:

using System;
using System.Collections.ObjectModel;
using System.Data.SqlTypes;
using System.DirectoryServices;
using Microsoft.SqlServer.Server;
public class ADGroups
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetGroupsForAccount(SqlString userName, out SqlString userGroups)
    {
        System.Security.Principal.WindowsImpersonationContext impersonatedIdentity = SqlContext.WindowsIdentity.Impersonate();
        Collection<string> adGroupMemberships = new Collection<string>();
        DirectoryEntry directoryEntry = new DirectoryEntry("LDAP://YourDomainControllerServerHostName/OU=YourOrganizationalUnit,dc=YourDomainName", "YourUser", "YourPassword");
 
        DirectorySearcher adSearcher = new DirectorySearcher(directoryEntry);
        if (userName.ToString().Contains("\\"))
        {
            userName = userName.ToString().Substring(userName.ToString().IndexOf("\\") + 1, userName.ToString().Length - userName.ToString().IndexOf("\\") - 1);
        }
 
        adSearcher.Filter = string.Format("samAccountName={0}", userName);
        adSearcher.PropertiesToLoad.Add("memberOf");
 
        try
        {
            SearchResult adSearchResult = adSearcher.FindOne();
 
            int equalsIndex;
            int commaIndex;
 
            foreach (string property in adSearchResult.Properties["memberOf"])
            {
                equalsIndex = property.IndexOf("=", 1);
                commaIndex = property.IndexOf(",", 1);
 
                if (equalsIndex == -1)
                {
                    throw new NullReferenceException();
                }
 
                if (!adGroupMemberships.Contains(property.Substring((equalsIndex + 1), (commaIndex - equalsIndex) - 1)))
                {
                    adGroupMemberships.Add("'" + property.Substring((equalsIndex + 1), (commaIndex - equalsIndex) - 1) + "'");
                }
            }
        }
 
        catch (Exception ex)
        {
            if (ex is NullReferenceException)
            {
                adGroupMemberships.Add(string.Format("'{0} is not a memeber of any groups'", userName));
            }
            else
            {
                adGroupMemberships.Add("'" + ex.Message + "'");
            }
        }
 
        finally
        {
            string[] results = new string[adGroupMemberships.Count];
            adGroupMemberships.CopyTo(results, 0);
 
            userGroups = String.Join(",", results);
 
            impersonatedIdentity.Undo();
        }
    }
}

Leave a Reply

Your email address will not be published. Required fields are marked *