Use CAPICOM to ENCRYPT a Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Use CAPICOM to ENCRYPT a Stored Procedure

Author

Topic

empigi
Starting Member

8 Posts

Posted - 2008-09-04 : 16:40:32

Hi to all,

Is it possible to use CAPICOM in order to encrypt the T-SQL code contained in a Stored Procedure (or a view, function etc)?

The WITH ENCRYPTION clause is of no use (too easy to decrypt)...

There is some Software on the net that can do this, but I'd like to do it on my own...

Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-05 : 02:48:34

Only if you include the decrypt algorithm into the SQL Server query engine.

E 12°55'05.63"
N 56°04'39.26"

empigi
Starting Member

8 Posts

Posted - 2008-09-05 : 09:30:10

Could you please send me some code? Or a link where find some examples.

I'd really appreciate this.

Thank you.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-09-05 : 10:26:35

What Peso is trying to say is...
NO!

Boycotted Beijing Olympics 2008

empigi
Starting Member

8 Posts

Posted - 2008-09-05 : 10:31:36

WOW !!!

Have you already "digested" with this problem and haven't found anything?
I was so hopeful after the Peso's post ...

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-09-05 : 11:53:01

Peso is trying to tell you that if you encrypt the sproc using your own method then the engine will not be able to decrypt it to run it.

Boycotted Beijing Olympics 2008

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-09-05 : 13:07:38

1. write all your procs as CLR procs
2. use only Reflection.Emit() calls in the CLR procs to do all the work
3. run them through an obfuscator before you import the assemblies to SQL Server.

if you do this, not only will nobody be able to understand what your procs are doing, you won't either.

elsasoft.org

empigi
Starting Member

8 Posts

Posted - 2008-09-05 : 13:32:19

REALLY GREAT SOLUTION!

Actually doing this will allow me to get to my point: protect the Business Logic from beeing manipulated, viewed etc.

Thank you !!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-09-05 : 13:35:32

And for ultimate security, when you are finished coding the application shoot yourself in the head.

Boycotted Beijing Olympics 2008

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-05 : 13:41:37

quote:
Originally posted by empigi

Hi to all,

Is it possible to use CAPICOM in order to encrypt the T-SQL code contained in a Stored Procedure (or a view, function etc)?

The WITH ENCRYPTION clause is of no use (too easy to decrypt)...

There is some Software on the net that can do this, but I'd like to do it on my own...

Thanks in advance.

For 3rd party tools see

http://www.xpcrypt.com/
and
http://www.quest.com/toad/
But note: MS SQL 2008 enterprise comes with encryptions as standard

empigi
Starting Member

8 Posts

Posted - 2008-09-05 : 14:10:50

"But note: MS SQL 2008 enterprise comes with encryptions as standard"

So is the SQL 2008 Encryption harder to "decompile"?

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-05 : 14:16:06

I dont know honestly.

We cant afford to deploy it now. A dedicated version by our web hosting company cost $1,000 monthly

empigi
Starting Member

8 Posts

Posted - 2008-09-05 : 14:17:09

well blindman ... this could be a solution ... before using the suggestion I'd like to try it out. Any volunteers ?

Anyway, I got 2 replies that really helped, thanks to jezemine and afrika.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-09-05 : 15:26:18

quote:
Originally posted by empigi

well blindman ... this could be a solution ... before using the suggestion I'd like to try it out. Any volunteers ?

LOL
My suggestion was an "undocumented feature", so employ it at your own risk!

Boycotted Beijing Olympics 2008

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-05 : 15:45:55

quote:
Originally posted by empigi

well blindman ... this could be a solution ... before using the suggestion I'd like to try it out. Any volunteers ?

Anyway, I got 2 replies that really helped, thanks to jezemine and afrika.

Glad2help

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-09-05 : 15:54:23

I forgot to mention these other steps:

1. implement CLR support for the WHITESPACE language
2. imepement all your CLR procs in WHITESPACE. Don't use C# or any other standard language. Those are for wimps.

that way your printouts of the procs will be secure.

elsasoft.org

empigi
Starting Member

8 Posts

Posted - 2008-09-05 : 16:17:03

Jezemin,

Do you happen to have some examples ? What do you intend for Whitespace language?
I was thinking about using C# ...

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-09-05 : 16:25:53

Apparently, jezemine, you are far more subtle than I am.
Score one point for you.

Boycotted Beijing Olympics 2008

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-09-05 : 19:13:19