Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I used symmetric encryption to encrypt sensitive data in my application. I don't want anyone see my password for decrypting the data. But system admin can able to see symmetric password in SQL Server Profiler.

How can I stop them ?

Profiler Result :

exec sp_executesql N' OPEN SYMMETRIC KEY sym_RegAna_Default DECRYPTION BY ASYMMETRIC KEY asym_RegAna_Default WITH PASSWORD = ''admin@123'' ;  
share|improve this question
    
If you don' trust your system admin not to snoop around, hire a new one that you do. –  vonPryz Dec 27 '13 at 9:40
    
@vonPryz to name one example: Having your database hosted somewhere in a 3rd party cloud. –  Edward Dortland Dec 27 '13 at 10:06
    
@EdwardDortland The question boils down to trust. A sysadmin has lots of ways to steal secrets ranging from physical access to protocol analysis. Valuable and sensitive data into cloud belongs not. –  vonPryz Dec 27 '13 at 12:24

1 Answer 1

Don't execute the statement as a string by using sp_executesql

Execute the statement directly:

OPEN SYMMETRIC KEY sym_RegAna_Default DECRYPTION BY ASYMMETRIC KEY asym_RegAna_Default WITH PASSWORD = 'admin@123'

That way, SQL Server is able to parse the statement and will notice that it's a command that contains sensitive information and will not show it in any trace output.

it will show up as:

--*OPEN SYMMETRIC KEY----------------------------

Please be aware that this applies to ALL statements that contain passwords. So also when you create the keys.

share|improve this answer
    
Thanks for your reply. I did not use "sp_executesql" to execute my query from c# code. Please find below my c# code "OPEN SYMMETRIC KEY sym_RegAna_Default DECRYPTION BY ASYMMETRIC KEY asym_RegAna_Default WITH PASSWORD = ''admin@123''; select CONVERT(varchar(max),DecryptByKey(UniqueNumber)) from employeeTable;CLOSE SYMMETRIC KEY sym_RegAna_Default; ". But the sql profiler return the result like that. –  vijay Dec 27 '13 at 9:33
    
Update your question with the relevant C# code. Including how you open the connection, which (if applicable) Data access layer you are using . Your question might get migrated to another stackexchange but lets first see the code. –  Edward Dortland Dec 27 '13 at 9:41
    
@vijay You need to show the C# part of your code too. Likely it is the one that sends SQL statements to sp_executesql. –  vonPryz Dec 27 '13 at 9:41
    
//Creating query. StringBuilder query = new StringBuilder(); query.Append(" OPEN SYMMETRIC KEY sym_RegAna_Default DECRYPTION BY ASYMMETRIC KEY asym_RegAna_Default WITH PASSWORD = 'admin@123' ; "); query.Append(" select CONVERT(varchar(max),DecryptByKey(UniqueIdentificationNumber)) as DecryptedNumber from EmployeePersonalDetailsTable "); query.Append("; CLOSE SYMMETRIC KEY sym_RegAna_Default;"); fetchQuery.SelectCondition = query; DBAccess dbAccess = new DBAccess(getConnectionStringName()); dbDataReader = dbAccess.ExecuteReader(fetchQuery); –  vijay Dec 27 '13 at 9:49
2  
@vijay Do you understand that definitions for fetchQuery and DBAccess would be quite important in this context? –  vonPryz Dec 27 '13 at 9:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.