Red Gate forums :: View topic - Exception When Registering a Database
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

Exception When Registering a Database

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



Joined: 08 Mar 2011
Posts: 12
Location: Mid-Atlantic USA

PostPosted: Wed Mar 09, 2011 7:57 pm    Post subject: Exception When Registering a Database Reply with quote

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

Code:

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



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

PostPosted: Thu Mar 10, 2011 8:34 pm    Post subject: Reply with quote

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



Joined: 08 Mar 2011
Posts: 12
Location: Mid-Atlantic USA

PostPosted: Fri Mar 11, 2011 2:12 pm    Post subject: Reply with quote

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



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

PostPosted: Fri Mar 11, 2011 2:30 pm    Post subject: Reply with quote

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



Joined: 08 Mar 2011
Posts: 12
Location: Mid-Atlantic USA

PostPosted: Sat Mar 12, 2011 6:11 pm    Post subject: Reply with quote

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



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

PostPosted: Sun Mar 13, 2011 3:40 pm    Post subject: Reply with quote

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