3

I am calling a web service from a SQLCLR TVF in SQL Server 2008 R2.

On the IIS server this service has open permissions. When tested using GET or POST requests on a browser the web service works as expected. However, when the function calls the service, it returns:

The request failed with HTTP status 401: Unauthorized.

In the typical anecdotal way, when I run the TVF on my machine to access the service on my machine, it works. (proof that code is correct)

This error occurs when using a SQL Server on the network accessing another ISS server on the network. Both servers are in the same domain.

How to determine the missing permissions that prevent this TVF from working?

  • 1
    What service account does SQL Server use please? – gbn Jun 23 '11 at 15:35
  • The service account is a domain account. The TVF is using the identity of the caller to execute the web service. – MauMen Jun 23 '11 at 16:02
  • Just going with the obvious---the web service either allows anonymous access or the domain account is authorized to access the service? – billinkc Jun 23 '11 at 20:03
  • @MauMen have you given up on this question? – billinkc Jul 1 '11 at 13:29
3

A couple basic SQL Server checks: Is your database set to trustworthy? Is the assembly set to allow UNSAFE execution?

If that seems to be working, there are a couple of tutorials out on the web that might help. If none of the tutorials out there help, then it sounds like a permissions issue. (Unfortunately, that gets tricky, as this thread discussing Kerberos versus NTLM authentication shows.)

One other check--as Maumen mentioned--make sure that you have anonymous access setup for IIS.

Ultimately, though, it sounds like something you want to avoid, if you can. The performance and memory implications, the difficulty getting all the accounts working, and decreasing all the security settings would make me a bit nervous.

2

Given that the error is occurring in the external service (i.e. IIS), the problem is not the security level of your Assembly. If it was the PERMISSION_SET of the Assembly you would be getting a .NET SecurityException of type WebPermission.

According to a comment by the O.P. on the question, we know that:

The service account is a domain account. The TVF is using the identity of the caller to execute the web service.

Determining the "identity of the caller" is not so straight-forward.

First, this could be referring to the EXECUTE AS clause of the CREATE FUNCTION statement, implying that it is set to CALLER (which it usually is anyway). That will be necessary, but by itself does not make external calls in SQLCLR objects take on the identity of the Windows Login that is executing the SQLCLR object.

Second, there are credentials to pass to the WebClient / HttpRequest object. If setting the UseDefaultCredentials property to true, one might think that the Login executing the code in SQL Server should get passed along, especially with the remarks on that property stating that requests will "be authenticated using the default credentials of the currently logged on user". However, by default, all requests executed by the CLR host within SQL Server are made using the security context of the service account running the MSSQLSERVER service (if a default instance, else something like MSSQL$InstanceName).

Third, in order to get security context of external operations to be of the Login executing the SQLCLR object, you need to use Impersonation. This requires getting the WindowsIdentity from the SqlContext and calling the Impersonate method (when done, you will need to call Undo() and then Dispose() ).

IF that does not work as expected, it might be due to the other part of the remark on the UseDefaultCredentials property:

For middle tier applications, such as ASP.NET applications, instead of using this property, you would typically set the Credentials property to the credentials of the client on whose behalf the request is made.

In which case you might want to try setting the credentials as suggested.

OR, if you are using Impersonation and UseDefaultCredentials and still facing this error, then given this info:

This error occurs when using a SQL Server on the network accessing another ISS server on the network.

you could very well be running into the infamous double-hop authentication problem that has wasted many an hour (especially when it comes to Linked Servers). This happens when a real person, or service (e.g. IIS), logs into machine A and connects to SQL Server on machine B using a Trusted Connection / Integrated Security. That is hop # 1 (since the person or service did not provide credentials / password directly to machine B). Then, the same Windows Login that just connected to SQL Server on machine B tries to connect to machine C, again using a Trusted Connection / Integrated Security. This would be hop # 2. By default, hop # 2 is not allowed, most likely because it is riskier from a security perspective because the source of the connection to machine C is machine B which is really just a proxy for the real logon event that occurred on machine A. However, there is a way around this, simplistically being:

  1. Use Kerberos authentication
  2. Set up the required SPNs.
  3. If that doesn't work, then in Active Directory, enable "Delegation" for Logins that should be able to call the web service from SQL Server.

You should be able to get this to work without needing to configure the Web Service to allow anonymous access.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.