Reputation: 133

How to Obfuscate SQL Sprocs?

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

Upvotes: 8

Answers (11)

Harry Moreno

Reputation: 11603

I use this tool to obfuscate sql https://harrymoreno.com/sql-obfuscator/

it replaces table and column names with letters

Upvotes: 0

Matt Rogish

Reputation: 24873

No. At least, not in a way that is irreversible. 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 is mine.

Upvotes: 4

InbetweenWeekends

Reputation: 1414

Old post, I know. But I got here from searching 'Why should I obfuscate SQL?' I just installed a free product called ApexSQL Refactor (no affiliation) which offers an obfuscation component.

It offers several different options for making your code hard to read. I wasn't sure why I'd want such a feature given, as others noted the ability to encrypt your stored procedures. Anyway, this is an example of the output it can return from it's obfuscation function.

CrEAtE Procedure spInsertOrUpdateProduct @ProductNumber nVarChar(25),
@ListPrice Money aS IF exIsTS(selECt * FROm Production.Product WHere
ProductNumber=@ProductNumber AnD ListPrice>1000) uPdatE Production.
Product sET ListPrice=(ListPrice-100) where ProductNumber=
@ProductNumber elsE INSerT intO Production.Product(ProductNumber,
ListPrice) SelECT @ProductNumber,@ListPrice GO SElEct * fRoM
Production.Product gO iNsERT iNTo Production.UnitMeasure(
UnitMeasureCode,Name,ModifiedDate) vAlUeS(N'FT2',N'Square Feet',
'20080923'); Go

Upvotes: 2

jason saldo

Reputation: 9950

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.

Upvotes: 2

Jon Galloway

Reputation: 53115

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

Upvotes: 2

Kev

Reputation: 119806

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

Upvotes: 1

regex

Reputation: 3601

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.

Upvotes: 0