Unable to decrypt Stored Procedure

Jacquers

Hi,

I have recently started using the sdk (fully licensed) and have found that it's not able to decrypt or script stored procedures (in the target db) that have been encrypted, so it cannot deploy changes for them. The database is 2008R2 and I'm using admin credentials.

Using the same databases and credentials SQL Compare UI is able to view, decrypt and script changes to those SPs.

Is there an option I'm missing?

Thanks,
Jacques

Jacquers

Something else I noticed, even though difference.Selected = true; is set, it doesn't script the object (it's not included in the change script). Since the encrypted SP is in the Target db, it would be nice if it could just take the source db's SP and add 'with encryption' to it.

I assume if my 1st issue of not being able to decrypt the SP in the target db is solved, then this one will be solved as well.

Brian Donahue

For starters, you should use the option to decrypt in all of the places that take Options as an argument.Then also append Options.AddWithEncryption to put encryption on all stored procedures.

Options o = Options.Default | Options.DecryptPost2kEncryptedObjects | Options.AddWithEncryption;

Jacquers

Thanks, I have tried those options. The documentation says the default options already include DecryptPost2kEncryptedObjects, but I added it explicitly and it makes no difference, I still get "Text was encrypted"

Adding AddWithEncryption isn't viable because that will encrypt all the stored procedures, not just the ones that are already encrypted.

I find it very strange and frustrating that the ui can decrypt successfully, but the sdk can't.

Brian Donahue

Also make sure you are consistently applying the options the same way to every function that accepts options.

Make sue the account connecting has db owner rights so it can dump the pages it needs to decrypt.

Check for exceptions; dumping the pages needs the backup reader DLL and that means you have to compile for x86 or it won't work. But you should get an error or badimageformatexception if that's the problem.

Jacquers

Both are live databases. I'm using the sa account. Same database and login details as used in the ui. Still no joy

Brian Donahue

I'd probably need all the code to say anything more.

Jacquers

I'll play around a bit more and then post some code if I don't come right.... thx.

Jacquers

I still haven't been able to get it to decrypt. I have tried various combos of the options without success. The resulting sql is always "text was encrypted"

using (var sourceDb = new Database())
{
var connectionProperties = new ConnectionProperties
{
ServerName = ".\\sqlexpress",
DatabaseName = "TestDB",
UserName = "sa",
Password = "sapassword"
};

var options = Options.Default | Options.DecryptPost2kEncryptedObjects | Options.AddWithEncryption;
sourceDb.Register(connectionProperties, options);

var sql = new Work().ScriptObject(sourceDb.StoredProcedures[0], Options.Default).ToString();
}


And when calling Comparewith, the result from the messages / warnings is:

The procedure [dbo].[spTest] cannot be decrypted in one or more of the datasources. Its contents cannot be compared and it cannot be synchronized.
The procedure [dbo].[spTest] cannot be decrypted in one or more of the datasources. Its dependencies cannot be exactly identified. The script may fail.

Brian Donahue

You are calling ScriptObject with Options.Default and not the options you have set for the comparison. That could do it.

Jacquers

Hi,

I changed it to use the options you suggested and still no decryption....

I'd like to get it working, but for now I'm getting the create script for the source and manually updating the target if the target is encrypted.

Regards,
Jacques

Brian Donahue

Ok, there is an idea - it's decrypting the source right? What is the difference in the way you register the source vs the way you register the target? And PS - you do not need Options.AddWithEncryption. I suggested that about 10 posts ago because you mentioned the requirement was to encrypt all stored procedures on deployment - or at least that's the way I understood it.

Jacquers

It's not decrypting source or target.

Brian Donahue

Sorry, at this point I need all the code and the database backups and about a day to figure this out. You can reply to your support ticket with the needed info.