BUG: You cannot enable encryption by using a certificate when SQL Server 2005 is running under the Network Service account

Article translations Article translations
Article ID: 900495 - View products that this article applies to.
BUG #: 374329 (SQLBUDT)
Expand all | Collapse all

SYMPTOMS

When Microsoft SQL Server 2005 is running under the Network Service account, you cannot enable encryption by using a certificate. If you provision a certificate for use in encryption, SQL Server will not start. Additionally, you may notice the following message in the SQL Server error log:

Date Time Server The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030d. Check certificates to make sure they are valid.
Date Time Server Error: 26014, Severity: 16, State: 1.
Date Time Server Unable to load user-specified certificate. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.

Date Time Server Error: 17195, Severity: 16, State: 1.
Date Time Server Server cannot startup because server is configured to require encryption but network libraries cannot support encryption.
Date Time Server Error: 17182, Severity: 16, State: 1.

CAUSE

This problem occurs because the Network Service account does not have permissions to read the private key containers.

RESOLUTION

To resolve this problem, compile and run the following .cpp code.

Note This code grants all services that are running under the Network Service account access to the machine store. Use this code with discretion.
/*
	MKACLS - This utility will list the machine key containers present and allow 
	the user to change the key container DACL.
*/

#include <stdio.h>
#include <windows.h>
#include <sddl.h>
#include <aclapi.h>
#include <lm.h>

//Get SD for retrieving DACL.
SECURITY_DESCRIPTOR* GetSecurityDescDacl(HCRYPTPROV hProv) {
 
	SECURITY_DESCRIPTOR *sd;
	unsigned long size = 0;

	CryptGetProvParam(
		hProv,
		PP_KEYSET_SEC_DESCR,
		0,
		&size,
		DACL_SECURITY_INFORMATION);

	int ret = GetLastError();
	if (ret != ERROR_INSUFFICIENT_BUFFER) {
		fprintf(stderr, "Error getting file security DACL: %d\n", ret);
		return 0;
	}

	sd = (SECURITY_DESCRIPTOR *) malloc(size);
	if (! sd) {
		fprintf(stderr, "Out of memory for security descriptor!\n");
		return 0;
	}

	CryptGetProvParam(
		hProv,
		PP_KEYSET_SEC_DESCR,
		(BYTE*)sd,
		&size,
		DACL_SECURITY_INFORMATION);

	return sd;
}

//Get DACL from SD.
ACL* GetDacl(SECURITY_DESCRIPTOR *sd) {
  
	ACL *acl;
	int defaulted, present;

	if (! sd) return 0;

	if (! GetSecurityDescriptorDacl(
		sd, 
		&present, 
		&acl, 
		&defaulted)) 
	{
		fprintf(stderr, "Error getting DACL from security descriptor: %d\n", GetLastError());
		return 0;
	}

	if (! present) {
		fprintf(stderr, "Security descriptor has no DACL present\n");
		free(acl);
		return 0;
	}

	return acl;
}


ACL *AddSidToAcl(const ACL *pOldACL, PSID pSID)
{
	EXPLICIT_ACCESS ea[1] ={{0}};

    // Initialize an EXPLICIT_ACCESS structure for an access control entry.
	ea[0].grfAccessPermissions = FILE_READ_DATA;
	ea[0].grfAccessMode = SET_ACCESS;
	ea[0].grfInheritance= NO_INHERITANCE;
	ea[0].Trustee.TrusteeForm = TRUSTEE_IS_SID;
	ea[0].Trustee.TrusteeType = TRUSTEE_IS_USER;
	ea[0].Trustee.ptstrName  = (LPTSTR) pSID;

	// Create a new DACL that contains the new access control entries and the old ones.
	
	ACL *pNewACL = NULL;
    DWORD dwRes = SetEntriesInAcl(1, ea, (ACL*)pOldACL, &pNewACL);
	if (ERROR_SUCCESS == dwRes) 
		return pNewACL;

	return NULL;
}


BOOL SetSecurityDescDacl(HCRYPTPROV hProv, const ACL *pACL) 
{
	PSECURITY_DESCRIPTOR pSD = NULL;
	BOOL bRetVal = FALSE;

    // Initialize a security descriptor.  
    pSD = (PSECURITY_DESCRIPTOR) LocalAlloc(LPTR, SECURITY_DESCRIPTOR_MIN_LENGTH); 
    if (NULL == pSD) 
    { 
        printf("LocalAlloc Error %u\n", GetLastError());
        goto CommonReturn; 
    } 
 
    if (!InitializeSecurityDescriptor(pSD, SECURITY_DESCRIPTOR_REVISION)) 
    {  
        printf("InitializeSecurityDescriptor Error %u\n",GetLastError());
        goto CommonReturn; 
    } 
 
    // Add the DACL to the security descriptor. 
    if (!SetSecurityDescriptorDacl(pSD, 
            TRUE,   
            (ACL*)pACL, 
            FALSE)) 
    {  
        printf("SetSecurityDescriptorDacl Error %u\n", GetLastError());
        goto CommonReturn; 
    } 

	if(!CryptSetProvParam(
		hProv,
		PP_KEYSET_SEC_DESCR,
		(BYTE*)pSD,
		DACL_SECURITY_INFORMATION))
	{
		printf("CryptSetProvParam failed, lasterror = 0x%08x\n", GetLastError());
		goto CommonReturn;
	}

	bRetVal = TRUE;

CommonReturn:
	if (pSD)
		LocalFree(pSD);

	return bRetVal;
}




PSID GetSIDForAccount(LPCSTR pszAcct)
{
	PSID	pSID = NULL;
	DWORD	cbSID = 0;
	DWORD   cbDomain = 0;
	LPTSTR  pszDomain = NULL;
	SID_NAME_USE snu;
	

	LookupAccountName(NULL, pszAcct, NULL, &cbSID, NULL, &cbDomain, &snu);

	if ( cbSID )
	{
		pSID = (PSID)malloc(cbSID);
		pszDomain = (LPTSTR)malloc(cbDomain);

		if ( pSID && pszDomain)
		{
			if ( LookupAccountName(NULL, pszAcct, pSID, &cbSID, pszDomain, &cbDomain, &snu) )
			{
				// Success. Therefore, kill the domain buffer that we do not need.
				free(pszDomain);
				pszDomain = NULL;
			}
			else
			{
				// Failed. Therefore, kill both buffers.
				free(pszDomain);
				pszDomain = NULL;
				free(pSID);
				pSID = NULL;
			}
		}
	}

	return pSID;
}

BOOL ChangeContainerACL(LPCSTR pszContainer)
{
	HCRYPTPROV hProv = 0;
	BOOL bRetVal = FALSE;

	if(CryptAcquireContext(&hProv,
							pszContainer,
							MS_DEF_PROV,
							PROV_RSA_FULL,
							CRYPT_MACHINE_KEYSET))
	{

		SECURITY_DESCRIPTOR *pSD = GetSecurityDescDacl(hProv);
		if (pSD)
		{
			ACL *pCurACL = GetDacl(pSD);
			if (pCurACL)
			{
				//TODO: This should be replaced with a call to RtlCreateServiceSid for LH.
				PSID pSID = GetSIDForAccount("NetworkService");

				if ( pSID)
				{
					ACL *pNewACL = AddSidToAcl(pCurACL, pSID);
					if ( pNewACL )
					{
						bRetVal = SetSecurityDescDacl(hProv, pNewACL);
						LocalFree(pNewACL);
					}

					free(pSID);
				}

				LocalFree(pCurACL);
			}
			free(pSD);
		}

		CryptReleaseContext(hProv, 0);
	}
	else
		printf("Error opening key container %s\n", pszContainer);
		 
	return bRetVal;
}

int __cdecl main()
{ 
	HCRYPTPROV hProv= 0;	

	printf("Looking for CAPI machine key containers...\n");

	if(CryptAcquireContext(&hProv,
							NULL,
							MS_DEF_PROV,
							PROV_RSA_FULL,
							CRYPT_VERIFYCONTEXT | CRYPT_MACHINE_KEYSET))
	{

		for (int i=0 ; ; i++) 
		{
			DWORD dwFlags = i ? 0 : CRYPT_FIRST;
			const DWORD BUFLEN = 4096;
			DWORD dwBufLen = BUFLEN;
			BYTE szBuf[BUFLEN];

			if(!CryptGetProvParam(hProv, PP_ENUMCONTAINERS, szBuf, &dwBufLen, dwFlags)) 
			{
				DWORD dwError = GetLastError();
				if( dwError != ERROR_NO_MORE_ITEMS && dwError != ERROR_FILE_NOT_FOUND)
		            printf("Error reading container name - %08x\n", GetLastError());
				break;
			}
			else
			{
				if ( !ChangeContainerACL((LPCSTR)szBuf) )
					printf("Error changing ACL on container %s\n", szBuf);
				else
					printf("Changed ACL on container %s\n", szBuf);
			}
			
		}

		CryptReleaseContext(hProv,0);
	}
	else
		printf("Error opening CSP - %08x\n", GetLastError());

	printf("Done\n");

	return 0;
}
This code grants the Network Service account permissions to read the private key containers. After you run this code, you may notice the following message in the SQL Server error log when you start SQL Server:

Date Time Server The certificate was successfully loaded for encryption.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.

Properties

Article ID: 900495 - Last Review: March 11, 2006 - Revision: 2.2
APPLIES TO
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard Edition
Keywords: 
kbcode kbtshoot kbprb KB900495

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com