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

script encrypted database object

revenreven Posts: 6
hi im trying to script a encrypted database object in sql server 2008 r2 but all I am getting back is
-- Text was encrypted
GO

im running it under "sa" and using the following code (note RG is an alias for the RedGate.SQLCompare.Engine name space to avoid a namespace conflict with the rest of my code).
            RG.Database db = new RG.Database();
            try
            {
                RG.ConnectionProperties connectionProperties = new RG.ConnectionProperties(DbHelper.DbServer, DbHelper.DbDatabase, DbHelper.DbUser, DbHelper.DbPassword);
                RG.Options options = RG.Options.Default | RG.Options.DecryptPost2kEncryptedObjects | RG.Options.IgnorePermissions | RG.Options.IgnoreWhiteSpace | RG.Options.IgnoreUsers;
                db.Register(connectionProperties, options);
                RG.Work work = new RG.Work();
                RG.Regions regions = null;
                switch(Type)
                {
                    case DbObjectType.StoredProcedure:
                        regions = work.ScriptObject(db.StoredProcedures[ObjectName], options);
                        break;
                    case DbObjectType.Table:
                        regions = work.ScriptObject(db.Tables[ObjectName], options);
                        break;
                    case DbObjectType.Function:
                        regions = work.ScriptObject(db.Functions[ObjectName], options);
                        break;
                    case DbObjectType.Trigger:
                        regions = work.ScriptObject(db.DdlTriggers[ObjectName], options);
                        break;
                }
                string script = regions.ToString();
                return script;
            }
            catch (Exception ex)
            {
                Trace.WriteLine(ex.Message + Environment.NewLine + ex.StackTrace);
                return null;
            }
            finally
            {
            }

Comments

  • Options
    IIRC, you need to add a reference to the BackupReader DLL for decryption to work. This also requires your app to be specifically built as x86 as opposed to "Any CPU".
    Try that, and post back if the problem persists!
    Systems Software Engineer

    Redgate Software

  • Options
    IIRC, you need to add a reference to the BackupReader DLL for decryption to work. This also requires your app to be specifically built as x86 as opposed to "Any CPU".
    Try that, and post back if the problem persists!

    both of those are done, the BackupReader dll reference is from the same directory/version as the rest of the dlls. even using the same project and changing it from "Tables[0]" to a encrypted stored proc doesn't decrypt it.
  • Options
    OK - you probably don't need to include the decrypt as an option as it's included in default, but I don't think that would actually cause the problem.

    What version of the Compare DLL's are you using? Just the ones supplied with the SDK? If so, you might want to grab Compare 8.50 from here: http://www.red-gate.com/supportcenter/G ... rsions.htm then try referencing the DLLs from that.
    Systems Software Engineer

    Redgate Software

  • Options
    hi, I was using the DLLs from the sample solution, which were version 7.x, I am now using the DLLs from the program files which are 9.x. But over the weekend my trial expired and cannot test this. Is there anyway to get an extended trial?
  • Options
    If you email in, we should be able to give you a trial extension key to extend it.
    Systems Software Engineer

    Redgate Software

  • Options
    hi,

    Received extended trial key, using the DLLs
    - C:\Program Files (x86)\Red Gate\SQL Compare 9\RedGate.Licensing.Client.dll (v2.50.30.1)
    - C:\Program Files (x86)\Red Gate\SQL Compare 9\RedGate.Shared.SQL.dll (v8.50.30.10)
    - C:\Program Files (x86)\Red Gate\SQL Compare 9\RedGate.Shared.Utils.dll (v8.50.30.10)
    - C:\Program Files (x86)\Red Gate\SQL Compare 9\RedGate.SQLCompare.ASTParser.dll (v9.0.0.51)
    - C:\Program Files (x86)\Red Gate\SQL Compare 9\RedGate.SQLCompare.Engine.dll (v9.0.0.51)
    - C:\Program Files (x86)\Red Gate\SQL Compare 9\RedGate.SQLCompare.Rewriter.dll (v9.0.0.51)

    and using the following code under the "sa" account (database is in a VM and code is on my local box)
                RG.Database db = new RG.Database();
                try
                {
                    RG.ConnectionProperties connectionProperties = new RG.ConnectionProperties(Server, Database, User, Password);
                    RG.Options options = RG.Options.Default | RG.Options.DecryptPost2kEncryptedObjects | RG.Options.IgnorePermissions | RG.Options.IgnoreWhiteSpace | RG.Options.IgnoreUsers;
                    db.Register(connectionProperties, options);
                    RG.Work work = new RG.Work();
                    RG.Regions regions = null;
                    switch (Type)
                    {
                        case DbObjectType.StoredProcedure:
                            foreach (var storedProc in db.StoredProcedures)
                            {
                                if (storedProc.Name == ObjectName || storedProc.Name.Contains(String.Format("[{0}]", ObjectName)))
                                {
                                    regions = work.ScriptObject(storedProc, options);
                                    break;
                                }
                            }
                            break;
                        case DbObjectType.Table:
                            regions = work.ScriptObject(db.Tables[ObjectName], options);
                            break;
                        case DbObjectType.Function:
                            regions = work.ScriptObject(db.Functions[ObjectName], options);
                            break;
                        case DbObjectType.Trigger:
                            regions = work.ScriptObject(db.DdlTriggers[ObjectName], options);
                            break;
                    }
                    if (regions == null)
                        throw new Exception("Failed to find object: " + ObjectName);
                    string script = regions.ToString();
                    return script;
                }
                catch (Exception ex)
                {
                    Trace.WriteLine(ex.Message + Environment.NewLine + ex.StackTrace);
                    return null;
                }
                finally
                {
                }
    

    and im still getting the result
    -- Text was encrypted
    GO

    what am I doing wrong?
  • Options
    In your latest message you didn't mention the BackupReader DLL.

    I got the same result as you initially, but the following should decrypt the objects:

    - add a reference to "RedGate.BackupReader.dll"
    - add a reference to "System.Data.SQLite.dll" (the backup reader needs this)
    - ensure you compile to "X86" rather than "AnyCPU"
    Systems Software Engineer

    Redgate Software

  • Options
    added the following references

    - C:\Program Files (x86)\Red Gate\SQL Compare 9\RedGate.BackupReader.dll (v1.50.30.9)
    - C:\Program Files (x86)\Red Gate\SQL Compare 9\SQLite\System.Data.SQLite.DLL (v1.0.54.0)

    platform target is x86.

    still getting text is encrypted.
Sign In or Register to comment.