script encrypted database object

Automate and integrate using the SQL comparison API

Moderator: Chris Auckland

script encrypted database object

Postby reven » Wed Oct 19, 2011 10:13 pm

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).

Code: Select all
            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
            {
            }
reven
 
Posts: 5
Joined: Wed Oct 19, 2011 10:09 pm

Postby james.billings » Fri Oct 21, 2011 8:11 am

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!
james.billings
 
Posts: 1146
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Re:

Postby reven » Fri Oct 21, 2011 11:30 am

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.
reven
 
Posts: 5
Joined: Wed Oct 19, 2011 10:09 pm

Postby james.billings » Fri Oct 21, 2011 11:41 am

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.
james.billings
 
Posts: 1146
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby reven » Wed Oct 26, 2011 12:33 am

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?
reven
 
Posts: 5
Joined: Wed Oct 19, 2011 10:09 pm

Postby james.billings » Wed Oct 26, 2011 7:37 am

If you email in, we should be able to give you a trial extension key to extend it.
james.billings
 
Posts: 1146
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby reven » Sun Nov 06, 2011 10:03 pm

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)
Code: Select all
            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?
reven
 
Posts: 5
Joined: Wed Oct 19, 2011 10:09 pm

Postby james.billings » Mon Nov 07, 2011 3:03 pm

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"
james.billings
 
Posts: 1146
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby reven » Mon Nov 07, 2011 8:55 pm

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.
reven
 
Posts: 5
Joined: Wed Oct 19, 2011 10:09 pm


Return to SQL Comparison SDK Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests