KB #240106: SQL AutoExec script to confirm whether Encryption Key passed to a Secured SQL Instance

Search Knowledge Base

KB #240106: SQL AutoExec script to confirm whether Encryption Key passed to a Secured SQL Instance

Type:

Tip

Summary:

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.

Additional Information:

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
   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.
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.

Knowledge Base

Search Knowledge Base