SQL CLR function fails to impersonate when SQL Server is running as a domain account instead of SYSTEM or NETWORK SERVICE

I have the following code working perfectly on a test box, which runs the MSSQLSERVER service as SYSTEM:

      currentIdentity = SqlContext.WindowsIdentity;
      impersonatedIdentity = currentIdentity.Impersonate();

       client.PreAuthenticate = true;
 开发者_StackOverflow中文版      client.Credentials = CredentialCache.DefaultNetworkCredentials;
       client.Url = currentURL;

   /* Encrypt */
   try
   {
      encryptedText = client.Encrypt(plainText);
   }
   finally
    {
        if (impersonatedIdentity != null)
        {
            impersonatedIdentity.Undo();
        }
    }

        return encryptedText;

The basic idea of this function is to encrypt/decrypt text via a web service hosted internally. Whenever this function is called on a server that is running the MSSQLSERVER service as 'SYSTEM' or some other local account, the impersonation works fine. If the server is running the MSSQLSERVER service as a domain account (i.e. DOMAIN\sqlaccount), the credentials of 'DOMAIN\sqlaccount' are always passed to the web service, and impersonation does not seem to be working.

Is this expected behavior? Thanks in advance.

For the service account that you're running your SQL server as, does that account have impersonation turned on? There's a tab that's called delegation in the properties window for the user within active directory. You'll have the choice of not allowing delegation, allowing delegation indiscriminately, or configuring what services that account is authorized to delegate to. The default is "no trust" which may be your issue. Give it a look and see what you find.

Move your encryption function call out of SQLCLR. Making web service calls from SQL is a very very very bad idea. SQL Server resources are far too precious to waste them waiting on a HTTP response, even an internal intranet one. I have never ever encountered a reasonable explanation why to make the web service call from inside SQLCLR, other than short sighted coolness factor. Call the web service from the application, is the proper place to wait for HTTP responses.

What protocol are you using for authentication?

If you are using Kerberos I would expect this behavior. In Kerberos the SYSTEM and NETWORK SERVER SPNs are automatically defined. However, if you want to use a custom service name you need to define an SPN (for that service name) for authentication to work.