Security is an important consideration when designing a database application. Who can get access to what data? How much damage can someone do if they get at your data?

In SQL Server 2005 Microsoft implemented column-based encryption, and in SQL Server 2008 they added Transparent Data Encryption. Both solutions work well for their intended use, and they're not mutually exclusive.

When I walk into a new client it's important for me to know what the security environment is, and how data is secured. Weak encryption is better than none at all, but strong encryption is better.

I've written a PowerShell script that reports by database what encryption keys have been implemented, and what algorithm is used for each key. First I load the SMO library and then connect to the SQL Server instance via SMO.

[System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO') | out-null
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer\MyInstance'

Then, I iterate through the databases, and within each database I grab the database name, then I pipe the AsymmetricKeys collection to a select where I return the database name, the key name and algorithm, then do the same thing for the symmetric keys.

# Iterate through the databases to check database names
foreach ($db in $s.Databases) {
[string]$nm = $db.Name
$keys = $db.AsymmetricKeys
$keys | select @{Name="Database"; Expression = {$nm}}, Name, KeyEncryptionAlgorithm
$keys = $db.SymmetricKeys
$keys | select @{Name="Database"; Expression = {$nm}}, Name, EncryptionAlgorithm
}

This gives me a quick view of what's been set up. I look to see that a strong algorithm is used, like AES256 (or higher), and any use of DES or TripleDes is a big red flag, due to its weakness against dictionary attacks.

A discussion on encryption strategies is beyond the scope of what I wanted to do here, I just wanted to give you a way to find the keys you're using and make sure they meet your needs.

Allen