Search Knowledge Base
KB #240106: SQL AutoExec script to confirm whether Encryption Key passed to a Secured SQL Instance
The following script can be used as an “Auto Exec” script inside SQL to confirm that the instance has been properly secured, or that the encryption keys have been passed to SQL when SQL started.
The following script will generate an alert upon SQL Startup if the instance was not properly secured or if the key was not passed to the secured SQL instance when the instance was started.
We recommend always using it in connection with Column Encryption. Note: you must have installed the Encryptionizer APIs in order to run it.
use master GO create procedure my_autoexec as set nocount on; declare @keycount int; set @keycount = dbo.fn_n_keycount(); if @keycount is null or @keycount <= 0 begin RAISERROR(...) -- create alert of your choice end GO -- enable startup procedures EXEC sp_configure 'show advanced options', 1; EXEC sp_configure 'scan for startup procs', 1; reconfigure; GO -- set my_autoexec as a startup procedure exec sp_procoption 'my_autoexec', 'startup', 'on'; GO
Below are some of the reasons the encryption keys have not been passed to the SQL instance, here are a few:
- The NetLib Key Management Service (nlcbtask) is not running.
- Used the “Store Key to Alternate Location”, but the alternate location is not accessible or the NetLib Key Management Service does not have permission to access the alternate location. See KB 240040.
- The instance was secured while a User Key Master Key (UKMK) was in place but the UKMK was cleared or changed.
- The SQL instance was secured with the Lock Key To Machine feature, but the hardware characteristics of the machine have changed. This could be caused by restoring an image of machine on other hardware, or changing the name of the machine, etc.
- The SQL instance was secured with the Master Must Be Encrypted option, but the Master is not encrypted.
- A registration key is invalid or expired.
240102: Encrypted Databases not accessible
240040: Cannot start SQL Server or encrypted database is inaccessible when profile is on a remote machine
240091: NLCBTASK Service fails to start with error “The system cannot find the file specified”