What are the challenges you face when working across database platforms? Take the survey
Options

Unable to decrypt Stored Procedure

JacquersJacquers Posts: 10
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

Comments

  • Options
    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.
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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;
    
  • Options
    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.
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
  • Options
    Both are live databases. I'm using the sa account. Same database and login details as used in the ui. Still no joy :(
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    I'd probably need all the code to say anything more.
  • Options
    I'll play around a bit more and then post some code if I don't come right.... thx.
  • Options
    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.
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    You are calling ScriptObject with Options.Default and not the options you have set for the comparison. That could do it.
  • Options
    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
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
  • Options
    It's not decrypting source or target.
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
Sign In or Register to comment.