Bug with SQL Compare involving Encrypted Stored Procedures
Francis Chung
Posts: 3
Hi RedGate Support,
I've noticed a bug with SQL Compare involving DBs with Encrypted Sprocs on 1 side.
I've had no problems in the past comparing our Prod DB (Some encrypted Sprocs, No DBO) vs Our Dev DB (No encrypted Sprocs, DBO) using SQL Compare. Obviously, it would skip the encrypted sprocs but that was as expected.
It stopped working recently after a product update as it returns a Invalid Cast Exception of NULL->String during the Read Constraints stage of the Prod DB.
Trawling through the forums, it appears that I now need DBO Access to the Production DB as well to decrypt the encrypted sprocs. This is not a viable option not only for myself but I suspect many of your userbase in a similar situation.
I tried unchecking the Decrypt encrypted Objects option as well as checking the Ignore Check Constraints option to no avail.
Is there anyway of skipping the comparison of Encrypted Sprocs as previously ?
(i.e. New Ignore encrypted objects would be useful)
Thanks in Advance.
Francis
I've noticed a bug with SQL Compare involving DBs with Encrypted Sprocs on 1 side.
I've had no problems in the past comparing our Prod DB (Some encrypted Sprocs, No DBO) vs Our Dev DB (No encrypted Sprocs, DBO) using SQL Compare. Obviously, it would skip the encrypted sprocs but that was as expected.
It stopped working recently after a product update as it returns a Invalid Cast Exception of NULL->String during the Read Constraints stage of the Prod DB.
Trawling through the forums, it appears that I now need DBO Access to the Production DB as well to decrypt the encrypted sprocs. This is not a viable option not only for myself but I suspect many of your userbase in a similar situation.
I tried unchecking the Decrypt encrypted Objects option as well as checking the Ignore Check Constraints option to no avail.
Is there anyway of skipping the comparison of Encrypted Sprocs as previously ?
(i.e. New Ignore encrypted objects would be useful)
Thanks in Advance.
Francis
Comments
In SQL Compare, the option you want is now under "behavior" on the "Options" tab and is called "Decrypt Encrypted objects on 2005 and 2008 databases".
The procedure for decrypting the encrypted objects involves reading pages directly from the database and therefore sysadmin access is unavoidable.
If you still get an exception then there is probably some other bug and you should probably email support directly so we can investigate.
I have now posted an error report to Redgate Support, referring them to this forum for the problem details.
Regards,
Francis