Encrypting password column in T-SQL?

ppalubinski · #1 (**permalink**) 01-28-09, 16:56

Hi Folks,

I'm trying to create a table in my database to contain user login info. In MySQL, there were some functions you could use to encrypt the data that went into the password column. Is there similar functions in T-SQL? If so, what is the most convenient way to encrypt the passwords stored in the DB and decrypt them when requested from an outside programming procedure (such as in ASP.NET)? Any suggestions would be greatly appreciated.

Regards,

Paul

MCrowley · #2 (**permalink**) 01-29-09, 10:18

SQL Server 2005 and SQL Server 2008 have a lot of encryption options. You can start here for SQL Server 2008. Most of the concepts are the same between the two, but SQL Server 2008 expanded on the actual functions a bit.

ppalubinski · #3 (**permalink**) 01-30-09, 00:38

Thanks for the response. I just took a quick look at that article, but it seems to be what I need.

gvee · #4 (**permalink**) 01-30-09, 04:13

By the by, you shouldn't need to decrypt your passwords... You take the entered value, encrypt it, and then compare this derivation to the stored encrypted password.

MCrowley · #5 (**permalink**) 01-30-09, 09:36

Ahh, georgev, georgev, georgev. Some day, you will learn that the word "should" bites you more often than anyone else.

Code:

if not exists (select * from sys.symmetric_keys where name = '##MS_DatabaseMasterKey##')
  begin 
	create master key encryption by password = 'Here is a database master key!'
  end

create asymmetric key UserKey with algorithm = RSA_512

select EncryptByAsymKey (AsymKey_ID('UserKey'), 'Georgev should not assume')
select EncryptByAsymKey (AsymKey_ID('UserKey'), 'Georgev should not assume')
select EncryptByAsymKey (AsymKey_ID('UserKey'), 'Georgev should not assume')
select EncryptByAsymKey (AsymKey_ID('UserKey'), 'Georgev should not assume')

Now, encode it 50 more times on the blackboard, young man.

gvee · #6 (**permalink**) 01-30-09, 09:58

Code:

DECLARE @i int
    SET @i = 1

WHILE @i <= 50
  BEGIN
    PRINT EncryptByAsymKey (AsymKey_ID('UserKey'), 'Georgev should not assume')

    SET @i = @i + 1
  END

Doron_ · #7 (**permalink**) 02-18-09, 07:41

the code:

Quote:

SELECT CHECKSUM('my string')

returns a number which is a one-directional encrytion of the given string.

Nob Hill Software - tools for database people (+ free stuff!) Nob Hill Software - tools for database people (+ free stuff!)
http://www.nobhillsoft.com

blindman · #8 (**permalink**) 02-18-09, 09:42

No, not really.
And Good Lord, if you are going to do that then at least use BinaryChecksum.

MCrowley · #9 (**permalink**) 02-18-09, 10:22

I never thought of using checksum. Probably because the results of the function are not unique. Although, this would give you the ability to better guess the password, since there are multiple variations that will generate the same checksum, and therefore grant you access. Better yet, if you have access to the checksum column, you can make an educated guess as to the password.

pootle flump · #10 (**permalink**) 02-18-09, 10:24

Yeah - I believe Peso reverse engineered sql server checksum.

ppalubinski · #11 (**permalink**) 02-18-09, 13:44

I ended up using ENCRYPTBYASYMKEY() and in terms of awesomeness, it was the opposite of Colin Farrell's performance in the movie, Dare Devil. Thanks for all the assistance folks.

dportas · #12 (**permalink**) 02-18-09, 21:21

Quote:

Originally Posted by ppalubinski

I ended up using ENCRYPTBYASYMKEY() and in terms of awesomeness, it was the opposite of Colin Farrell's performance in the movie, Dare Devil. Thanks for all the assistance folks.

Best practice is not to encrypt passwords at all. Passwords should be hashed (use the HashBytes function). In that respect George was correct: hash the password and compare hashes, don't compare passwords.

The only reason to encrypt a password is if you require recovery of the original password. Password recovery is itself a weakness and ideally passwords should be unrecoverable. Note that password complexity rules are also important to make sure the password can't be recovered using a password dictionary attack.

ppalubinski · #13 (**permalink**) 02-18-09, 21:34

To be honest, I probably don't need to apply a hash or password checker for this project. I would be deeply honored if someone decided it was worthwhile to try crack the system we're working on. And I'm sure the achievement would be more disappointing than a Colin Farrell movie.

dportas · #14 (**permalink**) 02-18-09, 21:50

Here's the problem however. If you implement password authentication with only trivial security and from which passwords are recoverable then two things will happen. Firstly, you will deceive legitimate users into thinking their data is secure. Secondly, a determined attacker may be able to identify a user and recover the user's password. Chances are that user has used the same password or derivations of it elsewhere - possibly for more sensitive or important data than your system holds. Determined attackers know this and may target the weakest-link systems to get at those passwords. So as a consequence of your system's weaknesses you could make other data vulnerable too.

In my opinion that is why every system should implement best practice password management and the user should never be swindled by a system that looks secure when it actually isn't.

ppalubinski · #15 (**permalink**) 02-18-09, 21:54

Fair enough, but we determine the passwords for our clients and the data is our own data that they are just allowed to view, but I'll definitely look into using a hash algorithm to get into a good habit.

Internet Services

Web Development

Digital Marketing

Consumer Tech