Hi there,
I am planning to save customer's sensitive information through SQL Server certificate/ (a)symmetric keys. Though data is secure now, certificate and keys are not readable, i wonder where should i put the keys? Is there any best practices? only thing i could think of is to create table only accessible to dbo or dedicated login and save there. Please give me your valuable advices.
Thanks, Ebe.
hash the password and store the hash. if the user forgets the password send him a new one. also give him an option to change it.
also think about implementing openId registration so you won't have to worry about logins.
Filesystem. A configuration file for example, under a directory that only the account under which your application runs can read and/or write. That assumes that you trust your data center folks of course.
On the other hand, maybe I'm not getting your question quite right. If you just want to store user passwords on a table, you should hash them (use a salt) using SHA-1 ideally. Never store passwords in clear text, regardless of the DB permissions you're using.
Certificates can be encrypted with the database master key and the database master key can be encrypted with the server master key. The server master key is encrypted using DPAPI with the machine key and/or the account key. This is all explained in the Encryption Hierarchy.
An automated service that needs access to the encrypted data secrets can rely on this encryption hierachy. Data is protected at runtime using normal access protection (access conrrol lists and grant/deny/revoke permissions for SELECT), the encryption is added just to provide accidentall media compromise protection. Accidental media compromise protection ultimately relies on the service account password and/or on the host machine system account security.
If you need stronger protection in case of physicall loss you must use SQL 2008 and rely on the EKM infrastructure (Extensible Key Management), create a master key stored on a physical device. This will add security in case of media compromise because an attack needs physical access to the hardware crypto module that stores the master key.
A process that does not need access to the secrets does not need any of these. The access is obtaines by passwords provided by the client applications in this case, and those passwords are usually obtained from the user itself.
If you're system is of the former type (needs automated access to the secrets and does not ask the current user for an access password to the secrets) then the way to go is use the ecryption hierarchy (database master key, service master key). If the app is of the later type (it asks the user for a password to access the user's own specific secrets) then the proper solution is to have a master assymetric key encrypted with a password provided by the user.
Nowhere, and I must emphasize and repeat, NOWHERE in these schemes is there any room for a secret saved into a file, or a master password saved into a table. That is just a major design #FAIL.