Compare problem with Encrypted Stored Procedures

deweigend

We recently purchased a tool called SQL Shield from Activecrypt and installed it on one of our QA servers. Before encrypting each stored procedure, view, UDF, and trigger we were able to use SQL Compare and get results in a relatively short period of time (approx 15 - 25 seconds to register and do the compare). The database giving us the problem is large with several thousand objects (stored procedure, views, etc). Now is takes over 10 minutes and usually errors with an "out of memory" error. Since it is not an option to remove the encryption is there any way to exclude the stored procedure, views, etc so they are not included during the DB registration? Has anyone else seen this problem with SQL Shield? It does appear to me that SQL Compare is attempting to decrypt everything so is it at least possible to turn this feature off?

Don Weigend

Daniel Handley

Hi Don

Unfortunately there is no way to exclude these objects from the registration process. The reason is that because we use our own dependency engine it must view all objects to create its map.

Regards
Dan

deweigend

It does seem that SQL Compare tries to decrypt each encrypted object, is there anyway to turn off this feature. All our QA, UA and production servers will have encrypted objects (SP's, views, UDF's, triggers). As it stands now, we will not be able to use SQL Compare against any of these servers.

Brian Donahue

Hi Don,

Not really, but if the object cannot be decrypted because you are not a member of the sysadmin or securityadmin server roles, the object will remain encrypted and 'invisible' to SQL Compare.

You should be able to get away with this if you log on to the database using a limited account with only public or db_datareader access. When you view any encrypted objects registered under this account, you will see only 'this is an encrypted object and cannot be viewed'.