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