How to Encrypt Stored Procedure

Question

Hi All,

We can precompile our (ASP.NET) websites and can publish only the IL code, so that the source code is not available to the customer.

But how do we do it for stored procedures written in SQL Server. I mean, when we give the customer the DB, he could see all my stored procedures and can modify the same... How could I protect it.

Thanks

Raja

Answer 1

use WITH ENCRYPTION

example

CREATE PROCEDURE prTest
WITH ENCRYPTION
AS
SELECT GETDATE()

Keep in mind that it can be cracked and also make sure you have the unencrypted source code backed up

Answer 2

CREATE PROCEDURE ... WITH ENCRYPTION

However note that this encryption is really more like obfuscation, and there are several ways to bypass it if your vendor is determined, including the DAC connection, some 3rd party products (including RedGate SQL Prompt), and code samples you can readily find online.

Answer 3

An old problem. Here are a few answers I've picked up here and there:

• Encrypt the stored procedures. As has already been pointed out twice, this doesn't really work, as 5 minutes of Googling will find several hacks.

• Write the stored procedures as CLR procedures. Harder to hack than "regular" stored procedures, probably a lot more effort to produce and support.

• Submit all queries dynamically from your compiled IL code. I understand it can be done reasonably secure from SQL injection attack, but make darn sure before you release. (Maybe use Linq to do this?)

• Convert all database object names (tables, columns, procedures) to guids or random gibberish. They could read it, but that wouldn't help much.

• I am not totaly conversant on encryption within SQL 2005 and up. I really don't think you can use it on code-based objects (procedures, funtions, etc.), but maybe you can?

But by and large, once you give a copy of your database to someone with SysAdmin rights, they can do pretty much anything they want with it.