SQL Server 2005: searching encrypted data

How to search encrypted data is a question that came up several times on forums and I should have blogged about this issue earlier, but better later than never. So the problem is that we have just encrypted that confidential column in our table, but we would also like to continue retrieving records based on its values, and…


About security and encryption with references to SQL Server 2005

There have been many books written on security and encryption, and there is much talk about security these days. I will not bring anything new with this post to the general topic of security, but I would like to present some ideas in condensed format. The main point I want to make is that we cannot discuss security without defining what…


SQL Server 2005: Yet another column encryption demo – "Clinic"

Here’s another demo I just used for a presentation of SQL Server 2005 encryption features. This shows how multiple keys can be used for encrypting data in a column and also how access to keys can be controlled. It’s a fairly long demo: —- Column Encryption Demo – “Clinic”–use master — Create two logins that will be…


A couple of links for submitting feedback on Microsoft products

I want to advertise a couple of sites that can be used for submitting feedback and suggestions, or for filing bug reports for Microsoft products. These are: The Microsoft Technical Forums at: http://forums.microsoft.com/MSDN/. The SQL Server Forums are at: http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=19&SiteID=1. The Product Feedback Center at: https://connect.microsoft.com/default.aspx. Update [2005/12/21]: Another useful link for reporting security vulnerabilities…


SQL Server 2005: The loginproperty builtin function

In this post, I’d like to describe a builtin function that is very useful for login management: loginproperty. Loginproperty accepts two arguments: the first is the name of the login for which information is requested and the second is a property name that specifies what information should be retrieved. The names of the properties are case insensitive,…


SQL Server 2005: using symmetric keys to encrypt data

In SQL Server 2005, the recommended method for encrypting data is to use symmetric keys. In this post, I’d like to comment on three topics related to using symmetric keys: 1) basics of using a symmetric key2) ways to restrict access to a symmetric key3) how to prevent a symmetric key loss So, let’s tackle each of these: 1) Basics of using a symmetric key A…


A new blog link

Here’s a link to a new blog that a colleague has started: http://blogs.msdn.com/yukondoit/. The first entry is about the various levels of data protection in SQL Server 2005 and you can access it at: http://blogs.msdn.com/yukondoit/articles/480854.aspx.


Converting SQL Server 2005 certificates to PFX format

I just found a very nice article on Kyle Alons’s blog, about how to convert a certificate from the cer/pvk format to the pfx format: http://www.kinook.com/blog/?p=10


SQL Server 2005: A look at the master keys – part 2

This is a continuation of a previous post, in which I discussed the service master key (SMK) and the database master keys. I mentioned in that post that a new encryption will be added to the SMK and I will describe it in this article. Also, a few things have changed since I wrote my previous…


SQL Server 2005: How to fix outdated names of Windows logins

The SQL Server login catalogs store the names of Windows principals as well as their SIDs. Because the names are stored, changes that affect a name can lead to a state where a catalog entry is out of sync with the current login name. For example, in the case of local Windows accounts, if we change the machine name, the catalogs…