Red Gate forums :: View topic - Unable to decrypt Stored Procedure
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK 10
SQL Comparison SDK 10 forum

Unable to decrypt Stored Procedure

Search in SQL Comparison SDK 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
Jacquers



Joined: 25 Oct 2013
Posts: 9

PostPosted: Fri Oct 25, 2013 7:45 pm    Post subject: Unable to decrypt Stored Procedure Reply with quote

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
Back to top
View user's profile Send private message
Jacquers



Joined: 25 Oct 2013
Posts: 9

PostPosted: Fri Oct 25, 2013 7:49 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6588

PostPosted: Tue Oct 29, 2013 5:47 pm    Post subject: Reply with quote

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:
Options o = Options.Default | Options.DecryptPost2kEncryptedObjects | Options.AddWithEncryption;
Back to top
View user's profile Send private message
Jacquers



Joined: 25 Oct 2013
Posts: 9

PostPosted: Tue Oct 29, 2013 6:54 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6588

PostPosted: Thu Oct 31, 2013 12:36 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Jacquers



Joined: 25 Oct 2013
Posts: 9

PostPosted: Thu Oct 31, 2013 12:40 pm    Post subject: Reply with quote

Both are live databases. I'm using the sa account. Same database and login details as used in the ui. Still no joy Sad
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6588

PostPosted: Thu Oct 31, 2013 4:11 pm    Post subject: Reply with quote

I'd probably need all the code to say anything more.
Back to top
View user's profile Send private message
Jacquers



Joined: 25 Oct 2013
Posts: 9

PostPosted: Thu Oct 31, 2013 4:18 pm    Post subject: Reply with quote

I'll play around a bit more and then post some code if I don't come right.... thx.
Back to top
View user's profile Send private message
Jacquers



Joined: 25 Oct 2013
Posts: 9

PostPosted: Fri Nov 01, 2013 11:30 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6588

PostPosted: Fri Nov 01, 2013 2:14 pm    Post subject: Reply with quote

You are calling ScriptObject with Options.Default and not the options you have set for the comparison. That could do it.
Back to top
View user's profile Send private message
Jacquers



Joined: 25 Oct 2013
Posts: 9

PostPosted: Fri Nov 01, 2013 2:20 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6588

PostPosted: Mon Nov 04, 2013 4:26 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Jacquers



Joined: 25 Oct 2013
Posts: 9

PostPosted: Mon Nov 04, 2013 5:32 pm    Post subject: Reply with quote

It's not decrypting source or target.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6588

PostPosted: Wed Nov 06, 2013 1:59 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group