Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Encrypting Data With the Encrypt Function

By Brian Knight, (first published: 2001/07/17)

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.
Total article views: 32966 | Views in the last 30 days: 21
 
Related Articles
FORUM

Inserting Encrypted Files

Inserting Encrypted Files

FORUM

Encrypted Stored Procedures

Encrypted Stored Procedures

FORUM

Insert - Exec Select statements

Insert - Exec Select statements

FORUM

Problem with encryption

encryption

SCRIPT

Encrypt All Stored procedures at once

this code encrypts all stored procedures that meet a certain format. this code is easy to change to ...

Tags
advanced querying    
security    
sql server 6.5    
sql server 7    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones