MSSQLWIKI

Karthick P.K on SQL Server

Archive for April, 2012

SQL Server Agent is taking long time to start

Posted by Karthick P.K on April 19, 2012

 

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 start

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

Posted in SQL General | Tagged: , , , | 1 Comment »

(SQLServer) Initializing the FallBack certificate failed with error code: 1, state: 1, error number: -2146893802.

Posted by Karthick P.K on April 19, 2012

SQL Server might fail to start with below error

Server Error: 17190, Severity: 16, State: 1.

Server Initializing the FallBack certificate failed with error code: 1, state: 1, error number: -2146893802.

Server Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate

Error: 15466, Severity: 16, State: 1.

spid7s An error occurred during decryption.

Cause

CryptAcquireContext function is used by SQL Server to acquire a handle to key containers, create key containers and destroy key containers.

By default CryptAcuireContext function create key in “Roaming\Microsoft\Crypto\..” under path mentioned in below registry

HKEY_USERS\S-1-X-XXX\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders\AppData

If the AppData Key is missing or if the user don’t have permission in path mentioned in above registry or if user profile is corrupted we might end up with above error.

To narrow down the issue outside SQL-Server run THIS executable which will Open or Create key container if it doesn’t exist. If the exe fails look at error code returned by exe and troubleshoot further.

To check if the problem is because of corrupted profile modify the path mentioned in HKEY_USERS\S-1-X-XXX\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders\AppData folder to a

different path and check if the exe is able to create the key container.

 

Source  code for Exe is below

#include <windows.h> 
#include <string> 
#include <winbase.h> 
#include <iostream> 
using namespace std;
#include <Wincrypt.h >
 
                                      
void main()
{
LPCSTR rgwchKeyContName = "Test123456";  
HCRYPTPROV m_hCryptoProviderFB;
BOOL ret;
BOOL ret2;

ret=CryptAcquireContext(&m_hCryptoProviderFB, rgwchKeyContName, MS_ENHANCED_PROV, PROV_RSA_FULL, CRYPT_SILENT);
    
if (!ret && GetLastError() == NTE_BAD_KEYSET)

{
    
    printf("\nUnable to open Keyset.CryptAcquireContext failed with error: 0x%X . \nWe will try creating key",GetLastError());

    ret2=CryptAcquireContext(&m_hCryptoProviderFB, rgwchKeyContName, MS_ENHANCED_PROV, PROV_RSA_FULL, CRYPT_NEWKEYSET | CRYPT_SILENT);
        if (!ret2)
        {
        printf("\nCryptAcquireContext failed creating key.Error: 0x%X",GetLastError());
        }
        else
        {
        printf("\nKey created");
        }
    exit;
}


else if (!ret && GetLastError() == NTE_BAD_KEYSET)
{
printf("CryptAcquireContext failed with error: 0x%X",GetLastError());
}

else
{

    printf("CryptAcquireContext opened key. Return value is 0x%X.",ret);
}

    if (CryptReleaseContext(m_hCryptoProviderFB,0))
    {
    printf("\nHandle is released.\n");
    }
    else
    {
    printf("\nHandle could not be released.\n");
    }

}

 

 

Thanks

Karthick P.K

Posted in Configuration, Security, Startup failures | Tagged: , , , , , , , | 19 Comments »

 
%d bloggers like this: