THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

What Encryption Algorithms Are You Using?

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

Published Tuesday, May 31, 2011 1:04 PM by AllenMWhite

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement