SQL Server 2005 security presentations at PASS – Pre Conference

If you missed the PASS Pre Conference security presentations, you can now catch up by viewing them online: http://cmcgc.com/Media/WMP/261115/. [UPDATE 8/24/2010]: The main content of my execution context presentation is available here. The associated demo was already available here. 


Who needs encryption?

For those that read my previous posts, the question in the title may be startling. I want to reassure you from the start: this post is not about encryption being a useless technique; it is just about it not being a solution for certain problems and definitely not being a general solution for any problem. Also,…


Anonymous comments are now disabled

Due to the large amount of spam comments that I received over the last weeks, I decided to disable anonymous comments.


SQL Server 2005: Demo for enabling database impersonation for cross database access

[UPDATE 8/24/2010]: I added a companion post covering the theory behind this demo. There is an excellent article on this topic in Books Online: Extending Database Impersonation by Using Execute As. I just wrote a small demo to illustrate the techniques described in that article. It can be used as a companion to that article, if…


SQL Server 2005: An example for how to use counter signatures

A while ago, I wrote a post showing how signatures can be used to allow users to perform operations without explicitly granting them the permissions required for that operation. In this post I’ll present more details about the use of signatures. One important thing to keep in mind when working with signatures is that, normally, they will only have…


SQL Server 2005: How to determine what key was used to encrypt a piece of data

Let’s say we have some data that is encrypted and we would like to find out what key was used to perform the encryption. SQL Server 2005 knows what key was used to encrypt the data because the key identifier (the key_guid value) is prefixed to the encrypted data. We can find out the key same as SQL Server does with…


SQL Server 2005: How to regenerate the same symmetric key in two different databases

In a previous post on using symmetric keys, I mentioned that keys can be recreated using the KEY_SOURCE and IDENTITY_VALUE clauses of CREATE SYMMETRIC KEY. In this post, I’d like to expand a little on this topic and present a small demo as well. Because keys cannot be individually backed up and restored, there is…


Why encryption should be salted and a small C# demo

In my previous post on searching encrypted data, I mentioned that the SQL Server 2005 encryption procedures are salted and that this prevents an index on encrypted data from being useful for any type of cleartext searches. Today, I will illustrate why encryption should be salted by presenting a small C# encryption demo. Interest in searching encrypted data comes primarily…


SQL Server 2005: a proposed update of sp_help_revlogin

The sp_helprevlogin procedure is described in KB article 246133. This procedure generates a script that can be used to recreate the logins that exist on a server at a specific point in time. It can be useful for transferring logins from one server to another. My colleague, Craig Gick, has updated this stored procedure to work with SQL…