Encryption using Stored Procedure.

[jimoo]

I have a legacy SQL app that has been migrated to SQL 2008 on the backend. The front end apps use a classic VB6 DLL that connects to the database and verifies a username and pw are correct, and if so it passes back credential info.

The problem is that the passwords have never been encrypted, and the DLL is legacy. I am looking at writing a webservice to replace the DLL, but there are several older apps that will still use the DLL for quite some time.

I mention this because I want to provide some level of security on the data, either through hashing, encryption, or an internal algorithm. If I provide this at the field level it will likely break the legacy code.

Since the legacy code calls a stored procedure I am thinking I could read the encrypted value, then unencrypt in the stored procedure (or encrypt the user typed password) and compare the two values. I will have to do the same with the new web-service.

Is there a way to encrypt an unencrypted value in a stored procedure and then save it encrypted. I would also need a way to read the value and then compare it to what was passed so I would have to encrypt the type value and compare the two or unencrypt the saved value and compare the two.

I do could do this through code with my own algorithm, but I don’t consider that encryption or hashing, and more of a way to hide the password as something else to keep the honest person honest.

Thoughts


Jim

 

[Qik3Coder]

It sounds like you've thought this through, as least a little.

I'm not sure what sort of security you have on your database, because this will shift how well this will work.
If everyone is a dba, you might as well just forget it. If you have some decent security, this may work.

I'm going to assume that the world (Public) has access to run the current getMyPassword sproc, which will make things challenging.


This is probably the easiest way to make it a little harder to figure out what's going on. If you have an audit group, or need to check code into a source control system, this may be a lost cause.

Create a UDF if code that encrypts/decrypts the password however you see fit (this is your encryption algorithm). This should take a salt value.

Set the permissions on the UDF so that no one has access to view the definition. Create a special account that's sole purpose is to run this UDF. Ensure that new account is the only one that can run this.

Create another udf that uses the new account instead of whoever is signed in. Once again, disable all access to this procedure. This UDF will accept a regular string or an encrypted password and return the value "flipped". This function calls the first with a hard coded value for the salt and the current password.

Add a bit flag to the table that stored the passwords. This will indicate whether or not the password is encrypted.

Modify your existing sproc so that if the bit flag is tripped it calls the new UDF, something like:

Select CASE WHEN myBit = 1 THEN dbo.udfFlippedEncryptedText(a.Col1) ELSE a.col1 END [Password]


Ideally the encrypting would happen in the code, and the database is merely informed of the encrypted value, but this is the real world we work in.

That might get you started,
Lodlaiden

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]