English 中文(简体)
Validate NT User via SQL CLR
原标题:

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!

问题回答

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

HTH





相关问题
Anyone feel like passing it forward?

I m the only developer in my company, and am getting along well as an autodidact, but I know I m missing out on the education one gets from working with and having code reviewed by more senior devs. ...

NSArray s, Primitive types and Boxing Oh My!

I m pretty new to the Objective-C world and I have a long history with .net/C# so naturally I m inclined to use my C# wits. Now here s the question: I feel really inclined to create some type of ...

C# Marshal / Pinvoke CBitmap?

I cannot figure out how to marshal a C++ CBitmap to a C# Bitmap or Image class. My import looks like this: [DllImport(@"test.dll", CharSet = CharSet.Unicode)] public static extern IntPtr ...

How to Use Ghostscript DLL to convert PDF to PDF/A

How to user GhostScript DLL to convert PDF to PDF/A. I know I kind of have to call the exported function of gsdll32.dll whose name is gsapi_init_with_args, but how do i pass the right arguments? BTW, ...

Linqy no matchy

Maybe it s something I m doing wrong. I m just learning Linq because I m bored. And so far so good. I made a little program and it basically just outputs all matches (foreach) into a label control. ...

热门标签