SQLServerWiki

“The Only Thing That Is Constant Is Change”

Reason: Token-based server access validation failed with an infrastructure error.

Posted by database-wiki on January 13, 2012

Issue:

=====

Cx reported that he gets the following error whenever the pertinent account is not a member of SysAdmin:

Login failed for user ‘Our-Domain\MS-Login’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 1x.x7.3x.xx9]

The error occurs using SSMS, SQLCMD, or their client application, and occurs both locally and remotely.

Service account is a member of sysadmins.  Users have access to their default database.

Error:

====

2010-03-18 16:02:11.71 Logon       Error: 18456, Severity: 14, State: 11.

2010-03-18 16:02:11.71 Logon       Login failed for user ‘Our-Domain\MS-Login’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 1x.x7.3x.xx9]

Data’s collected:

==========

select * from sys.server_principals where name=’MS-Domain\MS-Login’;

name                                                                                                                             principal_id sid                                                                                                                                                                          type type_desc                                                    is_disabled create_date             modify_date             default_database_name                                                                                                            default_language_name                                                                                                            credential_id

——————————————————————————————————————————– ———— —————————————————————————————————————————————————————————- —- ———————————————————— ———– ———————– ———————– ——————————————————————————————————————————– ——————————————————————————————————————————– ————-

MS-Domain\MS-Login                                                                                       274          0x010500000000000515000000D594617A44E0200                                                                                                                   U    WINDOWS_LOGIN                                                0           2010-03-17 15:02:32.143 2010-03-18 15:46:14.567 model                                                                                                                            us_english                                                                                                                       NULL

select * from sys.server_permissions order by grantee_principal_id;

class class_desc                                                   major_id    minor_id    grantee_principal_id grantor_principal_id type permission_name                                                                                                                  state state_desc

—– ———————————————————— ———– ———– ——————– ——————– —- ——————————————————————————————————————————– —– ————————————————————

100   SERVER                                                       0           0           1                    1                    COSQ CONNECT SQL                                                                                                                      G     GRANT

100   SERVER                                                       0           0           2                    1                    VWDB VIEW ANY DATABASE                                                                                                                D     DENY

105   ENDPOINT                                                     2           0           2                    1                    CO   CONNECT                                                                                                                          D     DENY

105   ENDPOINT                                                     3           0           2                    1                    CO   CONNECT                                                                                                                          D     DENY

105   ENDPOINT                                                     4           0           2                    1                    CO   CONNECT                                                                                                                          D     DENY

105   ENDPOINT                                                     5           0           2                    1                    CO   CONNECT                                                                                                                          D     DENY

100   SERVER                                                       0           0           101                  1                    VWAD VIEW ANY DEFINITION                                                                                                              G     GRANT

100   SERVER                                                       0           0           102                  1                    AUTH AUTHENTICATE SERVER                                                                                                              G     GRANT

100   SERVER                                                       0           0           102                  1                    VWAD VIEW ANY DEFINITION                                                                                                              G     GRANT

100   SERVER                                                       0           0           102                  1                    VWSS VIEW SERVER STATE                                                                                                                G     GRANT

100   SERVER                                                       0           0           103                  1                    AUTH AUTHENTICATE SERVER                                                                                                              G     GRANT

100   SERVER                                                       0           0           105                  1                    CL   CONTROL SERVER                                                                                                                   G     GRANT

100   SERVER                                                       0           0           105                  1                    VWAD VIEW ANY DEFINITION                                                                                                              G     GRANT

100   SERVER                                                       0           0           257                  1                    COSQ CONNECT SQL                                                                                                                      G     GRANT

100   SERVER                                                       0           0           257                  1                    VWAD VIEW ANY DEFINITION                                                                                                              G     GRANT

100   SERVER                                                       0           0           257                  1                    VWSS VIEW SERVER STATE                                                                                                                G     GRANT

100   SERVER                                                       0           0           261                  1                    COSQ CONNECT SQL                                                                                                                      G     GRANT

100   SERVER                                                       0           0           262                  1                    COSQ CONNECT SQL                                                                                                                      G     GRANT

100   SERVER                                                       0           0           263                  1                    COSQ CONNECT SQL                                                                                                                      G     GRANT

100   SERVER                                                       0           0           265                  1                    COSQ CONNECT SQL                                                                                                                      G     GRANT

100   SERVER                                                       0           0           266                  1                    COSQ CONNECT SQL                                                                                                                      G     GRANT

100   SERVER                                                       0           0           267                  1                    COSQ CONNECT SQL                                                                                                                      G     GRANT

100   SERVER                                                       0           0           268                  1                    COSQ CONNECT SQL                                                                                                                      G     GRANT

100   SERVER                                                       0           0           270                  1                    COSQ CONNECT SQL                                                                                                                      G     GRANT

100   SERVER                                                       0           0           271                  1                    COSQ CONNECT SQL                                                                                                                      G     GRANT

100   SERVER                                                       0           0           273                  1                    COSQ CONNECT SQL                                                                                                                      G     GRANT

100   SERVER                                                       0           0           274                  1                    COSQ CONNECT SQL                                                                                                                      G     GRANT

(27 row(s) affected)

Select * from sys.dm_os_ring_buffers where ring_buffer_type = ‘RING_BUFFER_SECURITY_ERROR’

ring_buffer_address ring_buffer_type                                             timestamp            record

——————- ———————————————————— ——————– —————————————————————————————————————————————————————————————————————————————————————-

0x00000000058C3C80  RING_BUFFER_SECURITY_ERROR                                   99801899             <Record id = “14” type =”RING_BUFFER_SECURITY_ERROR” time =”99801899″><Error><SPID>55</SPID><APIName>LookupAccountSid</APIName><CallingAPIName>LookupAccountSidInternal</CallingAPIName><ErrorCode>0x534</ErrorCode></Error><Stack><frame id = “0”>0X00000000025

0x00000000058C3C80  RING_BUFFER_SECURITY_ERROR                                   99801885             <Record id = “13” type =”RING_BUFFER_SECURITY_ERROR” time =”99801885″><Error><SPID>55</SPID><APIName>LookupAccountSid</APIName><CallingAPIName>LookupAccountSidInternal</CallingAPIName><ErrorCode>0x534</ErrorCode></Error><Stack><frame id = “0”>0X00000000025

0x00000000058C3C80  RING_BUFFER_SECURITY_ERROR                                   6131644              <Record id = “0” type =”RING_BUFFER_SECURITY_ERROR” time =”6131644″><Error><SPID>54</SPID><APIName>LookupAccountSid</APIName><CallingAPIName>LookupAccountSidInternal</CallingAPIName><ErrorCode>0x534</ErrorCode></Error><Stack><frame id = “0”>0X0000000002572

Select * from sys.endpoints

name                                                                                                                             endpoint_id principal_id protocol protocol_desc                                                type type_desc                                                    state state_desc                                                   is_admin_endpoint

——————————————————————————————————————————– ———– ———— ——– ———————————————————— —- ———————————————————— —– ———————————————————— —————–

Dedicated Admin Connection                                                                                                       1           1            2        TCP                                                          2    TSQL                                                         0     STARTED                                                      1

TSQL Local Machine                                                                                                               2           1            4        SHARED_MEMORY                                                2    TSQL                                                         0     STARTED                                                      0

TSQL Named Pipes                                                                                                                 3           1            3        NAMED_PIPES                                                  2    TSQL                                                         0     STARTED                                                      0

TSQL Default TCP                                                                                                                 4           1            2        TCP                                                          2    TSQL                                                         0     STARTED                                                      0

TSQL Default VIA                                                                                                                 5           1            5        VIA                                                          2    TSQL                                                         0     STARTED                                                      0

(5 row(s) affected)

From the above details customer has denied access to all the endpoints. That’s the reason we were not able to login to the server. We granted access to all the endpoints under default scenario that’s how it is going to be. And logins started to work.

class class_desc                                                   major_id    minor_id    grantee_principal_id grantor_principal_id type permission_name                                                                                                                  state state_desc

—– ———————————————————— ———– ———– ——————– ——————– —- ——————————————————————————————————————————– —– ————————————————————

100   SERVER                                                       0           0           2                    1                    VWDB VIEW ANY DATABASE                                                                                                                D     DENY

105   ENDPOINT                                                     2           0           2                    1                    CO   CONNECT                                                                                                                          D     DENY

105   ENDPOINT                                                     3           0           2                    1                    CO   CONNECT                                                                                                                          D     DENY

105   ENDPOINT                                                     4           0           2                    1                    CO   CONNECT                                                                                                                          D     DENY

105   ENDPOINT                                                     5           0           2                    1                    CO   CONNECT                                                                                                                          D     DENY

command:

======

grant connect on endpoint::[TSQL named Pipes] to public

grant connect on endpoint::[TSQL Local Machine] to public

grant connect on endpoint::[TSQL default TCP] to public

grant connect on endpoint::[TSQL default VIA] to public

grant view any database to public

Issue is resolved but after that when we ran Public Not Granted Server Permissions from policy based management it fails.

Additional Info:

About policy “Public Not Granted Server Permissions”

This policy is checking to see if the server has been Locked Down. It is looking to see if all connections have Specific writes to connect. You see by default a user connects and is assigned to the PUBLIC role. They have rights to view the database list as well. (Why have a login on a SQL Server unless there are basic rights?) But there are cases where specific rights are required. Here is a note from one of our program managers.  Sometimes it helps to hear it from a different perspective.
It appears to be testing whether the server-level public role is granted *any* permissions at all.   Note, however, that by default we ship that role with some granted permissions.  To see this, use:
select sp.name, sp.principal_id, sp.type, perms.*
from sys.server_principals as sp
join sys.server_permissions as perms
on   sp.principal_id = perms.grantee_principal_id
where name = 'public'
go
Essentially, by default we are letting members of public connect to SQL Server and see results from a ‘select * from sys.databases’.
As part of a security lock-down best practice , this rule presupposes you have REVOKE-ed all the default out-of-the-box GRANTs and *explicitly* GRANT-ed CONNECT and whatever other permissions are required to the app’s *explicit* logins and/or roles.  This is a defense-in-depth strategy that prevents connections from any principal the app doesn’t explicitly expect.
The point is this: you don’t want logins “inheriting” permissions from “public” – you want to grant them explicitly.   But you can’t just revoke these default permissions without making some compensating actions that are specific to your app.
There is documentation that describes how to “Lock Down” SQL so that it this policy might be useful.
This white paper provides some best practices (for 2005 but also applies to 2008) and includes discussions on endpoints, http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SQL2005SecBestPract.doc    
So I guess the question for you is, are you running your SQL Server in a “Locked Down” security mode? This is something that we might see at a military installation for example. This white paper might help narrow down whether you need this policy.
If you do need to use this policy, we need to look closer at how you do your connections and specify security. I found in my testing that this policy is very sensitive. It fails for any security that is inherited from the role Public.

For any login related error collect the output of the following queries.

On Master Database

=================

1.SELECT * FROM sys.server_principals

2.SELECT * FROM sys.server_permissions

3.Select * from sys.dm_os_ring_buffers where ring_buffer_type = ‘RING_BUFFER_SECURITY_ERROR’

4.Select * from sys.endpoints

5.Select * from sys.login_token

On Problematic Database

=====================

1.SELECT * FROM sys.database_principals

2.SELECT * FROM sys.database_permissions

3.SELECT * FROM SYS.user_token

A profiler trace when reproducing the issue with all events and columns , All SQL Server Error Logs, Application logs and system event logs ,security logs.

If the client machine is VISTA ( above ) or WINDOWS 2008 ( above ) please check if USER ACCESS CONTOR is enabled.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: