Granting Permissions Based on AD User Group Membership
After several different trial and error expeditions, I finally have a working PageLoad event to check whether the currently logged in user is a member of a specific group in Active Directory.
The first step I used was a stored procedure to get a list of the user’s groups in Active Directory. We needed this for another project and I just reused it for this one.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[GetLdapUserGroups] Script Date: 11/18/2013 11:58:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetLdapUserGroups]
( @LdapUsername NVARCHAR(256) )
AS
BEGIN
DECLARE @Query NVARCHAR(1024), @Path NVARCHAR(1024)
SET @Query = 'SELECT @Path = distinguishedName FROM OPENQUERY(ADSI, '' SELECT distinguishedName FROM ''''LDAP://DC=YOURDOMAINHERE,DC=local'''' WHERE objectClass = ''''user'''' AND sAMAccountName = '''''
+ @LdapUsername + ''''' '') '
EXEC SP_EXECUTESQL @Query, N'@Path NVARCHAR(1024) OUTPUT', @Path = @Path OUTPUT
SET @Query = 'SELECT name AS LdapGroup FROM OPENQUERY(ADSI,'' SELECT name FROM ''''LDAP://DC=YOURDOMAINHERE,DC=local'''' WHERE objectClass=''''group'''' AND member='''''
+ @Path + ''''' '') ORDER BY name'
EXEC SP_EXECUTESQL @Query
END
GO
The second step was I created another stored procedure that looks at the list of groups generated by the first stored procedure and sees whether the specific group being tested is in that list of groups.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[GetLdapUserGroups] Script Date: 11/18/2013 09:35:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[aer_IsUserInGroup]
( @LdapUsername NVARCHAR(256), @GroupName NVARCHAR(50) )
AS
BEGIN
CREATE TABLE #usergroups (groups VARCHAR(50))
INSERT INTO #usergroups
EXEC dbo.GetLdapUserGroups @LdapUsername = @LdapUsername
SELECT CASE WHEN @GroupName IN(SELECT groups FROM #usergroups) THEN 1 ELSE 0 END
END
The last step was putting the following in my PageLoad event of the page I want to protect based on the user being a member of a certain group.
string LoggedOnUser;
System.Type oType = System.Type.GetTypeFromProgID("Wscript.Network");
object pc = System.Activator.CreateInstance(oType);
//Get NT userid data
LoggedOnUser = Request.ServerVariables["AUTH_USER"];
int pos = LoggedOnUser.IndexOf('\\');
if (pos > 0)
{
LoggedOnUser = LoggedOnUser.Substring(pos + 1);
}
SqlCommand cmd = new SqlCommand();
SqlDataAdapter adp = new SqlDataAdapter();
int isingroup;
string cs = null;
cs = "Data Source=YOURSQLSERVER;Initial Catalog=master;User Id=USERID;Password=PASSWORD;";
SqlConnection SQLCon = new SqlConnection(cs);
cmd = new System.Data.SqlClient.SqlCommand("aer_IsUserInGroup", SQLCon);
SQLCon.Open();
cmd.CommandType = CommandType.StoredProcedure;
System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["@LdapUsername"].Value = LoggedOnUser;
cmd.Parameters["@GroupName"].Value = "msds";
/* "msds" is the AD Group of which the user must be a member */
adp.SelectCommand = cmd;
isingroup = Convert.ToInt16(cmd.ExecuteScalar());
SQLCon.Close();
if (isingroup == 1)
{
admin.Visible = true;
}
The content I wanted to make sure was not accessible by people who were not in this group I put inside a
<div id="admin">Protected content here.</div>
tag. I have tested with multiple users and multiple groups and it seems to be working so far. I will easily be able to plug this in to any page on which I want to limit a user’s functionality based on their AD groups membership.
There are countless other ways to accomplish this, I’m sure, but this is the only one I was able to get to work rather easily.