is WITH ENCRYPTION now safe in SQL2005?

[helmut woess]

Hi,

has anybody knowledge about the safetyness of encrypting stored procs in
SQL-Server 2005 using WITH ENCRYPTION? Or can they be hacked with the same
old tools which exists for SQL 2000?

thanks,
Helmut

[Mike Epprecht \(SQL MVP\)]

Re: is WITH ENCRYPTION now safe in SQL2005?

Hi

Nothing has changed in this area.

Microsoft has never said that this feature is secure. With SQL Server 2005
you can set granular security so not everyone can get to the text of a
procedure.

If I get an application that encrypts it's SP, I will generally be more
inquisitive as to why it was done, and in the name of ensuring that the
procedure does not execute any code that could harm the system or the
corporate security, I will decrypt it and see what it is doing. Business
logic belongs in the middle tier, not the DB.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.n et

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"helmut woess" <hw@iis.at> wrote in message
news:55221fc879 9f.1cbqe7zcjsjh 4.dlg@40tude.ne t...[color=blue]
> Hi,
>
> has anybody knowledge about the safetyness of encrypting stored procs in
> SQL-Server 2005 using WITH ENCRYPTION? Or can they be hacked with the same
> old tools which exists for SQL 2000?
>
> thanks,
> Helmut[/color]

[Erland Sommarskog]

Re: is WITH ENCRYPTION now safe in SQL2005?

Mike Epprecht (SQL MVP) (mike@epprecht. net) writes:[color=blue]
> If I get an application that encrypts it's SP, I will generally be more
> inquisitive as to why it was done, and in the name of ensuring that the
> procedure does not execute any code that could harm the system or the
> corporate security, I will decrypt it and see what it is doing. Business
> logic belongs in the middle tier, not the DB.[/color]

On that we have radically different opinions. Business logic that
concerns data should be in the database. Period.

And, yes, we usually encrypt our stored procedures when we install our
system at customer sites. Just like people usually put up a fence around
their house, so that no one trespasses by mistake.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[helmut woess]

Re: is WITH ENCRYPTION now safe in SQL2005?

Am Sat, 5 Nov 2005 20:45:22 +0000 (UTC) schrieb Erland Sommarskog:

....[color=blue]
> On that we have radically different opinions. Business logic that
> concerns data should be in the database. Period.[/color]

I fully agree with you!
[color=blue]
> And, yes, we usually encrypt our stored procedures when we install our
> system at customer sites. Just like people usually put up a fence around
> their house, so that no one trespasses by mistake.[/color]

And it is a shame that microsoft knows about their weak encryption and is
nothing doing against it. I put a lot of brainpower into my stored procs
and cannot protect it. So i have to use extended stored procs further on.

bye,
Helmut

[helmut woess]

Re: is WITH ENCRYPTION now safe in SQL2005?

Am Sat, 5 Nov 2005 21:31:16 +0100 schrieb Mike Epprecht (SQL MVP):
[color=blue]
> ... I will generally be more
> inquisitive as to why it was done, and in the name of ensuring that the
> procedure does not execute any code that could harm the system or the
> corporate security, I will decrypt it and see what it is doing. ...
>[/color]
And what is with other software where you have no sourcecode? Do you
disassemble them to see that they are not dangerous? :-))

I want to secure my knowledge which is in the stored procs, because i am a
company and i don't want that everybody can copy my ideas so easy.
Customers can trust me or they buy another solution. But i cannot decide if
a prospective client is a potential buyer or a competitor who wants to get
some knowledge about my product.

bye,
Helmut

[Erland Sommarskog]

Re: is WITH ENCRYPTION now safe in SQL2005?

helmut woess (hw@iis.at) writes:[color=blue]
> And it is a shame that microsoft knows about their weak encryption and is
> nothing doing against it. I put a lot of brainpower into my stored procs
> and cannot protect it. So i have to use extended stored procs further on.[/color]

There are very dissenting opinions on that in the SQL Server community.
While you and I as software vendors would like to make our procedures
uncrackable, many of my MVP colleagues work as consultants and are
called into sites where they run some third-party package where they
for one reason or another do not get good support from the vendor. When
you are in this situation you are quite happy that you can decrypt the
procedures so you can fix bad code.

There are also a few things to keep in mind about enscryption of stored
procedures. How would a safe encryption method be implemented? SQL Server
must be able to decrypt the encrypted code, so it can parse the code
and build a query plan from it. So even if you use a method with a
private key, SQL Server must have access to that private key. And strong
encryption and decryption with private keys of good length takes
CPU resources. It is not acceptable if takes 30 seconds to compile a
stored procedure - keep in mind that compilation takes place at run-time.

As for using extended stored procedures, this is entirely poor idea. The
overhead for extended stored procedures is considerable, and puts the
stability of SQL Server at risk, as an execution error in an XP brings
down SQL Server. And, anyway, an XP can be disassembled as well.

In SQL 2005 you can write stored proecdures in .Net languages. With CLR
procedures you don't face the stability problems of XP:s, nor the overhead
of context switches and that. But the assemblies can still be disassembled.
And in the Profiler you can see what SQL statements you submit.

And that brings us to the most critical point. Even if the code itself
is encrypted, you have table definitions etc that are visible. And even
in the theoretical case that the code can not be reconstructed, there is so
much information that can be deducted by looking at query plans, that
you can get quite a good idea of what is going on anyway.

So if your aim is to protect your intellectual property, license agreements
is the way to go. WITH ENCRYPTION is not a bad idea, because as I said
in another post, it tells people that they are not supposed to enter here.

I should also add that SQL 2005 does add a whole lot in the encryption
area with certificates, symmetric and asymmetric keys, so if you want to
encrypt data with strong encryption, SQL 2005 has a good support for this.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[helmut woess]

Re: is WITH ENCRYPTION now safe in SQL2005?

Am Sun, 6 Nov 2005 10:20:12 +0000 (UTC) schrieb Erland Sommarskog:

....[color=blue]
> There are very dissenting opinions on that in the SQL Server community.
> While you and I as software vendors would like to make our procedures
> uncrackable, many of my MVP colleagues work as consultants and are
> called into sites where they run some third-party package where they
> for one reason or another do not get good support from the vendor. When
> you are in this situation you are quite happy that you can decrypt the
> procedures so you can fix bad code.
>[/color]
Yes, everything has two sides. But i never touch foreign stored procs for
repairing something! If a customer has a problem with a software not from
me, then he has to clear this with his vendor - or he can change his
software and work with my solution.
[color=blue]
> There are also a few things to keep in mind about enscryption of stored
> procedures. How would a safe encryption method be implemented? SQL Server
> must be able to decrypt the encrypted code, so it can parse the code
> and build a query plan from it. So even if you use a method with a
> private key, SQL Server must have access to that private key. And strong
> encryption and decryption with private keys of good length takes
> CPU resources. It is not acceptable if takes 30 seconds to compile a
> stored procedure - keep in mind that compilation takes place at run-time.
>[/color]
I worked some years ago with interbase/firebird, and as i remember i was
able to hold only a precompiled version of the stored proc and delete the
clear text. I don't know how save this was but i never heard something
about disassemled stored procs from firebird.
Something i wish for the stored procs in SQL-Server - not encrypted but
compiled. If the cost for understanding a disassemled procedure is higher
then writing it by yourself, then it would be enough.
[color=blue]
> As for using extended stored procedures, this is entirely poor idea. The
> overhead for extended stored procedures is considerable, and puts the
> stability of SQL Server at risk, as an execution error in an XP brings
> down SQL Server. And, anyway, an XP can be disassembled as well.
>[/color]
Yes, i know, i don't like extended stored procs too, but i know no other
way to secure a stored proc. And disassembling is much more harder then
reading a clear text. I need not 100% security, but it should not be sooo
easy to decrypt the source.

....[color=blue]
> So if your aim is to protect your intellectual property, license agreements
> is the way to go. WITH ENCRYPTION is not a bad idea, because as I said
> in another post, it tells people that they are not supposed to enter here.
>[/color]
I want to prevent damage before it can happen because i have not the time
nor the money to bring an action against somebody.

bye,
Helmut

[Erland Sommarskog]

Re: is WITH ENCRYPTION now safe in SQL2005?

helmut woess (hw@iis.at) writes:[color=blue]
> Yes, everything has two sides. But i never touch foreign stored procs for
> repairing something! If a customer has a problem with a software not from
> me, then he has to clear this with his vendor - or he can change his
> software and work with my solution.[/color]

I think there are a lot of people out there who can tell you horror stories
of third-party vendors that for one reason or another do not offer support.
The company may folded, or the support fees is simply outrageous.
[color=blue]
> Yes, i know, i don't like extended stored procs too, but i know no other
> way to secure a stored proc. And disassembling is much more harder then
> reading a clear text. I need not 100% security, but it should not be sooo
> easy to decrypt the source.
>...
> I want to prevent damage before it can happen because i have not the time
> nor the money to bring an action against somebody.[/color]

Look, if someone is dead set on stealing your code, disassembling is not
going to stop him.

In older versions of SQL Server, SQL Server did in fact stored some sort
of "object code" in sysprocedures. This was abandoned with the re-
architecture in SQL 7. This also resolved some gotchas that came with
the old arrangement and the final result is a cleaner architecture.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp