Encrypting Stored Procedure Code

19 Jan 2006 7:13 PM

darrin.wilkinson

Hi,

Is it possible to encrypt the code within a stored procedure in
Microsoft SQL Server?

My example is:

I've written a stored procedure. I don't want anyone to be able to
view the contents/code within this stored procedure unless I allow them
to see what is in it.

Thanks,
Darrin

19 Jan 2006 7:21 PM

Aaron Bertrand [SQL Server MVP]

> Is it possible to encrypt the code within a stored procedure in
> Microsoft SQL Server?

Sure, but it is not very secure. A google search will yield plenty of
decryption algorithms. For example:
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1056869,00.html

19 Jan 2006 7:29 PM

SQL

Once you encrypt a stored procedure you can not decrypt it (within SQL
Server)
So you can't show it to people who you want to show it to

example
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GO

But like Aaron said this is very weak, there are some vb apps out there
that will decrypt this

http://sqlservercode.blogspot.com/

22 Feb 2006 12:50 PM

Cranfield

I know there are decryptors out there for SQL2000 but do you know of nay that
have been written for SQL2005?

We encrypt our procs mainly as a safeguard against accidentally altering
them but do need a decryptor when we need to edit them. Our upgrade to
SQL2005 will be delayed untill we have a decryptor.

--
-- cranfield, DBA

Show quote

"Aaron Bertrand [SQL Server MVP]" wrote:

> > Is it possible to encrypt the code within a stored procedure in
> > Microsoft SQL Server?
>
> Sure, but it is not very secure. A google search will yield plenty of
> decryption algorithms. For example:
> http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1056869,00.html
>
>
>

22 Feb 2006 2:50 PM

Aaron Bertrand [SQL Server MVP]

>I know there are decryptors out there for SQL2000 but do you know of nay
>that
> have been written for SQL2005?

I will admit that I have only spent 5 minutes looking, but I have yet to
find where the encrypted text is stored, since sys.sql_modules.definition,
sys.syscomments.ctext and object_definition() all return NULL. My guess is,
to make the encoding a little more obscure, that they stuff this into
mssqlsystemresource, or hide it in some obscure system view. So, you may be
able to get to it, you may not.

> We encrypt our procs mainly as a safeguard against accidentally altering
> them but do need a decryptor when we need to edit them.

Isn't that what source control is for? And doesn't that defeat the purpose
of encrypting them in the first place? If someone can accidentally alter a
production procedure, they can also do accidentally after using a decryption
method to view the text. Encryption does not, and will never, solve the
problem of lack of adherence to proper process. My suggestion is to correct
the process.

A

23 Feb 2006 1:53 PM

Cranfield

Thanks for the response. Yes, I fully appreciate the importance of source
control and am confident that our dev department use it properly.

As a production DBA, though, looking after 100+ SQL Servers, when called at
2am to fix a performance "issue", the Decryptor is essential as you dont have
the time to delve into VSS. Also its essential when you need to compare an
existing proc to a proc in Source Safe.

With the move to SQL2005, we will, as suggested, need to look at our
process. It may mean moving away from encrypting procs.

--
-- cranfield, DBA

Show quote

"Aaron Bertrand [SQL Server MVP]" wrote:

> >I know there are decryptors out there for SQL2000 but do you know of nay
> >that
> > have been written for SQL2005?
>
> I will admit that I have only spent 5 minutes looking, but I have yet to
> find where the encrypted text is stored, since sys.sql_modules.definition,
> sys.syscomments.ctext and object_definition() all return NULL. My guess is,
> to make the encoding a little more obscure, that they stuff this into
> mssqlsystemresource, or hide it in some obscure system view. So, you may be
> able to get to it, you may not.
>
> > We encrypt our procs mainly as a safeguard against accidentally altering
> > them but do need a decryptor when we need to edit them.
>
> Isn't that what source control is for? And doesn't that defeat the purpose
> of encrypting them in the first place? If someone can accidentally alter a
> production procedure, they can also do accidentally after using a decryption
> method to view the text. Encryption does not, and will never, solve the
> problem of lack of adherence to proper process. My suggestion is to correct
> the process.
>
> A
>
>
>