script encrypted database object

revenreven Posts: 6
edited November 7, 2011 2:55PM in SQL Comparison SDK Previous Versions
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
            {
            }
· Share on Twitter

Comments

  • James BJames B Posts: 1,124 Silver 4
    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

    · Share on Twitter
  • revenreven Posts: 6
    james.billings wrote:
    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.
    · Share on Twitter
  • James BJames B Posts: 1,124 Silver 4
    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

    · Share on Twitter
  • revenreven Posts: 6
    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?
    · Share on Twitter
  • James BJames B Posts: 1,124 Silver 4
    If you email in, we should be able to give you a trial extension key to extend it.
    Systems Software Engineer

    Redgate Software

    · Share on Twitter
  • revenreven Posts: 6
    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?
    · Share on Twitter
  • James BJames B Posts: 1,124 Silver 4
    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

    · Share on Twitter
  • revenreven Posts: 6
    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.
    · Share on Twitter
Sign In or Register to comment.