How do I map a credential to a SQLCLR assembly in SQL Server?

Question

I have a CLR assembly in SQL Server which I need to inherit a particular account's permissions. The CLR function basically just grabs a webpage and returns it to SQL in this fashion:

    [SqlFunction]
    [return: SqlFacet(MaxSize = -1)]
    public static SqlChars Get(SqlChars H, SqlChars url)
    {
        var client = new WebClient();

        client.Credentials = CredentialCache.DefaultNetworkCredentials;

        AddHeader(H, client);
        return new SqlChars(
                client.DownloadString(
                    Uri.EscapeUriString(url.ToSqlString().Value)
                    ).ToCharArray());
    }

I want that to be able to authenticate via NTLM using a specific accounts details. This works fine in a C# program outside SQL but as a CLR function it just returns 401 Unauthorized messages, which is because the DefaultNetworkCredentials are those of the SQL Server Service account (I confirmed this by setting the service to use MY credentials, and the CLR then worked perfectly)

My understanding is that the way to do this is create a Credential in SQL Server with that account's details, since that's what the documentation says:

Credentials provide a way to allow SQL Server Authentication users to have an identity outside of SQL Server. This is primarily used to execute code in Assemblies with EXTERNAL_ACCESS permission set.

I can do that, but what I can't seem to do is find any way to map that credential to the assembly. How do I do that?

Answer 1

I am not sure that Credentials in SQL Server actually work that way (the way described in that one documentation page). But even if they could be used for external operations via SQLCLR modules, it would not be by assigning anything to the assembly. Credentials in SQL Server are mapped to SQL Server logins. Hence you would map a Credential to one or more SQL Server logins that would be executing the SQLCLR-based module.

And, if you need the external operation to use the security context other than the default — the SQL Server service account — then you need to activate Impersonation within the .NET code. Doing that will assume the security context of the Windows account that is executing the SQLCLR-based module within SQL Server. SQL Server logins are not Windows accounts which is why they typically cannot do Impersonation within the .NET code. Perhaps by mapping a T-SQL Credential to a SQL Server login it would then be possible (I have tested this and it does not work; the documentation is very much incorrect; I will submit a correction for it on GitHub). Please keep in mind that the WindowsIdentity and WindowsImpersonationContext objects are disposable, so you need to either wrap them in a using(...){} construct, or at least call the Dispose() methods in the finally section of a try/catch/finally or try/finally block. If you are using Windows Auth logins, then you can do this impersonation, but it will impersonate whichever login is executing the module, which I assume can be different accounts (i.e. not always yours) so they would all need access to the external resource.

Another option is to simply supply the credential directly in the .NET code via new NetworkCredential(). Doing this would not only work for SQL Server logins, but it would allow for a singular account to connect as (you certainly could look up credentials from a list to use different ones based on some condition, but this at least allows for accessing the remote resource via a single account).