Command line vs gui snapshot creation

delbert

I came across something odd, and I'm not sure what's causing it.

I have a script that uses the command line to generate a snapshot of a database:

. $sqlCompare /server1:$sqlServer /database1:$sqlDb /username1:$sqlUser /password1:$sqlPass /makesnapshot:$OutputFile /force /quiet

If I then compare that snapshot to the database (the very same one) it sees differences for two tables. The snapshot create script shows this:

[IsActive] [bit] NOT NULL CONSTRAINT [DF_User_IsActive] DEFAULT -- Text was encrypted

and the database script shows:

[IsActive] [bit] NOT NULL CONSTRAINT [DF_User_IsActive] DEFAULT ((1))

If I try to sync the live db to the snapshot, I get sql syntax errors. The odd thing is if I save a snapshot using the gui, this doesn't happen. The snapshot and database don't show any differences. It's only if I create the snapshot with command line tool.

How can I resolve this?

Thanks,

--
Dan

Brian Donahue

Hi Dan,

When you create the snapshot using the GUI and again using the command-line, are you using the same user? A non-administrative user doesn't have the rights necessary to decrypt encrypted objects on SQL Server 2000. On SQL Server 2005, it is not even possible to decrypt encrypted objects; this could also be an issue.

delbert

This is a 2005 database, and as far as I'm aware, none of our objects are encrypted.

Now that you mention it, I am using my windows credentials when using the gui and more limited credentials when using the command line tool.

I'll use the same login information in both and see what I get.

delbert

If I change the gui credentials to the same I use in the command line, I do get the same output. However, something is still not right because I get this in both:

CREATE TABLE [dbo].[User]
(
... Some Other Column definitions...
[IsActive] [bit] NOT NULL CONSTRAINT [DF_User_IsActive] DEFAULT -- Text was encrypted
)

As I said, if I'm using administrative credentials, I see the actual default value. This is a 2005 database, so I can't imagine there's an issue with the data actually being encrypted.

Brian Donahue

Hello Delbert,

Exactly. SQL Compare can't decrypt encrypted objects on SQL Server 2005 at all. Microsoft had closed up the security hole that allowed us to do this in SQL Server 2000.