Re: is WITH ENCRYPTION now safe in SQL2005?

helmut woess (hw@xxxxxx) writes:
> 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.

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@xxxxxxxxxxxxx

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

.

Relevant Pages

  • Re: CryptAPI(encryption/decryption)
    ... It seems like you're missing the Base64 decode step when trying to decrypt ... I misspelled the Private Key as Primary Key. ... Is there any variation in the encryption format in openssl compared to ... "Dylan DSilva " wrote: ...
    (microsoft.public.pocketpc.developer)
  • Re: CryptAPI(encryption/decryption)
    ... The openssl encrypted data format is in bigendian ... Is there any way I can import the PEM formated private key to the MS CSP ... I'm decoding the base64 encoded data before trying to decrypt. ... Is there any variation in the encryption format in openssl compared ...
    (microsoft.public.pocketpc.developer)
  • Re: CryptAPI(encryption/decryption)
    ... since symmetric encryption is faster than public key encryption. ... As per your reply I could get the handle of the private key. ... possible for B to decrypt the data using his Private Key. ... The PFX format encrypts the private key with the user supplied password ...
    (microsoft.public.pocketpc.developer)
  • Re: No way to encrypt with private key in C#?
    ... 1)if a file is encrypted with the private key, ... 2)if a file is encrypted with the public key, ... The two ways are usually called encryption and signing, from public to private and back again. ... There is nothing prohibiting an application using RSA to "encrypt" the entire file using the private key and release it, so that everyone that wants to use it must first decrypt it with the public key. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: DRA is Decrypting Files when it shouldnt be!!!
    ... > EFS is allowing the RA to decrypt 200 files that were encrypted BEFORE an RA ... > encryption to get the RA to decrypt encrypted files. ... the default RA certificate was used. ... certificate and private key only when needed). ...
    (microsoft.public.windowsxp.security_admin)