Drawbacks to SQL Server 2005+ object encryption?

Question

For a database that is shipped with a remotely deployed product, are there any drawbacks to encrypting all code objects (procs, functions, views) via the WITH ENCRYPTION clause in MS SQL Server 2005 and 2008?

The advantages are a deterrent to "custom" changes in the field that solve one need while creating many more. The goal is not to protect IP or data, but to help prevent unauthorized code changes when the rights to alter objects cannot be managed (the security is out of the developers hands, but the responsibility to fix issues caused by others is).

What new problems could be caused by taking this approach?

Answer 1

Microsoft created this feature specifically for this purpose. The only drawback I can see is that the protection is weak; it can be circumvented by publically available scripts, and SQL Profiler can be used to view the clear text of the decrypted procedures. But it should still act as an effective deterrent.

Answer 2

We implemented encryption in an enterprise product that we deployed to 200+ sites for the reasons you indicated and to deter reverse engineering.

In our case, it was more trouble than it was worth. Our clients never really changed anything and it made it difficult for us to debug issues in the field. Profiler does not give you the detail needed when everything is encrypted. We would often have to load an unencrypted version of our SPROCs etc. to debug issues.

Also, it makes it harder to varify your schema is consistant with the version you are expecting. Most differencing tools can't diff encrypted objects.

Answer 3

You are down the wrong path. WITH ENCRYPTION is an obsolete option from the SQL 2000 days. The ways around it are well known and can be discovered by anyone with minimal google search skills. The proper way is to use code signing. Best way is to create a certificate, sign the procedures, then drop the private key. This way nobody, not even you, can change code afterward without destroying the signatures. This does not prevent anybody from modifying the procedures, but once modified they destroy the signature seal and the tampering is evident. You can take it one step further and make sure the procedures in themselves have no authority to execute their purpose (ie. cannot read the tables the try to access) but the authority is derived from their signatures (the certificate used to sign them is granted the needed rights). This way if they tamper with the code not only they destroy the seal, they also cause the system to halt. Explaining the consequences of these actions is usually a strong enough deterrent.