...juggling with .net & sql: How to protect code written in Stored Procedure or User Defined Function?
How to protect code written in Stored Procedure or User Defined Function?
We have always been wondering on how to protect/secure our t-sql code written in Stored Procedures and UDF in shared hosting environment from other people who might tweak our work. Here, I would discuss a few options I know to serve purpose.
However, please make sure that the code of the SP is backed up as a separate script file for future references.
and script can not be generated.
a. Once encrypted, it is not possible to decrypt using SQL Server commands.
a. We as developers will always have to keep a copy of the SP/UDF as a script for our reference or future updates.
b. One way I know using which this approach could be defeated is by running SQL Profiler while executing the stored procedure.
c. Another way that users might use to get at your encrypted code is by using readily available code (if any) that allows you to break SQL Server's relatively trivial encryption algorithm.
b. Secondly, we can make use of sql-clr functions wherever possible. This would again help us in hiding our logic from the preying eyes. Please refer http://msdn.microsoft.com/en-us/library/ms345136(v=sql.90).aspx for more details.
One should not make use of WITH ENCRYPTION option unless it’s the last option and have a thorough knowledge of its consequences after implementation. However, I would strongly recommend NOT TO USE it.