November 27, 2002 at 1:07 pm
Does SQL Server have something like the Input Mask in MS Access which allows you to change how data is viewed in a table, such as astericks for a password? If not, I'd like to encrypt username/password data in a database that I have. Can anyone point me in the right direction for this kind of thing?
Many thanks!!!
November 27, 2002 at 3:43 pm
No support for a mask. If you want to make it read only you could replace the text with asterisks or whatever in a view, clunky. Encryption is hard to do. Search the forums and I think maybe Steve has an article about it.
Andy
November 27, 2002 at 5:57 pm
Your best bet is to encrypt the username and/or password on the application side. Otherwise, a sysadmin could see it coming across in plaintext using either Profiler or DBCC INPUTBUFFER().
What you could do is create a hash using one of the known methods, and store the hash in SQL Server. When a user attempts to login, rehash the password entered and pass it and the username to SQL Server via a stored procedure. The stored procedure can compare the hash and if there's a match, it passes back a positive result back to the application. If it doesn't match, pass a negative result back. In this manner SQL Server doesn't have to know anything about the method on encryption and no one looking at things from the SQL Server side has any idea what the password is.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
December 1, 2002 at 8:06 pm
Better than building from a known is to download a free set of encryption routines from a trusted source. (Laughing hysterically cause I said trusted) CAPICOM is part of the platform SDK for Windows (see more details at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/security/security/cryptography_portal.asp) which is free from MS and provide DES, 3DES and various others.
If you use Encryption client side I suggest you be carefull with the key-word. Many apps encrypt code except for constants. What I usually do is compile the app, open it in Notepad, find my keyword then grab a bunch of garbage data from nearby and use that. Then it will take awhile to find that way. Also, do not use varibale names like m_csPassword or m_csKeyword as they will be easily visible with a memory spy app and immediately free then or set to nothing when done with.
As for masks you have to build that intop your app your self. Or another trick you can use, is add computed columns to your table, one wofr the actualy input and one for the formatted data based on the input column.
December 1, 2002 at 10:16 pm
Known = Crypto algorithms that have been tested and so far have been shown to be relatively secure. DES, 3DES, DESX, RSA (probably won't use this one because it's public key and you're probably looking for a symmetric encryption algorithm since you want to minimize key management), etc., are all in this list. Whether you use CryptoAPI or build yourself or grab from another source is up to you. However, there are so many proven routines out there, best to use one that has been shown to be mathematically strong.
Don't use a "keyword." Use a key, certainly, because the algorithms require it. But make it a random stream of bits, not characters. Most algorithms are bit-level. The advantage there is someone scanning the contents of your files won't be able to separate the key from the rest of the compiled code. If you store the key separately in a file or in the registry, encrypt it as well. In that case you'll probably have code the key to decrypt the key in the application (don't laugh, EFS has two sets of keys, the File Encryption Key, and the public-private pair used to encrypt the FEK).
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
December 2, 2002 at 10:17 am
And keep in mind that the key management is one of the more difficult pieces of this. Most PKI solutions have problems with this part (As well as key distro).
I'd encrypt on the client, using advice from above. If you don't need to recover the password, use one way encryption. That way you can just compare this with what's in the db.
Steve Jones
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply