A common question I'm asked by clients is how to encrypt data and store it in SQL Server. One of the major problems I see in the field is when people store sensitive data unencrypted into SQL Server. For example, if a password is stored unencrypted into SQL Server, a malitious user could easily read all of the passwords with a simple select statement. You can develop your own COM mechanisms to encrypt the password but in this article, we'll discuss a method that is build into SQL Server.
Since SQL Server 6.x, you can use the ENCRYPT function to encrypt data with the same method used by the WITH ENCRYPTION keyword. There's a rather large problem that I will discuss after the example. To use the ENCRYPT function, use it before the string value as shown below:
SELECT ENCRYPT('TestPW1')
Will output the following result:
------------------------------ 0x5400650073007400500057003100 (1 row(s) affected)
Let's go ahead and create a sample table and try out this function. Create the following user table and load the sample data below:
CREATE TABLE Users (
UserID Varchar(10),
UserPW Varchar (20))
INSERT INTO USERS values('TestUser1',ENCRYPT('TestPW1'))
INSERT INTO USERS values('TestUser2',ENCRYPT('TestPW2'))
INSERT INTO USERS values('TestUser3',ENCRYPT('TestPW3'))
INSERT INTO USERS values('TestUser4',ENCRYPT('TestPW4'))
If you now select the data it will appear encrypted. Notice if you run a SELECT ENCRYPT('TestPW1') that the data that you see different that what appears when you select out of the Users table after you insert the value. The algorithm that SQL Server uses to encrypt the data is relativily easy and is case sensitive until it's stored into a table. At that point it becomes very difficult to read.
Data stored in an encrypted column can be used to store passwords. Once encrypted, you can't directly unencrypt the data. You could only perform checks against it as shown below:
SELECT * from Users where UserID = 'TestUser2'
and UserPW = ENCRYPT('TestPW2')
Keep in mind that the above command is case sensitive. If you want this to be case-insensitive, it is best to store all the data in uppercase by using the UPPER function. The UPPER function will have to be used in the insert statement as well as the select statement that we've mentioned. For example, the insert statement would look like this:
INSERT INTO USERS values('TestUser1',ENCRYPT(UPPER('TestPW1')))
SELECT * from Users where UserID = 'TestUser2'
and UserPW = ENCRYPT(UPPER('TestPW2'))
Another note to mention is that like any nicely encrypted data, the data may appear one length when viewing it, but is actually stored at a different length.
UserID UserPW ---------- -------------------- TestUser1 T TestUser2 T TestUser3 T TestUser4 T (4 row(s) affected)
But in actuality if we select the length of the field by using the LEN function, we can see the true length.
userid Length ---------- ----------- TestUser1 14 TestUser2 14 TestUser3 14 TestUser4 14 (4 row(s) affected)
With that point made, make sure that the length of your column represents the encrypted length,
not the unencrypted lenth. Now, for the large caveat that I mentioned earlier.
This stored procedure is unsupported by Micrososft and they could easily change it or rip it out. A good example of this function changing was between SQL Server 6.5 and 7.0. Another common question is decryption. Obiously, Microsoft does not make this easy and strings can only be easily decrypted using the comparison technique I showed earlier. If you don't use this method to encrypt your passwords, there
are other methods that are much better. These methods are much more robust and secure than what we discussed.
For example, Les Smith has 2 articles that show
you how to use Java or COM to encrypt passwords at :
http://www.sqlservercentral.com/columnists/lsmith/usingjavatoencryptpasswords.asp.
If you're serious about encryption of your data, the encrypt function is not the way to go. Instead, use one of the other custom methods that Les Smith mentions in his articles. These types of methods of encryption will not change from release to release but require
slightly more work.
