Can you browse to http://vs00:8080/VersionControl/V1.0/Integration.asmx ? When you said you were using HTTPS for the TFS server, is that the only protocol supported? If you enable both HTTP and HTTPS access, does it start working again?

It's not in the source, it's in the database.

Hi

I cant browse to the http:// address since I only allow HTTPS connections, if I change it to https:// it works just fine.

I have just changed the addresses in the TFSIntegration/dbo_tbl_Subscriptions table to https:// instead of http.

I'm just waiting for a chance to reboot the server to see if the problem dissaperas.

Although what confuses me is why can Project Admin look at the tasks and create them but not contributors. Shouldn't this problem have affected every user or does administrators have som kind of exception?

EDIT:

I've restarted the server and the error message above was gone, although I receive another error message that I cant find a answer to and I still cant view/create tasks as a contributor.

Error:

Hi

Thanks a lot for the answers, as I wrote above, the problem with it not being able to connect to http:// has been solved, I have missed to change the paths in the db, the other places you've mentioned were already exchanged. I followed the guide from microsoft to convert to HTTPS.

I'll follow your advice and start a new thread regarding the Workitem/SQL problem but the first problem isn't resolved, the one where a contributor doesn't have the right to add/view Workitems, but as soon as i'm a project lead admin I can view items.

Can I activate som kind of errorlogging or debug to trace what the difference might be, and if so where and how.

Really appriciate the help I have received.

The original problem (no rights for contributors, limited list of users) *sounds* like a different Work Item Tracking issue - specifically, there's a process that syncs identities from the "main" store in the TfsIntegration database over to the TfsWorkItemTracking database. If that sync process is failing, then the WIT database doesn't have the same identities (including membership of Contributors) as the integration database, hence the symptoms you're seeing.

I'm hoping to get some time from the developer that wrote that code soon to help diagnose/debug this.

Hi

I understod that so I've turned on logging and trace to try to find the problem.

What I found is that when I'm an project admin the query goes thru and I receive workitems, when I'm a user it just stops and doesn't generate any faults, at least not when I watch the log in TFSActivityLog database.

When I check the errorlog and also the logfiles I see the SQL error above and it shows when I try to open Work Items. I compered the values and the error contains the same tables and colums as the information in the databaselog.

The problem is that I don't get this error every time and also I sometimes get it even if I'm project admin but I can still view workitems.

The databaselog for the contributor and the admin are the same until a line where it fetches the workitems and calls the same colums that are displayed in the error message. Thats why I belive that it could be related, I have tried creating new projects with new admins and new user accounts without success.

Here are the logs (I have masked out the domain / ip ):

Databaselog for the User:

68	WorkItem Tracking	QueryWorkitems	0	2006-10-05 09:05:55.343	237967	xxxx\User	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	68	Constants	4	114829
68	WorkItem Tracking	QueryWorkitems	0	2006-10-05 09:05:55.343	237967	xxxx\User	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	68	ConstantSets	6	114862
68	WorkItem Tracking	QueryWorkitems	0	2006-10-05 09:05:55.343	237967	xxxx\User	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	68	Fields	2	3820
68	WorkItem Tracking	QueryWorkitems	0	2006-10-05 09:05:55.343	237967	xxxx\User	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	68	FieldUsages	7	23364
68	WorkItem Tracking	QueryWorkitems	0	2006-10-05 09:05:55.343	237967	xxxx\User	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	68	Hierarchy	1	98585
68	WorkItem Tracking	QueryWorkitems	0	2006-10-05 09:05:55.343	237967	xxxx\User	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	68	HierarchyProperties	3	100109
68	WorkItem Tracking	QueryWorkitems	0	2006-10-05 09:05:55.343	237967	xxxx\User	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	68	psQuery	0	<Group GroupOperator="And"><Expression Column="System.AreaId" FieldType="288" Operator="under"><Number>187</Number></Expression><Expression Column="System.WorkItemType" FieldType="16" Operator="equals"><String>Task</String></Expression><Expression Column="System.State" FieldType="16" Operator="equals"><String>Active</String></Expression></Group>
68	WorkItem Tracking	QueryWorkitems	0	2006-10-05 09:05:55.343	237967	xxxx\User	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	68	Rules	5	100275
68	WorkItem Tracking	QueryWorkitems	0	2006-10-05 09:05:55.343	237967	xxxx\User	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	68	WorkItemTypes	8	100111
68	WorkItem Tracking	QueryWorkitems	0	2006-10-05 09:05:55.343	237967	xxxx\User	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	68	WorkItemTypeUsages	9	100125

Databaselog for the Admin: (note that it continues after the WorkitemTypeUsages and does something with the colums/tables that can be found in the error)

9

116	WorkItem Tracking	QueryWorkitems	0	2006-10-05 09:12:51.837	86269	xxxx\TFS_Admin	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	116	Rules	5	100275
116	WorkItem Tracking	QueryWorkitems	0	2006-10-05 09:12:51.837	86269	xxxx\TFS_Admin	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	116	WorkItemTypes	8	100111
116	WorkItem Tracking	QueryWorkitems	0	2006-10-05 09:12:51.837	86269	xxxx\TFS_Admin	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	116	WorkItemTypeUsages	9	100125
117	WorkItem Tracking	PageWorkitemsByIds	0	2006-10-05 09:12:52.043	156905	xxxx\TFS_Admin	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	117	asOfDate	33	2006-10-05 11:12
117	WorkItem Tracking	PageWorkitemsByIds	0	2006-10-05 09:12:52.043	156905	xxxx\TFS_Admin	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	117	columns	11	<12 array element(s)>
117	WorkItem Tracking	PageWorkitemsByIds	0	2006-10-05 09:12:52.043	156905	xxxx\TFS_Admin	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	117	columns[0]	12	System.Id
117	WorkItem Tracking	PageWorkitemsByIds	0	2006-10-05 09:12:52.043	156905	xxxx\TFS_Admin	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	117	columns[1]	13	System.Rev
117	WorkItem Tracking	PageWorkitemsByIds	0	2006-10-05 09:12:52.043	156905	xxxx\TFS_Admin	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	117	columns[2]	14	System.AreaId
117	WorkItem Tracking	PageWorkitemsByIds	0	2006-10-05 09:12:52.043	156905	xxxx\TFS_Admin	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	117	columns[3]	15	System.WorkItemType
117	WorkItem Tracking	PageWorkitemsByIds	0	2006-10-05 09:12:52.043	156905	xxxx\TFS_Admin	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	117	columns[4]	16	System.ChangedDate
117	WorkItem Tracking	PageWorkitemsByIds	0	2006-10-05 09:12:52.043	156905	xxxx\TFS_Admin	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	117	columns[5]	17	Microsoft.VSTS.Common.Discipline
117	WorkItem Tracking	PageWorkitemsByIds	0	2006-10-05 09:12:52.043	156905	xxxx\TFS_Admin	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	117	columns	18	System.State
117	WorkItem Tracking	PageWorkitemsByIds	0	2006-10-05 09:12:52.043	156905	xxxx\TFS_Admin	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	117	columns[7]	19	System.AssignedTo
117	WorkItem Tracking	PageWorkitemsByIds	0	2006-10-05 09:12:52.043	156905	xxxx\TFS_Admin	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	117	columns	20	Microsoft.VSTS.Common.Rank
117	WorkItem Tracking	PageWorkitemsByIds	0	2006-10-05 09:12:52.043	156905	xxxx\TFS_Admin	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	117	columns[9]	21	Microsoft.VSTS.Scheduling.CompletedWork
117	WorkItem Tracking	PageWorkitemsByIds	0	2006-10-05 09:12:52.043	156905	xxxx\TFS_Admin	xxx.xxx.xxx.xxx	00000000-0000-0000-0000-000000000000	Team Foundation (devenv.exe, 8.0.50727.147)	NULL	117	ids	0	<15 array element(s)>

Tracelogs for the error message:

[WI] [Error, 2880, 7, 13:43:19.363] SqlEx: id-547 msg-The INSERT statement
conflicted with the CHECK constraint "CK_Rules_NoUserRulesGlobal". The conflict
occurred in database "TfsWorkItemTracking", table "dbo.Rules", column
'System.AreaId'..

[WI] [Error, 2880, 7, 13:43:19.363] SvrEx: System.Data.SqlClient.SqlException: The
INSERT statement conflicted with the CHECK constraint "CK_Rules_NoUserRulesGlobal".
The conflict occurred in database "TfsWorkItemTracking", table "dbo.Rules", column
'System.AreaId'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,
Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand
cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler,
TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.HasMoreRows()
   at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
   at System.Data.SqlClient.SqlDataReader.NextResult()
   at
Microsoft.TeamFoundation.WorkItemTracking.Server.PayloadTableCollection.Populate(SqlDataReader
reader)
   at
Microsoft.TeamFoundation.WorkItemTracking.Server.SqlAccess.ExecuteBatchPayloadImpl(IRequestContext
context, String sqlBatch, List`1 parameterList, Boolean& errorOnBulkUpdate,
String connectionString).

[WI] [Verbose, 2880, 7, 13:43:19.363] --Counter:Update

[WI] [Error, PID 2880, TID 2332, 11:43:19.363] TF53010: An unexpected condition has
occurred in a Team Foundation component. The information contained here should be
made available to your site administrative staff.
Technical Information (for the administrative staff):
Date (UTC): 2006-10-05 11:43:19
Machine: VS00
Application Domain: /LM/W3SVC/3/Root/WorkItemTracking-4-128045221922700527
Assembly: Microsoft.TeamFoundation.WorkItemTracking.Server.DataServices,
Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a; v2.0.50727
Process Details:
  Process Name: w3wp
  Process Id: 2880
  Thread Id: 2332
  Account name: VS00\TFS_Admin

Detailed Message: The INSERT statement conflicted with the CHECK constraint
"CK_Rules_NoUserRulesGlobal". The conflict occurred in database
"TfsWorkItemTracking", table "dbo.Rules", column 'System.AreaId'.
Exception Message: The INSERT statement conflicted with the CHECK constraint
"CK_Rules_NoUserRulesGlobal". The conflict occurred in database
"TfsWorkItemTracking", table "dbo.Rules", column 'System.AreaId'. (type
SqlException)
SQL Exception Class: 16
SQL Exception Number: 547
SQL Exception Procedure: LookupRule
SQL Exception Line Number: 315
SQL Exception Server: VS00
SQL Exception State: 0
SQL Error(s):

Exception Data Dictionary:
HelpLink.ProdName = Microsoft SQL Server
HelpLink.ProdVer = 09.00.2047
HelpLink.EvtSrc = MSSQLServer
HelpLink.EvtID = 547
HelpLink.BaseHelpUrl = http://go.microsoft.com/fwlink
HelpLink.LinkId = 20476


Exception Stack Trace:    at
Microsoft.TeamFoundation.WorkItemTracking.Server.SqlAccess.HandleDalError(Exception
e)
   at
Microsoft.TeamFoundation.WorkItemTracking.Server.SqlAccess.ExecuteBatchPayloadImpl(IRequestContext
context, String sqlBatch, List`1 parameterList, Boolean& errorOnBulkUpdate,
String connectionString)
   at
Microsoft.TeamFoundation.WorkItemTracking.Server.SqlBatchBuilder.ExecuteBatchInternal(IRequestContext
context, Boolean passInConnectionInfo, String server, String database)
   at
Microsoft.TeamFoundation.WorkItemTracking.Server.DataAccessLayerImpl.UpdateImpl(String
serverName, String databaseName, String attachmentServer, String
attachmentDatabase, XmlElement updateElement, MetadataTable[] tablesRequested,
Int64[] rowVersions, Payload metadataPayload, Boolean bisNotification, String&
dbStamp, Boolean bulkUpdate, Boolean& bulkUpdateSuccess, String userSid)
   at
Microsoft.TeamFoundation.WorkItemTracking.Server.DataAccessLayerImpl.Update(String
serverName, String databaseName, String attachmentServer, String
attachmentDatabase, XmlElement updateElement, MetadataTable[] tablesRequested,
Int64[] rowVersions, Payload metadataPayload, Boolean bisNotification, String&
dbStamp, String userSid)
   at
Microsoft.TeamFoundation.WorkItemTracking.Server.ProcessSecurityEventMessage.ExecuteBatch(String
updateXml)
   at
Microsoft.TeamFoundation.WorkItemTracking.Server.ProcessSecurityEventMessage.Process()
   at Microsoft.TeamFoundation.WorkItemTracking.Server.SecurityEventMessage.Process()
   at
Microsoft.TeamFoundation.WorkItemTracking.Server.EventMessageHandler.ProcessSecurity(Int32
seqId)
   at
Microsoft.TeamFoundation.WorkItemTracking.Server.EventMessageHandler.ProcessSecurityEvent()

I believe that you paused sync long enough to hit one of several known conditions where there are permissions on area nodes that are either deleted or non-existent.

Do either of these SQL queries return a result that isn't 0?

select count(*)
from TfsIntegration.dbo.tbl_nodes_tombstone as n 
join TfsIntegration.dbo.tbl_security_objects as o
    on 'vstfs:///Classification/Node/' + cast(n.node_id as varchar(36)) = o.[object_id]
join TfsIntegration.dbo.tbl_security_acls as a
    on a.oid = o.oid
        and a.deleted = 0
        and (a.action_id = 'WORK_ITEM_READ' or a.action_id = 'WORK_ITEM_WRITE')
left join TfsWorkItemTracking.dbo.TreeNodes as tn
    on tn.CssNodeID = cast(n.node_id as varchar(36))
where tn.CssNodeID is null
 
select count(*)
from TfsIntegration.dbo.tbl_security_objects as o
join TfsIntegration.dbo.tbl_security_acls as a
    on a.oid = o.oid
        and a.deleted = 0
        and (a.action_id = 'WORK_ITEM_READ' or a.action_id = 'WORK_ITEM_WRITE')
left join TfsIntegration.dbo.tbl_nodes as n1
    on 'vstfs:///Classification/Node/' + cast(n1.id as varchar(36)) = o.[object_id]
left join TfsIntegration.dbo.tbl_nodes_tombstone as n2 
    on 'vstfs:///Classification/Node/' + cast(n2.node_id as varchar(36)) = o.[object_id]
where (n1.id is null and n2.node_id is null)

If either return results greater than 0, do not attempt to repair the database yourself. Please contact my email address and will send you a repair script. It's a bit more than a code-snippet.