question

jayaswal_raj avatar image
jayaswal_raj asked

Encryption of results sent by SQL Server Stored Procedure

My client requires the communication between my web application and database server to be done in encrypted manner. Currently, they are converting the SQL params into an encrypted XML and then pass this to web server. My issue is: 1) I am still able to view the plain text (params and even the actual data) using SQL Trace. 2) The results/Table sent by SQL Stored procedure is in XML (Plain). Is there any way to encrypt that communication as well. Thanks Asked same question here on : http://stackoverflow.com/questions/27729813/encryption-of-results-sent-by-sql-server-stored-procedure
t-sqlsql-serversql serverencryptionstored procedures
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Tom Staab avatar image
Tom Staab answered
I checked the link you provided, and someone there gave a link to the TechNet article explaining how to encrypt network protocols used by SQL Server. I found an updated article of the same topic [here][1], and that one includes some additional information. Alternatively, if you do not have permission to (or can/should not for any reason) change SQL Server configuration properties, there is a TSQL method of encrypting specific pieces of data using the function [EncryptByKey][2] (and DecryptByKey). Here's a simple example from MSDN assuming you create a symmertic key if you do not already have one): USE AdventureWorks2012; GO -- Create a column in which to store the encrypted data. ALTER TABLE HumanResources.Employee ADD EncryptedNationalIDNumber varbinary(128); GO -- Open the symmetric key with which to encrypt the data. OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037; -- Encrypt the value in column NationalIDNumber with symmetric key -- SSN_Key_01. Save the result in column EncryptedNationalIDNumber. UPDATE HumanResources.Employee SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber); GO [1]: http://msdn.microsoft.com/en-us/library/ms191192.aspx [2]: http://msdn.microsoft.com/en-us/library/ms174361.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.