Sql-server – Why would I get PREEMPTIVE_OS_AUTHORIZATIONOPS using tSQLt when Working at Home

sql serversql-clrtsqltwait-types

I have an SSDT project containing tSQLt unit tests.

I always find when working at home that publishing this and running all tests (from a post deploy script) is problematic (against both localdb and SQL Server developer edition).

The publish hangs indefinitely and I eventually have to kill visual studio.

The wait type is PREEMPTIVE_OS_AUTHORIZATIONOPS and an example of a statement hung waiting for this (from sys.dm_exec_sql_text) is 

(@r BIT OUTPUT)
SELECT @r = CASE
              WHEN I.Version = I.ClrVersion THEN 1
              ELSE 0
            END
FROM   tSQLt.Info() AS I;

I also reproduce this by calling

SELECT tSQLt.Private::Info()

Which is a simple method

public static SqlString Info()
{
  return (SqlString) Assembly.GetExecutingAssembly().GetName().Version.ToString();
}

I assume that it is trying to contact the domain controller to establish that I have some permissions or other. I don't get this with other CLR assemblies and so suspect that this may be something to do with TSQLT not being a SAFE_ACCESS assembly (permission set is EXTERNAL_ACCESS).

Anyone know what's going on here and how I can fix this and work disconnected from my company's network without encountering this?

Best Answer

If you aren't going to be connected to the domain all the time, as it is a development instance, change the db owner from a domain account to a local account or a sql account.

Check this great blog out on the subject:

http://andreas-wolter.com/en/where-is-that-preemptive-wait-coming-from/

To summarise the blog post:

  • Having a db owner as a domain user causes kerberos ticket granting ticket requests (i.e. sql wants to authenticate using the domain account that is a db owner and kerberos has a thing called ticket granting ticket which lets sql impersonate the db owner)
  • If you have high latency (or issues connecting, as in this case) to your domain controller, these calls will be slow
  • SQL caches the calls for 10 minutes so these can appear intermittent

ed

Related Question