none
Cannot log in to SQL Server 2008

    Question

  • I just recently installed SQL Server 2008 in my computer. I used Windows authentication and added the current user (whom which is the only user) to the users of SQL Server. After successful installation, I started SSMS but I cannot log in. I checked the error logs and this is what is written:

     

    Error: 18456, Severity: 14, State: 11.

    Login failed for user 'REGISTRAR\registrar'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

    I looked in the internet to find a solution and all of them tell me to disable UAC. Problem is, I'm using WinXP which doesn't have UAC. Can anyone guide me please? Thank you very much. Also, i am not in a domain (mentioned since I remembered someone saying a solution that involves domain users).

    Thursday, June 02, 2011 10:47 AM

Answers

  • State 11 means that SQL Server was able to authenticate you, but weren't able to validate with the underlying Windows permissions.

    http://sqlblogcasts.com/blogs/simons/archive/2011/02/01/solution-login-failed-for-user-x-reason-token-based-server-access-validation-failed-and-error-18456.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, June 02, 2011 12:54 PM
    Answerer
  • Hi,

    You can switch to Mixed-mode Authentication and enable the sa account to login to the instance. Once you logged in with sa, then assign desired permission on your windows login account. later you can disabled the sa account. Here is a post on the required step to follows.

    Hope, this may help.


    SKG: Please Marked as Answered, if it resolves your issue.
    Saturday, June 04, 2011 6:40 AM
  • Yes, you should have specify windows login (connect to SQL Server) during the installation, or log in as Administrator and then connect to the SQL Server
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, June 05, 2011 7:16 AM
    Answerer
  • During installation of SQL you should have informed a Windows login account. In this case, connect to the SQL login as SA and then follow the steps:

    - In Object Explorer, go to Security / Login
    - create a new login and enter the Windows account you want.

    Marco Antônio Pinheiro / MCTS - Database Developer 2008 http://marcoantoniopinheiro.blogspot.com Se o post foi útil, não esqueça de marcá-lo.
    Sunday, June 05, 2011 12:22 PM
  •  >>connect to the SQL login as SA andthen follow the >>>steps:

    Only if you specified MIXED authentication


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, June 05, 2011 1:15 PM
    Answerer
  • Can be two things:

    1) The logging mode set. On the properties of SQL, the Security tab, verify the authentication mode. Select "SQL Server Authentication and Windows Server" and then restart SQL.

      or

    2) Check the properties of the SQL services. To start / run - open the screen "services.msc". Locate the SQL services, go to properties and check the logon form. Test marking the first option (Local System account) or a supervisor any user account. And then restart the SQL.

    Marco Antônio Pinheiro / MCTS - Database Developer 2008 http://marcoantoniopinheiro.blogspot.com Se o post foi útil, não esqueça de marcá-lo.
    Monday, June 06, 2011 12:56 PM
  • Hi manduz_griffus,

     

    Please refer the below link about troubleshooting conncetion to SQL Server when the System Administrators are locked out:

    http://msdn.microsoft.com/en-us/library/dd207004.aspx

     

    Thanks,
    Weilin Qiao


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    Monday, June 13, 2011 9:25 AM
    Moderator
  • Please change the authentication to Mixed mode restart and SQL Server  enable sa login then provide the sysadmin role to your windows login.

    here are the steps to change the authentication and enable sa login http://msdn.microsoft.com/en-us/library/ms188670.aspx

     


    http://uk.linkedin.com/in/ramjaddu
    Monday, June 13, 2011 5:26 PM
  • let me step back ...
    {
    Error: 18456, Severity: 14, State: 11.
    Login failed for user 'REGISTRAR\registrar'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]
    }

    UAC is already ruled out.  its windows XP so no UAC question.

    Few questions.
    1. What is the machine name?
    2. What is the user name which you have used to logged into windows?
    3. Was this account ever deleted and recreated?

    As other said, change to mixed mode, use sa account to login. Or start in single user mode and make is mixed mode+reset sa password.
    Then, I want you to follow http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/troubleshooting-specific-login-failed-error-messages.aspx and run the query. Please share the results.



    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Monday, June 13, 2011 5:42 PM
    Moderator
  • Sorry for the late reply, health problems. Anyway, I've ended up reformatting the computer and reinstalling SQL Server. Sorry, I haven't had the opportunity to tried the suggested solutions. Thanks for the replies, much appreciated.
    Monday, July 11, 2011 8:30 AM

All replies

  • State 11 means that SQL Server was able to authenticate you, but weren't able to validate with the underlying Windows permissions.

    http://sqlblogcasts.com/blogs/simons/archive/2011/02/01/solution-login-failed-for-user-x-reason-token-based-server-access-validation-failed-and-error-18456.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, June 02, 2011 12:54 PM
    Answerer
  • Hi Uri,

    Thanks for the reply and the link. I still have a problem though. In the link you posted, his solution was to delete the login. But I can't use SSMS because I cannot login. Is there a way to delete or create a new login outside of SSMS?

    Saturday, June 04, 2011 5:54 AM
  • Hi,

    You can switch to Mixed-mode Authentication and enable the sa account to login to the instance. Once you logged in with sa, then assign desired permission on your windows login account. later you can disabled the sa account. Here is a post on the required step to follows.

    Hope, this may help.


    SKG: Please Marked as Answered, if it resolves your issue.
    Saturday, June 04, 2011 6:40 AM
  • Hi Sudeepta,

     

    Thanks for replying but step 1 on the link you provided me requires that I log in to SSMS. Problem is, I can't log in to SSMS.

    Saturday, June 04, 2011 7:20 AM
  • Any help please. I've even reinstalled SQL Server 2008. Why won't it let the Windows user log in? It is the only user of Windows. Never happened to me before, only on this terminal. Is there something I should set for the Windows user before installing? It is an Administrator account so what could be lacking?
    Saturday, June 04, 2011 7:39 AM
  • Yes, you should have specify windows login (connect to SQL Server) during the installation, or log in as Administrator and then connect to the SQL Server
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, June 05, 2011 7:16 AM
    Answerer
  • During installation of SQL you should have informed a Windows login account. In this case, connect to the SQL login as SA and then follow the steps:

    - In Object Explorer, go to Security / Login
    - create a new login and enter the Windows account you want.

    Marco Antônio Pinheiro / MCTS - Database Developer 2008 http://marcoantoniopinheiro.blogspot.com Se o post foi útil, não esqueça de marcá-lo.
    Sunday, June 05, 2011 12:22 PM
  •  >>connect to the SQL login as SA andthen follow the >>>steps:

    Only if you specified MIXED authentication


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, June 05, 2011 1:15 PM
    Answerer
  • Hi everybody,

     

    As I stated: "Is there something I should set for the Windows user before installing? It is an Administrator account so what could be lacking?"

    The account I added was an Administrator account and is the only account on Windows XP. I added that user to SQL Server (using Windows Authentication) during installation. I thought there might be other requirements for the user so that is why the question. Still, I haven't resolve this issue yet so any help and/or ideas would be greatly appreciated.

    Monday, June 06, 2011 2:54 AM
  • Can be two things:

    1) The logging mode set. On the properties of SQL, the Security tab, verify the authentication mode. Select "SQL Server Authentication and Windows Server" and then restart SQL.

      or

    2) Check the properties of the SQL services. To start / run - open the screen "services.msc". Locate the SQL services, go to properties and check the logon form. Test marking the first option (Local System account) or a supervisor any user account. And then restart the SQL.

    Marco Antônio Pinheiro / MCTS - Database Developer 2008 http://marcoantoniopinheiro.blogspot.com Se o post foi útil, não esqueça de marcá-lo.
    Monday, June 06, 2011 12:56 PM
  • Hi manduz_griffus,

     

    Please refer the below link about troubleshooting conncetion to SQL Server when the System Administrators are locked out:

    http://msdn.microsoft.com/en-us/library/dd207004.aspx

     

    Thanks,
    Weilin Qiao


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    Monday, June 13, 2011 9:25 AM
    Moderator
  • Please change the authentication to Mixed mode restart and SQL Server  enable sa login then provide the sysadmin role to your windows login.

    here are the steps to change the authentication and enable sa login http://msdn.microsoft.com/en-us/library/ms188670.aspx

     


    http://uk.linkedin.com/in/ramjaddu
    Monday, June 13, 2011 5:26 PM
  • let me step back ...
    {
    Error: 18456, Severity: 14, State: 11.
    Login failed for user 'REGISTRAR\registrar'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]
    }

    UAC is already ruled out.  its windows XP so no UAC question.

    Few questions.
    1. What is the machine name?
    2. What is the user name which you have used to logged into windows?
    3. Was this account ever deleted and recreated?

    As other said, change to mixed mode, use sa account to login. Or start in single user mode and make is mixed mode+reset sa password.
    Then, I want you to follow http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/troubleshooting-specific-login-failed-error-messages.aspx and run the query. Please share the results.



    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Monday, June 13, 2011 5:42 PM
    Moderator
  • Sorry for the late reply, health problems. Anyway, I've ended up reformatting the computer and reinstalling SQL Server. Sorry, I haven't had the opportunity to tried the suggested solutions. Thanks for the replies, much appreciated.
    Monday, July 11, 2011 8:30 AM