SQL Server Agent is taking long time to start

  • Article

 

SQL Server Agent might take long time to start because of slow communications with Certificate Authorities.

 

If you enable verbose logging for SQL Server agent (-v) and look at the SQL Server agent log you will notice that 'ANALYSISQUERY' subsystem has taken long time to long

2012-02-15 15:42:42 - ? [124] Subsystem 'QueueReader' successfully loaded (maximum concurrency: 800)

2012-02-15 15:47:08 - ? [124] Subsystem 'ANALYSISQUERY' successfully loaded (maximum concurrency: 800)

2012-02-15 15:47:08 - ? [124] Subsystem 'ANALYSISCOMMAND' successfully loaded (maximum concurrency: 800)

 

Also if you collect dumps during the SQLServer agent startup you will notice the stack like one below.

 

ntdll!ZwWaitForSingleObject

kernel32!WaitForSingleObjectEx

cryptnet!CryptRetrieveObjectByUrlWithTimeout

cryptnet!CryptRetrieveObjectByUrlW

crypt32!ChainRetrieveObjectByUrlW

crypt32!CCertChainEngine::RetrieveCrossCertUrl

crypt32!CCertChainEngine::UpdateCrossCerts

crypt32!CCertChainEngine::Resync

crypt32!CCertChainEngine::CreateChainContextFromPathGraph

crypt32!CCertChainEngine::GetChainContext

crypt32!CertGetCertificateChain

wintrust!_WalkChain

wintrust!WintrustCertificateTrust

wintrust!_VerifyTrust

wintrust!WinVerifyTrust

mscorsec!GetPublisher

mscorwks!PEFile::CheckSecurity

mscorwks!PEAssembly::DoLoadSignatureChecks

mscorwks!PEAssembly::PEAssembly

mscorwks!PEAssembly::DoOpenHMODULE

mscorwks!PEAssembly::OpenHMODULE

mscorwks!AppDomain::BindExplicitAssembly

mscorwks!AppDomain::LoadExplicitAssembly

mscorwks!ExecuteDLLForAttach

mscorwks!ExecuteDLL

mscorwks!CorDllMainForThunk

mscoree!CorDllMainWorkerForThunk

mscoree!VTableBootstrapThunkInitHelper

mscoree!VTableBootstrapThunkInitHelperStub

SQLAGENT!LoadSubsystem

SQLAGENT!StartSubSystems

SQLAGENT!DumpAndCheckServerVersion

SQLAGENT!ServiceMain

advapi32!ScSvcctrlThreadW

kernel32!BaseThreadInitThunk

ntdll!RtlUserThreadStart

 

ANALYSISQUERY subsystem has assembly which has an Authenticode signature. When the CLR loads an assembly which has an Authenticode signature, it will always try to verify that signature.

This verification can be quite time intensive, since it can require hitting the network several times to download up to date certificate revocation lists, and also to ensure that there is a full chain

of valid certificates on the way to a trusted root.

If you can’t get to the internet to authenticate signature or want to bypass the Authenticode signature you can try creating a sqlagent.exe.config file with the following xml in Binn directory. This bypasses the check

 

Create a sqlagent.exe.config file with:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<runtime>
<generatePublisherEvidence enabled="false"/>
</runtime>
</configuration>

 

Thanks

Karthick P.K