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 support center
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: 844
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: 844
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: 844
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: 844
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