1

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?

Improve this question
3
  • I'm not sure that the documentation is correct in that particular statement. That doesn't seem to show up on any other Credential-related page. Either way, Credentials are mapped to SQL Server logins, not to assemblies. And, when you say "inherit a particular account's permissions", are you specifically meaning the Windows account to use for external operations, such as file system, network, registry, etc? Can you please update the question to clarify exactly what you are attempting to do that requires the other account's permissions? thanks. Commented Jan 15, 2020 at 15:32
  • @SolomonRutzky updated to explain - I want it to do some NTLM auth with a particular accounts details. By default, it's picking up the SQL Server Service account's details Commented Jan 15, 2020 at 15:48
  • Hi Dan. I have updated my answer with the results of my testing. The documentation seems to be erroneous and Credentials in fact cannot be used in this manner. But, I do note two alternatives. Commented Jan 15, 2020 at 20:55

1 Answer 1

Reset to default
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).

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

thanks; this is a really comprehensive answer, and I appreciate the testing effort. The Impersonation might work actually, the only people who'll be running this will have access to the resource already. I am aiming to avoid using new NetworkCredential() as it means storing credentials somewhere, and that always gives me the willies.
@DanScally You're welcome. And I completely understand re: not wanting to store passwords, especially if they need to be in plain text. There might be other ways to create the credentials, but I don't have time to look into that. Still, if there users who need access all have Windows logins and those logins have access to the remote resource, then yes, use Impersonation... Good luck!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.