Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I am calling a web service from a CLR TVF in SQLServer 2008R2.

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 anectdotal 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 returns 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?

share|improve this question
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

1 Answer

up vote 2 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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