Red Gate forums :: View topic - script encrypted database object
Return to www.red-gate.com RSS Feed Available

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

script encrypted database object

Search in SQL Comparison SDK Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
reven



Joined: 19 Oct 2011
Posts: 5

PostPosted: Wed Oct 19, 2011 10:13 pm    Post subject: script encrypted database object Reply with quote

hi im trying to script a encrypted database object in sql server 2008 r2 but all I am getting back is
Quote:
-- 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:

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



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Fri Oct 21, 2011 8:11 am    Post subject: Reply with quote

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



Joined: 19 Oct 2011
Posts: 5

PostPosted: Fri Oct 21, 2011 11:30 am    Post subject: Re: Reply with quote

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



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Fri Oct 21, 2011 11:41 am    Post subject: Reply with quote

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/GeneralContent?c=all_products%5carticles%5cold_versions.htm then try referencing the DLLs from that.
Back to top
View user's profile Send private message
reven



Joined: 19 Oct 2011
Posts: 5

PostPosted: Wed Oct 26, 2011 12:33 am    Post subject: Reply with quote

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



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Wed Oct 26, 2011 7:37 am    Post subject: Reply with quote

If you email in, we should be able to give you a trial extension key to extend it.
Back to top
View user's profile Send private message
reven



Joined: 19 Oct 2011
Posts: 5

PostPosted: Sun Nov 06, 2011 10:03 pm    Post subject: Reply with quote

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:

            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
Quote:
-- Text was encrypted
GO


what am I doing wrong?
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Mon Nov 07, 2011 3:03 pm    Post subject: Reply with quote

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



Joined: 19 Oct 2011
Posts: 5

PostPosted: Mon Nov 07, 2011 8:55 pm    Post subject: Reply with quote

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