2

I am using SQL Server 2008 Enterprise on Windows Server 2003 Enterprise. I developed some store procedure for SQL Server and the machine installed with SQL Server may not be fully under my control (may be used by un-trusted 3rd party).

I want to protect my store procedure T-SQL source code (i.e. not viewable by some other party) by using encrypt store procedure function provided by SQL Server. I am not sure whether encrypt store procedure is 100% safe and whether the administrator of the machine (installed with SQL Server) still have ways to view store procedure's source codes?

thanks in advance, George

George2
  • 1,137
  • 6
  • 22
  • 41

3 Answers3

1

It's 'safe' in that SQL Server has stored both the encrypted procedure text and the key required to decrypt it in your database, so when it needs the procedure text - to compile the query plan in order to execute it - it can decrypt it.

The most SQL Server can ever do is obfuscate the key, trying to hide it so it isn't easily stolen.

Historically there was a replay attack, where if you used ALTER PROCEDURE it would re-use the same key. By making use of ALTER PROCEDURE with a known plaintext, you could compare the resulting encrypted output and recover the keystream, then use that to decrypt the original encrypted text. (It uses the RC4 stream cipher, which produces a pseudorandom string of bits called the keystream, which is then XORed with the plaintext to encrypt, or XORed with the encrypted text to decrypt.) I don't know if that's still the case in SQL Server 2008.

Mike Dimmick
  • 251
  • 2
  • 4
1

A SQl server DBA will always have a way to view the contents of a stored procedure if they try hard enough. The only way to make this effort more difficult is to use a 3rd party product like sql-shield that will use a different encryption scheme to preven the "usual' decryption attacks from being sucessful. Whenever you give someone encrypted data and access to the decryption key you can never prevent decryption of the data ( no matter how obfuscated the process might be ). Encryption is only really "good" when the encryption key and the data not available together. You can also consider obfuscating your code by using a CLR stored procedure and any of a number of .net obfuscators available. See CLR Stored Procedures for an example.

Jim B
  • 23,938
  • 4
  • 35
  • 58
1

Be assured that even if you use encryption for Stored Procedures in SQL Server 2008, it could be very easily decrypted using third party tools.There are lots of them and one of them is http://www.elitude.net/.

If you are using proprietary software then adding another layer using encryption is NOT going to be enough if there is a determined user. All you can do is ask them to sign a Non-Disclosure Agreement (NDA) and stand by these rules.

Sankar Reddy
  • 1,374
  • 8
  • 8