Hi - I have a sql 2008 database and some of the account information has been encrypted by the function "encryptbypassphrase"
I have a stored procedure to decrypt the vales once the passphase parameter is supplied by the user.
the user is apply to supply this passphrase though a asp.net application which I am building. My issue is that the profiler can easily trace the parameters passed to the sql server and this reveals the passphrase .
is there any way i can stop profiler to read the passphrase. Back in the sql 2000 days when a password was passed profiler would have hidden that textdata... is there something similar in sql2008?
- Moved by Jim Carley - MSFTMicrosoft employee Thursday, October 13, 2011 10:40 PM This question is more appropriate for a SQL Server forum. (From:Transactions Programming)
If your motivation is to protect data from anyone in the sysadmin role, using the EncryptByPassphrase() feature will require that you generate your DML statements in the application and send the batch to SQL Server. In this example, your application may store the passphrases outside of SQL Server in a configuration file or directly in the application code. Of course, the sysadmin should not have access to the source code or file location containing the passphrases. When the statement is sent to SQL Server from the application, SQL Server Profiler will not reveal text related to encryption functions. When your application code passes the DecryptByPassphrase() function coupled with the proper passphrase, Profiler will replace the text of the event with "The text has been replaced with this comment for security reasons." Navigating toward an approach to embed DML SQL statements within the application code carries a number of concerns. http://programming4.us/security/1074.aspx
See if ENCRYPTBYKEY function :- http://msdn.microsoft.com/en-us/library/ms174361.aspx can be used in this scenario. But it may need some changes in the coding.