Last night, I got a case to write a function in SQL Server 2005 to support the user account and password check against Active Directory. The user requirement is quite clear:
- Create a scalar-valued function named LDAPUserCheck;
- Parameter @username nvarchar(MAX) for user name to check;
- Parameter @password nvarchar(MAX) for password matching the username specified;
- Return bit 1 if succeeded, or 0 for all other reasons.
After a digging, I found that LDAP password authentication is not supported directly by SQL Server. But SQLCLR is a way to build the native dotnet program into SQL Server. In a new created SQLCLR project in VS 2005, I realized it’s unable to add the reference System.DirectoryServices.AccountManagement, which is required by running the code for Active Directory authentication. But a Web Service is a choice.
My steps to achieve that:
1 Create and deploy a Web Service for the authentication check.
1.1 Create a Web Service project.
1.2 Add System.DirectoryServices.AccountManagement as a reference.
1.3 Add a setting DomainName as string for storing the domain name.
1.4 Add a service like this:
public class LDAPAuthentication : System.Web.Services.WebService
{
static string domainName = Settings.Default.DomainName;
[WebMethod]
public bool Check(string userName, string password)
{
using (PrincipalContext pc = new PrincipalContext(ContextType.Domain, domainName))
{
// validate the credentials
bool isValid = pc.ValidateCredentials(userName, password);
return isValid;
}
}
}
1.5 Deploy this service and use a application pool running by a domain user. This user should be added to IIS_WPG group in Windows Server 2003.
2 Create a SQLCLR project to call the Web Service.
2.1 Create a SQLCLR project in Visual Studio 2005.
2.2 Add a Web Service reference. It’s named as LDAP in my project.
2.3 Add a User Defined Functions.
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean LDAPUserCheck(
SqlString username, SqlString password)
{
using (LDAPAuthentication.LDAP.LDAPAuthentication service = new LDAPAuthentication.LDAP.LDAPAuthentication())
{
if (service.Check(username.ToString(), password.ToString()))
{
return SqlBoolean.True;
}
else
{
return SqlBoolean.False;
}
}
}
2.4 Set Permission Level to External in Database page of project setting.
2.5 Build this project to get the dll files. In my case, these files are named LDAPAuthentication.dll and LDAPAuthentication.XmlSerializers.dll.
3 Deploy this project into SQL Server 2005.
3.1 Enable the CLR support in SQL Server 2005 by running this code:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO
3.2 Create a database for storing this function. Or, you can use any existed database as well. In my case, I created a database “tester”.
3.3 Set trustworthy on this database by running:
ALTER DATABASE [tester] SET trustworthy ON3.4 Copy the 2 dll files created in step 2 to this server. In my case, these are stored in C:\SQLCLR folder.
3.5 Create assemblies in SQL Server by running:
create assembly [LDAPAuthentication] from 'C:\SQLCLR\LDAPAuthentication.dll' with permission_set = external_access
create assembly [LDAPAuthentication.XmlSerializers] from 'C:\SQLCLR\LDAPAuthentication.XmlSerializers.dll' with permission_set = external_access
3.6 Create function to run the method we’ve created in VS 2005:
CREATE FUNCTION [dbo].[LDAPUserCheck](@username [nvarchar](4000), @password [nvarchar](4000))
RETURNS [bit] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [LDAPAuthentication].[UserDefinedFunctions].[LDAPUserCheck]Now everything is done. You can call this function like all others created by SQL. Run this for test.
select dbo.LDAPUserCheck('myusername','mypassword')
