MSSQLWIKI

Karthick P.K on SQL Server

Posts Tagged ‘Error: 15466’

(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 »

Linked server connection fails with “An error occurred during decryption”

Posted by Karthick P.K on January 9, 2012

We might get Error: 15466, Severity: 16, State: 2  An error occurred during decryption while installing Projects servers (or) Sending mails using database mail (or) Linked server connections might fail with Msg 15593, Level 16, State 1, Line 1

Linked server connection fails with below error

{

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
An error occurred during decryption. (Microsoft SQL Server, Error: 15466)

Msg 15593, Level 16, State 1, Line 1

An error occurred while decrypting the password for linked login ‘distributor_admin’ that was encrypted by the old master key. The error was ignored because the FORCE option was specified.

}

Database mail might fail with below error

{

Set mail server login password failed for MailServer ‘Domain’.  (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

An error occurred during decryption. (Microsoft SQL Server, Error: 15466)

}

Or

You notice below errors in SQL Server errorlogs

spid10s Error: 15581, Severity: 16, State: 3.
Please create a master key in the database or open the master key in the session before performing this operation.

Cause

SQL Server service account was changed from services control manager (or) service master key was not backed up and restored when migrating SQL Server to another computer domain.

{

http://msdn.microsoft.com/en-us/library/ms187788.aspx

To change the SQL Server service account, use SQL Server Configuration Manager. To manage a change of the service account, SQL Server stores a redundant copy of the service master key protected by the machine account that has the necessary permissions granted to the SQL Server service group. If the computer is rebuilt, the same domain user that was previously used by the service account can recover the service master key. This does not work with local accounts or the Local System, Local Service, or Network Service accounts. When you are moving SQL Server to another computer, migrate the service master key by using backup and restore.

The REGENERATE phrase regenerates the service master key. When the service master key is regenerated, SQL Server decrypts all the keys that have been encrypted with it, and then encrypts them with the new service master key. This is a resource-intensive operation. You should schedule this operation during a period of low demand, unless the key has been compromised. If any one of the decryptions fail, the whole statement fails.

The FORCE option causes the key regeneration process to continue even if the process cannot retrieve the current master key, or cannot decrypt all the private keys that are encrypted with it. Use FORCE only if regeneration fails and you cannot restore the service master key by using the RESTORE SERVICE MASTER KEY statement.

}

 

Resolution

Regenerate the service master key using ALTER SERVICE MASTER KEY REGENERATE

If you receive the following error message when running ALTER SERVICE MASTER KEY REGENERATE.

{

The current master key cannot be decrypted. If this is a database master key, you should attempt to open it in the session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.

}

We are left with only option to force regenerating service master key using ALTER SERVICE MASTER KEY FORCE REGENERATE “.

Note:The service master key is the root of the SQL Server encryption hierarchy. The service master key directly or indirectly protects all other keys and secrets in the tree. If a dependent key cannot be decrypted during a forced regeneration, the data the key secures will be lost.

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Posted in Configuration, Connectivity, Security, SQL Server Tools | Tagged: , , , , , , , , , , | 7 Comments »

 
%d bloggers like this: