How to Obfuscate SQL Sprocs?

Question

Is there a way to hide/protect/obfuscate MS SQL Stored Procedures?

Answer 1

I can vaguely understand obfuscating code if it's extremely advanced in what it does, but I think obfuscating your SQL may not be worth the hassle.

Anyway, a lot of the SQL I've seen around here comes obfuscated as standard.

Answer 2

You can always write ordinary code in C# (or VB) and store it outside the database in a DLL.

Then you don't have to worry about obfuscating your SQL.

Answer 3

If you must hide it, how about the "WITH ENCRYPTION" clause?

http://blog.sqlauthority.com/2007/07/01/sql-server-explanation-of-with-encryption-clause-for-stored-procedure-and-user-defined-functions/

Answer 4

See the ENCRYPTION option for the CREATE PROCEDURE statement.

http://msdn.microsoft.com/en-us/library/ms187926.aspx

Answer 5

If you're really worried about someone getting into the DB and seeing the source for the procedure, then as S. Lott said, you can port the procedure to C#. I would recommend LINQ.

However, the database itself should probably be protected from people accessing the code for procedures that shouldn't be. You can restrict a user or group's rights to only have EXECUTE access to a proc if needed.

Answer 6

You could use the ENCRYPTION clause when creating the stored procedure.

This would rely on not leaving the source SQL on the customer machine though.

See here for more info:

http://msdn.microsoft.com/en-us/library/ms187926(SQL.90).aspx

Answer 7

No. At least, not in a way that is un-reversible. SQL Server 2000's "WITH ENCRYPTION" can be reversed to get the original plaintext. The pseudo-code and a T-SQL script that illustrates this is here: http://education.sqlfarms.com/education/ShowPost.aspx?PostID=783

Note: I haven't tried it with SQL 2005 or above, but my guess is it is just as vulnerable.. As the MSDN docs state:

ENCRYPTION Indicates that SQL Server will convert the original text of the CREATE PROCEDURE statement to an obfuscated format.

Emphasis mine.

Answer 8

One option would be to place just the sensitive portions of the stored procedure in a CLR stored procedure, and obfuscate that assembly using a professional obfuscation product.

http://msdn.microsoft.com/en-us/library/ms131094.aspx

Answer 9

Easily reversible if you know but intimidating to to most people poking around code. hex encode you sproc logic and then execute with EXEC(@hexEncodedString).
see this post.

Answer 10

Thanks for the help!