Divya
Well, you can have SQL Server manage the keys for you, then you never have to expose them to client applications.
But I assume from your question that you are using passwords (passphrases) to protect the asymmetric keys? If so, is there a reason you are doing that? You could instead protect the key using the database master key and then not have to worry about providing the key in your stored procedure.
Don
|||A very good tutorial for similar situation is
http://aspnet.4guysfromrolla.com/articles/022807-1.aspx
|||It all depends on who and what you are trying to protect from.
By the web server passing the key, the loss of just the physical database server itself would be meaningless. You couldn't decrypt the data on it since they does not exist on that machine. It also can simplify a number of other issues as well since the key is stored with the application that is accessing the data. And it does make for a stronger link between application and data. if you were store the key on the database (and not pass it), then you most likely wouldn't gain very much. The stored procedure could look it up, but then you are subject to the problem that if the physical machine is compomised (Stolen), the data can be decrypted. It also causes a problem because then you have to make a second security layer so that the stored procedure is only then able to be executed by connections it trusts. And if you write that layer, what is the point of a stored procedure that encrypts data since you are now dependant on the security of the connection? If you can trust the connection, the just put permissions on your tables and you've got the same level of security. If somehow the stored procedure is compromised, again, the encryption is useless. It's encrypted, but anyone can decrypt it just by asking, so what is the point in that?
Now, that's not to say that the key being stored/sent from the application doesn't have it's own set of problems, but there is a lot more to encryption and security that must be thought through. And your security scheme must reflect what types of attacks you want to protect yourself from. All-in-all, having the application send the key is more secure in most cases. Just make sure your connection is encrypted as well.
|||hello don,
i tried d solution suggested by u by creating the database master key, but i am not able to take backup of this key in ntfs directory as it involves editing registry information. Can u suggest me any feasible solution for it.
Thanks
Divya
|||Hello don i tried this solution from this linkhttp://www.sqlservercentral.com/columnists/mcoles/sql2005symmetricencryption.asp and trying to encrypt and decrypt using database master key , creating certificate without password but here i am not able to decrypt the column with this method, can u plz help me on this.|||
Why don't you post the code you're using at this point? That will make it easier to see what you're trying to do.
And is a certificate the best type of encryption to use? Why did you select that? (I don't mean to imply there is anything wrong with that choice. I just want to understand what you are trying to do.)
Don
|||thanx don, earlier i was trying the encryption using password. But when i tried using master key it worked fine.
thanx
