can't login to new install of SQL 2008 x64 via SSMS

Posted by tpcolson on Server Fault See other posts from Server Fault or by tpcolson
Published on 2011-11-26T19:12:06Z Indexed on 2011/11/27 1:52 UTC
Read the original article Hit count: 365

Filed under:
|
|

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)

© Server Fault or respective owner

Related posts about sql

Related posts about sql-server