How to encrypt and decrypt highly sensitive information in SQL Server database with ASP Classic?

Question

I have been checking various questions on stackoverflow and of course google, but I can't really find any specific solution to this question:

How do I create a function in ASP Classic to encrypt and decrypt highly sensitive information in a SQL Server database? Like fx. a social security number or anything similar?

(Or is it possible to do in my SQL string?)

And yeah, I do know how to create a function with ASP ;)

And no, I just cant hash the information with SHA or MD5, because they only work one way. I need it to work both ways!

The more security, the merrier! :)

EDIT:
Afterwards I found this:

https://web.archive.org/web/20210728063606/https://www.4guysfromrolla.com/webtech/010100-1.shtml

But I don't really know if this are secure enough and will do? Of which I can see, it's going both ways?

Answer 1

It may well be beneficial to allow SQL Server to handle the encryption/decryption using Keys/Certificates. This way, you don't have to roll your own with ASP and the management of this system is kept where the data itself resides. There is also the benefit of not having to update this process should you decide to move to another platform.

It is a simple process to create the Keys on the server and use of them after this point is also simple, for example;

Encrypt;

OPEN SYMMETRIC KEY mykey DECRYPTION BY CERTIFICATE [mycert]
UPDATE table SET number = EncryptByKey(Key_GUID('mykey'), @number)

Decrypt;

 OPEN SYMMETRIC KEY mykey DECRYPTION BY CERTIFICATE [mycert]
 SELECT CONVERT(varchar, DecryptByKey(number)) AS number FROM TABLE

A good overview of this can be found here Introduction to SQL Server Encryption

Answer 2

You can use the Rinjdael cipher successfully in VBScript with this library. The key functions are EncryptData() and DecryptData().

It seems secure enough for me. Obviously you will want to keep your key pretty secret. An application variable in the global.asa might be a good place to store this (as that's usually where connection strings and such are found).