Check SQL Server encryption certificates present without a backup issue using PowerShell

Introduction

This script can be used to check your SQL Server encryption certificates without backup issue.

Scenarios

We can use Get-OSCEncryptionCertificates to identify the certificates used in TDE (Transparent Data Encryption) which have not been backed up and then list the affected databases. By default, this script use Windows Authentication to connect to your SQL Server. If you want to use SQL Server Authentication, just uncomment some code in this script.

Script

This script contains one advanced function Get-OSCEncryptionCertificates. You can use it in following ways:

Method 1:
  1. Download the script and copy it to your computer.
  2. Open the script file by using Notepad or any other script editors.
  3. Scroll down to the end of the script file, and then add the example command which you want to run.
  4. Save the file, and then run the script in PowerShell console.
Method 2:
  1. Rename scriptname.ps1 to scriptname.psm1 (PowerShell Module file)
  2. Run Import-Module cmdlet to import this module file.
    Import-Module filepath\scriptname.psm1
Here are some code snippets for your references. To get the complete script sample, please click the download button at the beginning of this page.
PowerShell
Edit|Remove
try 
{ 
    $Error.Clear() 
    #Open database$sqlConnection.Open() 
} 
catch [System.Data.SqlClient.SqlException] 
{ 
    #Catch and throw the terminating exceptionthrow$Error[0].Exception.Message 
} 
  
#Sql query statement to get all fragmented databases$sqlQuery = "WITH EncryptedCertificates AS (SELECT a.name AS Database_Name,COALESCE(c.name, 'NA')AS Certificate_Name, CASE a.is_encrypted WHEN 1 THEN 'Encrypted' WHEN 0 THEN 'Not Encrypted' END AS Is_Encrypted,COALESCE(Cast(c.pvt_key_last_backup_date AS VARCHAR(50)), 'NA')AS Last_BackupDate FROM sys.databases a LEFT OUTER JOIN sys.dm_database_encryption_keys b ON a.database_id = b.database_id LEFT OUTER JOIN sys.certificates c ON b.encryptor_thumbprint = c.thumbprint JOIN sys.database_mirroring d ON d.database_id = a.database_id WHERE d.mirroring_guid IS NULL AND source_database_id IS NULL AND a.name NOT IN ( 'master', 'tempdb', 'model', 'msdb' )) SELECT * FROM EncryptedCertificates WHERE  last_backupdate = 'NA' AND certificate_name <> 'NA'"#Create SqlDataAdapter object with command text and connection$sqlDataTable = New-Object System.Data.DataTable 
$oSQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($sqlQuery,$sqlConnection$oSQLAdapter.Fill($sqlDataTable|Out-Null#Close SqlConnection$sqlConnection.Close()

Examples

Example 1: Display help about Get-OSCEncryptionCertificates.
Command: Get-Help Get-OSCEncryptionCertificates
Screenshot:
Example 2: List the objects whose encryption certificates without a backup.
Command: Get-OSCEncryptionCertificates –ServerName “TestServerName\TestInstanceName”
Screenshot:

Prerequisite

Windows PowerShell 2.0
Microsoft SQL Server 2008 Enterprise
Microsoft SQL Server 2008 R2 Datacenter
Microsoft SQL Server 2008 R2 Enterprise
Microsoft SQL Server 2012 Enterprise
Additional Resources
http://support.microsoft.com/kb/2201900