Unable to decrypt Stored Procedure

Automate and integrate using the SQL comparison API

Unable to decrypt Stored Procedure

Postby Jacquers » Fri Oct 25, 2013 7:45 pm

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
 
Posts: 9
Joined: Fri Oct 25, 2013 7:40 pm

Postby Jacquers » Fri Oct 25, 2013 7:49 pm

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.
Jacquers
 
Posts: 9
Joined: Fri Oct 25, 2013 7:40 pm

Postby Brian Donahue » Tue Oct 29, 2013 5:47 pm

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.
Code: Select all
Options o = Options.Default | Options.DecryptPost2kEncryptedObjects | Options.AddWithEncryption;
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby Jacquers » Tue Oct 29, 2013 6:54 pm

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.
Jacquers
 
Posts: 9
Joined: Fri Oct 25, 2013 7:40 pm

Postby Brian Donahue » Thu Oct 31, 2013 12:36 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby Jacquers » Thu Oct 31, 2013 12:40 pm

Both are live databases. I'm using the sa account. Same database and login details as used in the ui. Still no joy :(
Jacquers
 
Posts: 9
Joined: Fri Oct 25, 2013 7:40 pm

Postby Brian Donahue » Thu Oct 31, 2013 4:11 pm

I'd probably need all the code to say anything more.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby Jacquers » Thu Oct 31, 2013 4:18 pm

I'll play around a bit more and then post some code if I don't come right.... thx.
Jacquers
 
Posts: 9
Joined: Fri Oct 25, 2013 7:40 pm

Postby Jacquers » Fri Nov 01, 2013 11:30 am

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.
Jacquers
 
Posts: 9
Joined: Fri Oct 25, 2013 7:40 pm

Postby Brian Donahue » Fri Nov 01, 2013 2:14 pm

You are calling ScriptObject with Options.Default and not the options you have set for the comparison. That could do it.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby Jacquers » Fri Nov 01, 2013 2:20 pm

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
Jacquers
 
Posts: 9
Joined: Fri Oct 25, 2013 7:40 pm

Postby Brian Donahue » Mon Nov 04, 2013 4:26 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby Jacquers » Mon Nov 04, 2013 5:32 pm

It's not decrypting source or target.
Jacquers
 
Posts: 9
Joined: Fri Oct 25, 2013 7:40 pm

Postby Brian Donahue » Wed Nov 06, 2013 1:59 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Comparison SDK 10

Who is online

Users browsing this forum: No registered users and 0 guests