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
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
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
IF that does not work as expected, it might be due to the other part of the remark on the
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:
- Use Kerberos authentication
- Set up the required SPNs.
- 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.