fbpx

Knowledge Base

Search Knowledge Base

KB #240101: Test the encryption state of a SQL database or backup

Type:

Information

Summary:

There are several methods of determining whether SQL Database files are encrypted. You can also determine whether backups and other files are encrypted.

Additional Information:

Testing Databases and files that are offline using a User Interface: The Encrypt/Decrypt Wizard from the Main Menu of your  Encryptionizer product can be used to test any file. If testing database files, the database must be offline, or SQL must be stopped in order to test the file (online databases are locked by SQL Server and cannot be tested with this utility):

  • run the Encrypt/Decrypt Wizard (secncrpt.exe)
  • Choose the Validate option
  • select the database file(s) or other file(s) to be tested.
  • returning to the list, you will see a report of the encryption state of each file selected.
  • if you wish, can proceed to test the encryption key by clicking Next and proceed through the utility.

Testing Databases and files that are offline using a Command Line Interface (CLI): Encryptionizer provides a command line interface called SECTOOL.EXE that allows you to test that a database or backup file is encrypted, and if desired, test the key with which it is encrypted. If testing database files, the database must be offline, or SQL must be stopped in order to test the file (online databases are locked by SQL Server and cannot be tested with this utility):

  • Open a command window and navigate to the directory where sectool.exe resides
  • Use the following command

    sectool.exe /v “C:\path-to-file\filename.mdf”

  • Encrypted++ or Encrypted– indicates that the file is in fact encrypted. If the file is not encrypted, the result will indicate Plain.
  • Find more detailed documentation on syntax and more options for this CLI in the included documentation NEP-CLI-REF.PDF

Testing Databases that are online: You can use one of the provided Encryptionizer for SQL API’s to list all the database files that are encrypted that are associated with attached databases. You will have needed to install the API’s – either at original installation time, or later. API’s installation is simple and instructions can be found in the NetLib installation guide provided with the software.

In a SQL Query window, you can run the following query from the master database:

sp_n_enumfiles

This will return a list of encrypted database files associated with attached databases that are online. This will not return the state of any databases that are Offline or Recovery Pending. And it will not report on any attached databases that are not encrypted. Inclusion in the list confirms encryption. Exclusion from the list means either the database files are not encrypted, are offline, or the encryption key used to encrypt the database files does not match the key(s) used to secure the SQL instance.

 

Testing all Attached databases:

If you installed the CLR version of Encryptionizer API’s, then you can run the following query which provides information about each attached database regarding of encryption status or online status:

sp_n_codelvl

This will return a list of database files associated with an instance along with its online status and encryption state.  2 or 6 = encrypted and matches a key with which the instance is secured, 0 or 4 = encrypted but no key match, 1 = not encrypted.  This will not return the state of any backup files as those are not attached to the SQL instance.

 

Where:

xcodelvl Description
Plain the database file is NOT encrypted
Encrypted++ the database file IS encrypted AND it matches the key that is loaded with the SQL Server
Encrypted– the database file IS encrypted AND it does not match the key that is loaded with the SQL Server OR the key did not load. (See KB 24102 for more information and troubleshooting)

 

If you agreed to install XP (extended stored procedures) version of the API’s,  then the more verbose sp_n_codelvl stored procedure described above will not be available; however, you can use another stored procedure that will give you some information.

 

To further test that your database or backup is encrypted: An encrypted database or backup file cannot be restored to another SQL Server instance either on the same machine or another machine, unless it is configured with the same Encryption key profile information.

Related Topics:

240100: SQL Backup not encrypted

240038: SQL database backups to a Mapped Drive are not encrypted

240056: Encrypt/Decrypt Wizard: Error – could not access file

240046: Backup not encrypted even though “encrypt new” flag was set

240028: How to Install Encryptionizer SQL APIs Manually

Last modified: 10/11/2022

Top