Re: is WITH ENCRYPTION now safe in SQL2005?

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

....
> 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.
>
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.

> 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.
>
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.

> 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.
>
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.

....
> 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 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
.

Relevant Pages

  • Re: Dynamically selected columns with column switch option
    ... the middle tier passes to the stored procs. ... So we are facing a design problem here. ... they might have to add new requests with new column sets. ... broad SELECT statements will force the sql server to produce huge ...
    (microsoft.public.sqlserver.programming)
  • Re: Encrypting SQL objects
    ... objects, i.e. Stored procs, Views, triggers. ... engine must be able to read the source code at run to be able to compile ... And if SQL Server has access to it, ...
    (microsoft.public.sqlserver.security)
  • ADO error handling when connecting to SQL Server 2000
    ... I use Delphi to connect to SQL Server 2000 using the ADO components. ... executing stored procedure that makes some calls to another stored procs. ...
    (microsoft.public.sqlserver.programming)
  • Re: is WITH ENCRYPTION now safe in SQL2005?
    ... > Yes, i know, i don't like extended stored procs too, but i know no other ... In older versions of SQL Server, SQL Server did in fact stored some sort ... of "object code" in sysprocedures. ... the old arrangement and the final result is a cleaner architecture. ...
    (comp.databases.ms-sqlserver)
  • Re: Any script to encrypt the all SPs and Views in one go
    ... you won't easily be able to tell where to add the "WITH ENCRYPTION" ... automate re-creation, because it will come back as multiple rows. ... and error to produce another script which can be run in an automated ... > or stored procs for our clients, ...
    (microsoft.public.sqlserver.programming)