Validate NT User via SQL CLR

Question

I need to be able to validate a given username and password against Active Directory and return whether that user exists.

My setup is that I have two web servers in a DMZ, and then a SQL Server in the LAN. The sales people have a admin panel that is on the web servers. When they login to the admin panel, I want it to run a stored procedure that will validate the username and password against the Active Directory on the LAN side (since the web servers do not have access to Active Directory). I tried making a SQL Stored Proc that does the following:

#region setup impersonation via interop
public const int LOGON32_LOGON_INTERACTIVE = 2;
public const int LOGON32_PROVIDER_DEFAULT = 0;

[DllImport("ADVAPI32.dll", SetLastError = true, CharSet = CharSet.Auto)]
public static extern bool LogonUser
    (
        string lpszUsername,
        string lpszDomain,
        string lpszPassword,
        int dwLogonType,
        int dwLogonProvider,
        ref IntPtr phToken
    );
#endregion

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean NTAuthenticateUser(SqlString UserName, SqlString Password)
{
    IntPtr token = IntPtr.Zero;

    return LogonUser
        (
            UserName.Value,
            "MYDOMAIN",
            Password.Value,
            LOGON32_LOGON_INTERACTIVE,
            LOGON32_PROVIDER_DEFAULT,
            ref token
        );

    return true;
}

However when I go to put it on the SQL Server, I get the error

CREATE ASSEMBLY failed because type "UserDefinedFunctions" in external_access assembly "SQLCLR" has a pinvokeimpl method. P/Invoke is not allowed in external_access assemblies.

Which happens because the solution is set to EXTERNAL permission level and apparently calling ASVAPI32.DLL cannot occur unless the solution is set to UNSAFE permission level(?).

So (finally) my question - is there some way to do this while still running the CLR with EXTERNAL permissions?

Thanks in advance!

Answer 1

I would suggest building a secure webservice that encapsulates your code. Then call it from SQL CRL or your web app.

HTH