Is it possible to protect SQL 2008 stored procedure code from anyone`s eyes? Maybe some encryption, or assembling like dll?
Asked
Active
Viewed 1,509 times
1 Answers
4
yes you can save it ti the database in encrypted form, but if you do, make sure you have the original source code safely stored somewhere...
CREATE PROCEDURE dbo.foo
WITH ENCRYPTION
AS
BEGIN
SELECT 'foo'
END
Unfortunately, there are at least two ways to defeat this mechanism. One is to run SQL Profiler while executing the stored procedure; this often can reveal the text of the procedure itself, depending on what the stored procedure does (e.g. if it has GO batches, dynamic SQL etc). If they miss the initial install, the user can delete the stored procedures or drop the database, start a Profiler trace, and ask you to re-create them (in which case they will capture the CREATE PROCEDURE statements). You can prevent Profiler from revealing the text to snoopers by embedding sp_password in the code, as a comment:
CREATE PROCEDURE dbo.foo
WITH ENCRYPTION
AS
BEGIN
SELECT 'foo'
-- comment: sp_password
END
look at MSDN Create Procedure documention
Charles Bretana
- 143,358
- 22
- 150
- 216
-
does this method have name? or maybe any Link? – alkersan Sep 11 '09 at 20:39
-
+1 for the nice answer (although I personally see no need for the technique involved) – ChristopheD Sep 11 '09 at 20:49
-
Unfortunately, it takes less than 5 mn on google to find a way to bypass the trivial encryption built in SQL server. If you really want to protect your code, you might want to look for a commercial solution – vincent jacquel Nov 23 '14 at 11:26