How to login MS SQL server 2008 database engine

Jephe Wu - http://linuxtechres.blogspot.com


Environment: Windows 2008 server R2 64bit with MS SQL Server 2008. Initially, user1 who belongs to Windows local Administrators group installed this SQL server,
after he resigned, another user who also belongs to local administrators users  group cannot login database engine.

Objective: to make user2 to login to database engine.

Errors: When user2 tries to login sql server 2008 with Windows authentication mode, it gives the following error messages in event log:
Login failed for user MYDOMAIN\MYLOGIN'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

Steps:
1. login sql server management studio as the initial user1 who set up the sql server.
use [local] to login, you may want to try computer name in server name part

2.  under 'security - login' tab, create a new user login name as \\computername\jwu for Windows authentication, grant public and sysadmin server role , and enable 'grant permission to login database engine' under status tab.

3. logout user1, login as local administrator user 'jwu', use computername as servername to login sql server management studio.

4. you should be able to login now.

References:

1.  Choose an authenticatin mode - http://msdn.microsoft.com/en-us/library/ms144284.aspx
2.  How to change server authentication mode - http://msdn.microsoft.com/en-us/library/ms188670.aspx