Exception When Registering a Database

Automate and integrate using the SQL comparison API

Moderator: Chris Auckland

Exception When Registering a Database

Postby wkhazzard » Wed Mar 09, 2011 7:57 pm

I am getting an ArgumentOutOfRangeException when I register a database to create a snapshot. The code looks like this:

Code: Select all
            using (Database stagingDB = new Database())
            {
                var connectionProperties = new ConnectionProperties(
                    args[1], args[2]);
                try
                {
                    Console.WriteLine("Registering database " + connectionProperties.DatabaseName);
                    stagingDB.Register(connectionProperties,
                        Options.CaseSensitiveObjectDefinition | Options.DecryptPost2kEncryptedObjects);
                    // Save a snapshot of the database to WidgetStaging.snp

                    string snapshotFile = String.Format(
                        @"{0}\\{1}-{2}.snp",
                        args[0], args[1].Replace('\\\\', '_'), args[2].Replace(' ', '_'));

                    Console.WriteLine("Saving snapshot file \\"{0}\\"...", snapshotFile);
                    stagingDB.SaveToDisk(snapshotFile);
                }
                catch (SqlException e)
                {
                    Console.WriteLine("{0} occurred returning message '{1}'.",
                        e.GetType().Name, e.Message);
                    Console.WriteLine("Could not connect to database '{0}' on server '{1}'.",
                        connectionProperties.DatabaseName, connectionProperties.ServerName);
                    return -2;
                }
                return 0;
            }


And here's the exception:

System.ArgumentOutOfRangeException was unhandled
Message=Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
Source=mscorlib
ParamName=index
StackTrace:
at System.Collections.BitArray.Set(Int32 index, Boolean value)
at System.Collections.BitArray.set_Item(Int32 index, Boolean value)
at RedGate.SQLCompare.Engine.DatabaseLevelPermissions.set_Item(DatabaseLevelPermissionAction permission, PermissionType value)
at o.H()
at o.F()
at o.a(n )
at RedGate.SQLCompare.Engine.Database.Register(ConnectionProperties connectionProperties, Options options)
at SaveDatabaseSnapshot.Program.Main(String[] args) in C:\\Projects\\Utilities\\SaveDatabaseSnapshot\\Program.cs:line 24
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:


The exception is occuring in the .Register call. This code works fine one half a dozen other databases and I have no way to step into your code so I can't quite figure out why this one particular database is causing this error. Any ideas?

Thanks,
Kevin Hazzard, C# MVP & Friend of Red Gate
http://devjourney.com
wkhazzard
 
Posts: 12
Joined: Tue Mar 08, 2011 4:28 pm
Location: Mid-Atlantic USA

Postby james.billings » Thu Mar 10, 2011 8:34 pm

Thanks for your post. I'm not immediately sure - Does the database in question differ from the others in any way? (Such as compatibility level etc?)

Have you tried connecting to it using the SQL Compare program to see if that works ok?
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby wkhazzard » Fri Mar 11, 2011 2:12 pm

Yes, it does work in the SQL Compare tool. And I'm using the two switches:

Options.CaseSensitiveObjectDefinition | Options.DecryptPost2kEncryptedObjects

in my code that appear to be set in the stand-alone comparison tool. My code still crashes without those options, by the way. This database is identical in schema, collation, etc. to 5 others that I am snapshotting without issue.

Digging in a bit, I did find one difference between this database and the rest. The database I'm having trouble with shows "Guest account enabled for: ..." checked on the mapping page of my login. All the others do not show that check mark. I am a sysadmin on that server so I didn't think that would matter. And as I said before, using the SQL Compare tool, I am able to make a snapshot of the troublesome database.

I can't clear the "Guest account enabled for: ..." checkbox. And the help on MSDN says to disable the guest account on the status page of the login properties for the guest account. But I don't have a "guest" user in my server logins. Strange. Any advice there?

Thanks,
Kevin Hazzard, C# MVP & Friend of Red Gate
http://devjourney.com
wkhazzard
 
Posts: 12
Joined: Tue Mar 08, 2011 4:28 pm
Location: Mid-Atlantic USA

Postby james.billings » Fri Mar 11, 2011 2:30 pm

I don't have one either; although I don't have the box ticked in my databases.

I guess potentially this could affect things if it's trying to read some information about the guest account as the database has it enabled, but this doesn't work as it doesn't really exist?

Perhaps this link will be of use:
http://www.mssqltips.com/tip.asp?tip=1172

The other thing would be to try a profiler trace when attempting to connect and seeing what the last query that ran was; this may give a little more information about the area that's causing trouble.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby wkhazzard » Sat Mar 12, 2011 6:11 pm

Hello James,

I ran the profiler as you recommended. Now I'm almost certain that it's the guest account is causing the issue. The last thing the shown in the profiler before the crash is a query that gets user and object permissions. I've attached it below along with two sets of results that show the successful \"non-guest\" access and the failing \"guest access results. I've snipped the local users from the results in both cases to hide some details about my database from public eyes. But the most interesting part of the results is that none of the schemas have real data in them in the guest case. I suppose this is entirely my problem now. I must figure out how to remove the guest access from the database. The link you sent and the Microsoft help allude to disabling the guest login but I don't have a guest login showing in the server's security configuration. In a future version of the SDK's Database.Register method, it may be useful to determine if guest access has been enabled and report on that when exceptions occur.

Thanks,

Kevin

Here's the code that was executing and the two result sets:

Code: Select all
SELECT CAST(0 AS INT) AS ColumnPermissionID,
sp.grantee_principal_id as uid,
su.name AS UserName,
OBJECT_NAME(sp.major_id) AS ObjectName,
SCHEMA_NAME(so.schema_id) AS ObjectOwner,
1 AS class, sp.type, sp.state,
so.type AS ObjectType,
CONVERT(int, null) as columns
FROM sys.database_permissions  AS sp WITH (NOLOCK)
INNER JOIN sys.sysusers su WITH (NOLOCK) ON su.uid=sp.grantee_principal_id
INNER JOIN sys.objects so WITH (NOLOCK) ON so.object_id=sp.major_id
WHERE sp.major_id>0 AND sp.class = 1 AND sp.minor_id = 0 AND (sp.type in ('IN','DL','EX')
OR CHARINDEX(sp.type, 'CRFNCRTBCRDBCRVWCRPRBADBCRDFBALOCRRU')%4 > 0)
AND so.schema_id IS NOT NULL
GROUP BY sp.major_id, sp.grantee_principal_id, sp.grantor_principal_id, sp.type, sp.state, so.type, su.name, so.schema_id

UNION ALL
SELECT
CAST((row_number() over (order by sp.major_id, sp.grantee_principal_id, sp.grantor_principal_id, sp.type, spParent.state, so.type,
su.name, so.schema_id, sp.type, sp.minor_id)
- row_number() over (partition by sp.major_id, sp.grantee_principal_id, sp.grantor_principal_id, sp.type, spParent.state, so.type,
su.name, so.schema_id, sp.type order by sp.minor_id)
+ 1 ) AS INT) AS ColumnPermissionID,
sp.grantee_principal_id AS uid,
su.name AS UserName,
OBJECT_NAME(sp.major_id) AS ObjectName,
SCHEMA_NAME(so.schema_id) AS ObjectOwner,
1 AS class, sp.type, spParent.state,
so.type AS ObjectType,
CASE spParent.state WHEN 'R' THEN CONVERT(int, null)
ELSE
    sp.minor_id
END AS columns
FROM sys.database_permissions AS sp WITH (NOLOCK)
INNER JOIN sys.database_permissions AS spParent
    ON spParent.class = 1 AND spParent.major_id=sp.major_id AND spParent.minor_id = 0 AND
    spParent.grantee_principal_id = sp.grantee_principal_id AND
    spParent.grantor_principal_id = sp.grantor_principal_id AND
    spParent.type = sp.type AND
    spParent.state <> 'R'
INNER JOIN sys.sysusers su WITH (NOLOCK) ON su.uid=sp.grantee_principal_id
INNER JOIN sys.objects so WITH (NOLOCK)  ON so.object_id=sp.major_id
WHERE sp.major_id>0 AND sp.class = 1 AND sp.type IN ('RF', 'SL', 'UP','AL','CL','RC','VW','TO')
AND so.schema_id IS NOT NULL

UNION ALL
SELECT
CAST((row_number() over (partition by sp.major_id, sp.grantee_principal_id, sp.grantor_principal_id, sp.type, so.type, sp.state order by sp.minor_id)
- row_number() over (order by sp.major_id, sp.grantee_principal_id, sp.grantor_principal_id, sp.type, so.type, sp.state, sp.minor_id)
- 1) AS INT) AS ColumnPermissionID,
sp.grantee_principal_id AS uid,
su.name AS UserName,
OBJECT_NAME(sp.major_id) AS ObjectName,
SCHEMA_NAME(so.schema_id) AS ObjectOwner,
1 AS class, sp.type, sp.state,
so.type AS ObjectType,
minor_id AS columns
FROM sys.database_permissions AS sp WITH (NOLOCK)
INNER JOIN sys.sysusers su WITH (NOLOCK) ON su.uid=sp.grantee_principal_id
INNER JOIN sys.objects so WITH (NOLOCK)  ON so.object_id=sp.major_id
WHERE sp.major_id>0 AND class = 1 AND minor_id <> 0 AND state <> 'R'
AND so.schema_id IS NOT NULL

UNION ALL
SELECT
CAST(0 AS INT) AS ColumnPermissionID,
sp.grantee_principal_id AS uid,
su.name AS UserName,
CASE sp.class
    WHEN 3 THEN (SELECT TOP 1 sch2.name FROM sys.schemas sch2 WITH (NOLOCK) WHERE sch2.schema_id = sp.major_id)
    WHEN 4 THEN (SELECT TOP 1 sp2.name FROM sys.database_principals sp2 WITH (NOLOCK) WHERE sp2.principal_id = sp.major_id)
    WHEN 5 THEN (SELECT TOP 1 sa2.name FROM sys.assemblies sa2 WITH (NOLOCK) WHERE sa2.assembly_id = sp.major_id)
    WHEN 6 THEN (SELECT TOP 1 st2.name FROM sys.types st2 WITH (NOLOCK) WHERE st2.user_type_id = sp.major_id)
    WHEN 10 THEN (SELECT TOP 1 sx2.name FROM sys.xml_schema_collections sx2 WITH (NOLOCK) WHERE sx2.xml_collection_id = sp.major_id)
    WHEN 15 THEN (SELECT TOP 1 smt2.name FROM sys.service_message_types smt2 WITH (NOLOCK) WHERE smt2.message_type_id = sp.major_id) COLLATE database_default
    WHEN 16 THEN (SELECT TOP 1 ssc2.name FROM sys.service_contracts ssc2 WITH (NOLOCK) WHERE ssc2.service_contract_id = sp.major_id)
    WHEN 17 THEN (SELECT TOP 1 ss2.name FROM sys.services ss2 WITH (NOLOCK) WHERE ss2.service_id = sp.major_id)
    WHEN 18 THEN (SELECT TOP 1 srs2.name FROM sys.remote_service_bindings srs2 WITH (NOLOCK) WHERE srs2.remote_service_binding_id = sp.major_id)
    WHEN 19 THEN (SELECT TOP 1 sr2.name FROM sys.routes sr2 WITH (NOLOCK) WHERE sr2.route_id = sp.major_id)
    WHEN 23 THEN (SELECT TOP 1 sft2.name FROM sys.fulltext_catalogs sft2 WITH (NOLOCK) WHERE sft2.fulltext_catalog_id = sp.major_id)   
    WHEN 24 THEN (SELECT TOP 1 ssk2.name FROM sys.symmetric_keys ssk2 WITH (NOLOCK) WHERE ssk2.symmetric_key_id = sp.major_id)
    WHEN 26 THEN (SELECT TOP 1 sak2.name FROM sys.asymmetric_keys sak2 WITH (NOLOCK) WHERE sak2.asymmetric_key_id = sp.major_id)
    WHEN 25 THEN (SELECT TOP 1 sc2.name FROM sys.certificates sc2 WITH (NOLOCK) WHERE sc2.certificate_id = sp.major_id)
    ELSE N''
END AS ObjectName,
CASE sp.class
    WHEN 10 THEN SCHEMA_NAME(( SELECT TOP 1 sx2.schema_id FROM sys.xml_schema_collections sx2 WITH (NOLOCK) WHERE sx2.xml_collection_id = sp.major_id))
    WHEN 6 THEN SCHEMA_NAME((SELECT TOP 1 st2.schema_id FROM sys.types st2 WITH (NOLOCK) WHERE st2.user_type_id = sp.major_id))
ELSE
USER_NAME(CASE sp.class
    WHEN 3 THEN (SELECT TOP 1 sch2.principal_id FROM sys.schemas sch2 WITH (NOLOCK) WHERE sch2.schema_id = sp.major_id)
    WHEN 4 THEN (SELECT TOP 1 sp2.owning_principal_id FROM sys.database_principals sp2 WITH (NOLOCK) WHERE sp2.principal_id = sp.major_id)
    WHEN 5 THEN (SELECT TOP 1 sa2.principal_id FROM sys.assemblies sa2 WITH (NOLOCK) WHERE sa2.assembly_id = sp.major_id)
    WHEN 15 THEN (SELECT TOP 1 smt2.principal_id FROM sys.service_message_types smt2 WITH (NOLOCK) WHERE smt2.message_type_id = sp.major_id)
    WHEN 16 THEN (SELECT TOP 1 ssc2.principal_id FROM sys.service_contracts ssc2 WITH (NOLOCK) WHERE ssc2.service_contract_id = sp.major_id)
    WHEN 17 THEN (SELECT TOP 1 ss2.principal_id FROM sys.services ss2 WITH (NOLOCK) WHERE ss2.service_id = sp.major_id)
    WHEN 18 THEN (SELECT TOP 1 srs2.principal_id FROM sys.remote_service_bindings srs2 WITH (NOLOCK) WHERE srs2.remote_service_binding_id = sp.major_id)
    WHEN 19 THEN (SELECT TOP 1 sr2.principal_id FROM sys.routes sr2 WITH (NOLOCK) WHERE sr2.route_id = sp.major_id)
    WHEN 23 THEN (SELECT TOP 1 sft2.principal_id FROM sys.fulltext_catalogs sft2 WITH (NOLOCK) WHERE sft2.fulltext_catalog_id = sp.major_id)
    WHEN 24 THEN (SELECT TOP 1 ssk2.principal_id FROM sys.symmetric_keys ssk2 WITH (NOLOCK) WHERE ssk2.symmetric_key_id = sp.major_id)
    WHEN 26 THEN (SELECT TOP 1 sak2.principal_id FROM sys.asymmetric_keys sak2 WITH (NOLOCK) WHERE sak2.asymmetric_key_id = sp.major_id)
    WHEN 25 THEN (SELECT TOP 1 sc2.principal_id FROM sys.certificates sc2 WITH (NOLOCK) WHERE sc2.certificate_id = sp.major_id)
    ELSE null
END)
END AS ObjectOwner,
sp.class, sp.type, sp.state,
CONVERT(CHAR(2), NULL) AS ObjectType,
CONVERT(int, null) AS columns
FROM sys.database_permissions AS sp WITH (NOLOCK)
INNER JOIN sys.sysusers su WITH (NOLOCK) ON su.uid=sp.grantee_principal_id
WHERE major_id>=0 AND sp.class <> 1


/* successful \"non-guest\" results
ColumnPermissionID uid UserName ObjectName ObjectOwner class type state ObjectType columns
0 0 public sp_helpdiagrams dbo 1 EX G P NULL
0 2 guest sp_helpdiagrams dbo 1 EX D P NULL
0 0 public sp_helpdiagramdefinition dbo 1 EX G P NULL
0 2 guest sp_helpdiagramdefinition dbo 1 EX D P NULL
0 0 public sp_creatediagram dbo 1 EX G P NULL
0 2 guest sp_creatediagram dbo 1 EX D P NULL
0 0 public sp_renamediagram dbo 1 EX G P NULL
0 2 guest sp_renamediagram dbo 1 EX D P NULL
0 0 public sp_alterdiagram dbo 1 EX G P NULL
0 2 guest sp_alterdiagram dbo 1 EX D P NULL
0 0 public sp_dropdiagram dbo 1 EX G P NULL
0 2 guest sp_dropdiagram dbo 1 EX D P NULL
0 0 public fn_diagramobjects dbo 1 EX G FN NULL
0 2 guest fn_diagramobjects dbo 1 EX D FN NULL
0 1 dbo NULL 0 CO G NULL NULL
<snip/>
*/

/* errored \"as guest\" results
ColumnPermissionID uid UserName ObjectName ObjectOwner class type state ObjectType columns
0 0 public NULL 0 AL G NULL NULL
0 0 public NULL 0 ALAK G NULL NULL
0 0 public NULL 0 ALAR G NULL NULL
0 0 public NULL 0 ALAS G NULL NULL
0 0 public NULL 0 ALCF G NULL NULL
0 0 public NULL 0 ALDA G NULL NULL
0 0 public NULL 0 ALDS G NULL NULL
0 0 public NULL 0 ALED G NULL NULL
0 0 public NULL 0 ALFT G NULL NULL
0 0 public NULL 0 ALMT G NULL NULL
0 0 public NULL 0 ALRL G NULL NULL
0 0 public NULL 0 ALRT G NULL NULL
0 0 public NULL 0 ALSB G NULL NULL
0 0 public NULL 0 ALSC G NULL NULL
0 0 public NULL 0 ALSK G NULL NULL
0 0 public NULL 0 ALSM G NULL NULL
0 0 public NULL 0 ALSV G NULL NULL
0 0 public NULL 0 ALTG G NULL NULL
0 0 public NULL 0 ALUS G NULL NULL
0 0 public NULL 0 AUTH G NULL NULL
0 0 public NULL 0 BADB G NULL NULL
0 0 public NULL 0 BALO G NULL NULL
0 0 public NULL 0 CORP G NULL NULL
0 0 public NULL 0 CP G NULL NULL
0 1 dbo NULL 0 CO G NULL NULL
0 2 guest NULL 0 AL G NULL NULL
0 2 guest NULL 0 ALAK G NULL NULL
0 2 guest NULL 0 ALAR G NULL NULL
0 2 guest NULL 0 ALAS G NULL NULL
0 2 guest NULL 0 ALCF G NULL NULL
0 2 guest NULL 0 ALDA G NULL NULL
0 2 guest NULL 0 ALDS G NULL NULL
0 2 guest NULL 0 ALED G NULL NULL
0 2 guest NULL 0 ALFT G NULL NULL
0 2 guest NULL 0 ALMT G NULL NULL
0 2 guest NULL 0 ALRL G NULL NULL
0 2 guest NULL 0 ALRT G NULL NULL
0 2 guest NULL 0 ALSB G NULL NULL
0 2 guest NULL 0 ALSC G NULL NULL
0 2 guest NULL 0 ALSK G NULL NULL
0 2 guest NULL 0 ALSM G NULL NULL
0 2 guest NULL 0 ALSV G NULL NULL
0 2 guest NULL 0 ALTG G NULL NULL
0 2 guest NULL 0 ALUS G NULL NULL
0 2 guest NULL 0 AUTH G NULL NULL
0 2 guest NULL 0 BADB G NULL NULL
0 2 guest NULL 0 BALO G NULL NULL
0 2 guest NULL 0 CL G NULL NULL
0 2 guest NULL 0 CO G NULL NULL
0 2 guest NULL 0 CORP G NULL NULL
0 2 guest NULL 0 CP G NULL NULL
0 2 guest NULL 0 CRAG G NULL NULL
0 2 guest NULL 0 CRAK G NULL NULL
0 2 guest NULL 0 CRAS G NULL NULL
0 2 guest NULL 0 CRCF G NULL NULL
0 2 guest NULL 0 CRDF G NULL NULL
0 2 guest NULL 0 CRED G NULL NULL
0 2 guest NULL 0 CRFN G NULL NULL
0 2 guest NULL 0 CRFT G NULL NULL
0 2 guest NULL 0 CRMT G NULL NULL
0 2 guest NULL 0 CRPR G NULL NULL
0 2 guest NULL 0 CRQU G NULL NULL
0 2 guest NULL 0 CRRL G NULL NULL
0 2 guest NULL 0 CRRT G NULL NULL
0 2 guest NULL 0 CRRU G NULL NULL
0 2 guest NULL 0 CRSB G NULL NULL
0 2 guest NULL 0 CRSC G NULL NULL
0 2 guest NULL 0 CRSK G NULL NULL
0 2 guest NULL 0 CRSM G NULL NULL
0 2 guest NULL 0 CRSN G NULL NULL
0 2 guest NULL 0 CRSV G NULL NULL
0 2 guest NULL 0 CRTB G NULL NULL
0 2 guest NULL 0 CRTY G NULL NULL
0 2 guest NULL 0 CRVW G NULL NULL
0 2 guest NULL 0 CRXS G NULL NULL
0 2 guest NULL 0 DL G NULL NULL
0 2 guest NULL 0 EX G NULL NULL
0 2 guest NULL 0 IN G NULL NULL
0 2 guest NULL 0 RF G NULL NULL
0 2 guest NULL 0 SL G NULL NULL
0 2 guest NULL 0 SPLN G NULL NULL
0 2 guest NULL 0 SUQN G NULL NULL
0 2 guest NULL 0 TO G NULL NULL
0 2 guest NULL 0 UP G NULL NULL
0 2 guest NULL 0 VW G NULL NULL
0 2 guest NULL 0 VWDS G NULL NULL
<snip/>
*/
wkhazzard
 
Posts: 12
Joined: Tue Mar 08, 2011 4:28 pm
Location: Mid-Atlantic USA

Postby james.billings » Sun Mar 13, 2011 3:40 pm

It does look like the guest thing is likely to be the culprit - I'll have a bit more of a look in the office tomorrow to see if I can spot anything useful, but do post back with anything else you come up with!
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.


Return to SQL Comparison SDK Previous Versions

Who is online

Users browsing this forum: No registered users and 1 guest