I have performed a fresh install of SQL 2008 x64 on a fresh install of Server 2008 R2 x64 in an AD environment. Upon install completion, I cannot login to the SQL Instance via SSMS, with the following error:
Login failed for user domain\user. Reason: Token-based server access validation
failed with an infrastructure error. Check for previous errors. [CLIENT: ].
Background: the server is correctly joined to the AD Domain, the install was performed with defaults, windows authentication only (per organizational rules), the SQL install completes with no errors, domain\user was added as SQL Amin during setup account provisioning, I am logged into to console as domain\user when this error occurs, windows firewall is OFF, UAC is ON (an will never be turned off in accordance with organizational policy).
To troubleshoot this error I have tried:
Run SSMS as administrator: fail;
Start SQL in single user mode, run SSMS: fail
Start SQL in single user mode, run SSMS as administrator: Success
Start SQL in single user mode, run SSMS as administrator, remove domain\user from sysadmin group, re-add, run SSMS: fail;
Any combination and permutation of log off and log on, reboot, and chant gregorian prayers: fail;
Reimage server with 2008 x64, slipstream SP2 into SQL 2008 install, all above troubleshooting steps are repeatable exactly, so I've narrowed this down to not being a SP issue;
(this is NOT 2008 SQL R2)
Any suggestion on how to grant management access to this fresh install of SQL 2008 via SSMS? Our organizational policy is no console access to servers, management will be done via management tools intalled on client workstations. domain\user is a group of 8 users whom will have SSMS installed on workstations. However, we can't even access SQL via SSMS from the console! We cannot deploy this in an environment where these 8 users will have to sneak into the server closet on the weekends and have console access to SQL and run SSMS as administrator.
EDIT:
domain\group is a replacement for the actual object; the queries indicate that domain\group does indeed have the right privelges....!?!
1> EXEC xp_logininfo 'domain\group' go
account name
type privilege mapped login
name
permission path
'domain\group' group admin 'domain\group'
NULL
xp_logininfo seems to show 'domain\group' in the sql admin group;
1> SELECT A.name AS 'Role', B.name AS 'Login'
3> FROM sys.server_role_members C
5> INNER JOIN sys.server_principals A ON A.principal_id = C.role_principal_id
7> INNER JOIN sys.server_principals B ON B.principal_id = C.member_principal
_id
9> go
Role
Login
sysadmin
sa
sysadmin
NT AUTHORITY\SYSTEM
sysadmin
NT SERVICE\MSSQLSERVER
sysadmin
NT SERVICE\SQLSERVERAGENT
sysadmin
domain\group
1> SELECT PRINCIPAL_ID AS [Principal ID],
2> NAME AS [User],
3> TYPE_DESC AS [Type Description],
4> IS_DISABLED AS [Status]
5> FROM sys.server_principals
6> GO
Principal ID User
Type Description
Status
------------ -------------------------------------------------------------------
------------------------------------------------------------- ------------------
------------------------------------------ ------
1 sa
SQL_LOGIN
1
2 public
SERVER_ROLE
0
3 sysadmin
SERVER_ROLE
0
4 securityadmin
SERVER_ROLE
0
5 serveradmin
SERVER_ROLE
0
6 setupadmin
SERVER_ROLE
0
7 processadmin
SERVER_ROLE
0
8 diskadmin
SERVER_ROLE
0
9 dbcreator
SERVER_ROLE
0
10 bulkadmin
SERVER_ROLE
0
101 ##MS_SQLResourceSigningCertificate##
CERTIFICATE_MAPPED
_LOGIN 0
102 ##MS_SQLReplicationSigningCertificate##
CERTIFICATE_MAPPED
_LOGIN 0
103 ##MS_SQLAuthenticatorCertificate##
CERTIFICATE_MAPPED
_LOGIN 0
105 ##MS_PolicySigningCertificate##
CERTIFICATE_MAPPED
_LOGIN 0
257 ##MS_PolicyTsqlExecutionLogin##
SQL_LOGIN
1
259 NT AUTHORITY\SYSTEM
WINDOWS_LOGIN
0
260 NT SERVICE\MSSQLSERVER
WINDOWS_GROUP
0
262 NT SERVICE\SQLSERVERAGENT
WINDOWS_GROUP
0
263 ##MS_PolicyEventProcessingLogin##
SQL_LOGIN
1
264 ##MS_AgentSigningCertificate##
CERTIFICATE_MAPPED
_LOGIN 0
265 domain\group
WINDOWS_GROUP
0
(21 rows affected)
