Microsoft KB Archive/900495

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

Article ID: 900495

Article Last Modified on 3/11/2006

-

APPLIES TO


 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Standard Edition

-



BUG #: 374329 (SQLBUDT)



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:

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.

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

Server Unable to load user-specified certificate. The server will not accept a connection. You should verify that the certificate is correctly installed. See &quot;Configuring Certificate for Use by SSL&quot; in Books Online.

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

Server Server cannot startup because server is configured to require encryption but network libraries cannot support encryption.

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.


 * 1) include 
 * 2) include 
 * 3) include 
 * 4) include 
 * 5) include 

//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, &quot;Error getting file security DACL: %d\n&quot;, ret); return 0; }

sd = (SECURITY_DESCRIPTOR *) malloc(size); if (! sd) { fprintf(stderr, &quot;Out of memory for security descriptor!\n&quot;); 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, &quot;Error getting DACL from security descriptor: %d\n&quot;, GetLastError); return 0; }

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

return acl; }

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

// 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(&quot;LocalAlloc Error %u\n&quot;, GetLastError); goto CommonReturn; }    if (!InitializeSecurityDescriptor(pSD, SECURITY_DESCRIPTOR_REVISION)) {         printf(&quot;InitializeSecurityDescriptor Error %u\n&quot;,GetLastError); goto CommonReturn; }    // Add the DACL to the security descriptor. if (!SetSecurityDescriptorDacl(pSD, TRUE, (ACL*)pACL, FALSE)) {         printf(&quot;SetSecurityDescriptorDacl Error %u\n&quot;, GetLastError); goto CommonReturn; }

if(!CryptSetProvParam( hProv, PP_KEYSET_SEC_DESCR, (BYTE*)pSD, DACL_SECURITY_INFORMATION)) {       printf(&quot;CryptSetProvParam failed, lasterror = 0x%08x\n&quot;, 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(&quot;NetworkService&quot;);

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(&quot;Error opening key container %s\n&quot;, pszContainer); return bRetVal; }

int __cdecl main {    HCRYPTPROV hProv= 0;

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

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(&quot;Error reading container name - %08x\n&quot;, GetLastError); break; }           else {               if ( !ChangeContainerACL((LPCSTR)szBuf) ) printf(&quot;Error changing ACL on container %s\n&quot;, szBuf); else printf(&quot;Changed ACL on container %s\n&quot;, szBuf); }       }

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

printf(&quot;Done\n&quot;);

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:

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 &quot;Applies to&quot; section.

Keywords: kbtshoot kbprb kbcode KB900495

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.