6

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?

Martin Smith
  • 87,941
  • 15
  • 255
  • 354

2 Answers2

5

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

Ed Elliott
  • 775
  • 5
  • 12
3

After lots of testing (details of which can be found in the related discussion), we have narrowed down the factors that need to be in place for the hanging to happen, but not ultimately what is causing the hanging itself.

What we now know:

  1. When not connected to the VPN, there is no hanging at all. I suspect that this is due to the OS realizing that it cannot get to the Domain Controller to do any validation, so it falls back to cached credentials.
  2. If a test has run successfully (i.e. when not connected to the VPN), then the VPN can be connected and the tests continue to run. This suggests that permissions at the SQL Server and/or OS level are being cached. We don't yet have clarity as to where the caching is happening, but we have been able to at least rule out that the permissions are cached with the loaded Assembly (we did this by unloading the App Domain and then re-running the test with the VPN connected and it did not hang).
  3. After some amount of time (or so it seems right now due to where we got in the testing), if the VPN is still connected and the App Domain is unloaded, then attempting to run a test again will again cause the process to hang. The link posted in @Ed's answer seems to confirm the roughly 10-minute cache timeout that was seen in testing.
  4. When the process hangs, it is during a series of external calls to get Windows security info (you can see the stack trace that Martin posted in the discussion at: https://chat.stackexchange.com/transcript/message/40428311#40428311 — click the "(see full text)" link and then look for sqllang.dll!FVerifyAssemblyPermsForSids)
  5. When the Assembly is set to SAFE:
    1. The initial call to the Info() TVF succeeds as there are no permissions to check.
    2. But, the tSQLt.Private_Init stored procedure always attempts to mark the tSQLtCLR Assembly as EXTERNAL_ACCESS, in which case (assuming that the Asymmetric Key has not been installed):
      1. If TRUSTWORTHY is OFF then the call in the Init proc to the tSQLt.EnableExternalAccess proc fails with the "unauthorized for EXTERNAL_ACCESS or UNSAFE..." error because TRUSTWORTHY is not ON AND there is no Asymmetric Key. Yet it does not hang because that is a meta-data operation that doesn't need anything from the OS. This should be the call to ISECManager::FIsAssemblyAuthorized, which is one step before the FVerifyAssemblyPermsForSids step that was noted above.
      2. If TRUSTWORTHY is ON then the call in the Init proc to the tSQLt.EnableExternalAccess proc hangs because: a) it passes the "authorization" check, and b) then needs to check the permissions at the OS-level. Hence, it hangs on the ALTER ASSEMBLY tSQLtCLR WITH PERMISSION_SET = EXTERNAL_ACCESS; statement.
  6. When the Assembly is set to EXTERNAL_ACCESS:
    1. If TRUSTWORTHY is OFF then the call in the Init proc to the Info() TVF fails due to lack of authorization. This is the same issue as we saw above when TRUSTWORTHY was OFF. The error message is different, but that is only due to the query being in a TRY...CATCH construct that has a custom error message (the call to tSQLt.EnableExternalAccess is also within a TRY...CATCH construct, but that call does not override the error message in the CATCH block).
    2. If TRUSTWORTHY is ON then the call in the Init proc to the Info() TVF hangs as it reaches out to the OS to get the SID permissions info from the Domain Controller.

What's left to figure out:

  1. Where is the caching taking place, and for how long? We have been unloading the App Domain and possibly releasing SQL Server-based authentication caching via: DBCC FREESYSTEMCACHE('ALL') WITH MARK_IN_USE_FOR_REMOVAL;. However, we have yet to find a consistent, repeatable length of time. Thus far it appears to be about 9 minutes.
  2. If the VPN is connected and the Assembly is loaded into memory, and everything is working, then does the hanging ever occur again?
  3. Would this ever happen if the Database had TRUSTWORTHY OFF? This can be tested for by waiting until the hung state occurs again, then running EXEC tSQLt.InstallExternalAccessKey. Then, set TRUSTWORTHY OFF and try again.
    Having the Asymmetric Key and signature-based Login is definitely better than relying on TRUSTWORTHY ON, so it is probably best to switch over to that and have TRUSTWORTHY OFF regardless.
  4. Finally: why does it hang at all? And does any other process hang outside of SQL Server? Or is this an issue that is specific to SQL Server? @Sean Gallardy (in the discussion), suggested that:

    the reason why the operation takes so long seems to be an issue with contacting the DCs and/or retrieving your information (groups, etc). My guess would be mismatched networking by what you've described where one interface (internet) is somehow picked to lookup this information - which will never complete.

Solomon Rutzky
  • 70,048
  • 8
  • 160
  • 306