Exception When Registering a Database

wkhazzardwkhazzard Posts: 20
I am getting an ArgumentOutOfRangeException when I register a database to create a snapshot. The code looks like this:
            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

Comments

  • James BJames B Posts: 1,124 Silver 4
    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?
    Systems Software Engineer

    Redgate Software

  • 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
  • James BJames B Posts: 1,124 Silver 4
    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.
    Systems Software Engineer

    Redgate Software

  • 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:
    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/>
    */
    Kevin Hazzard, C# MVP & Friend of Red Gate
    http://devjourney.com
  • 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!
    Systems Software Engineer

    Redgate Software

Sign In or Register to comment.