locked
How to login with Administrator user to SQL Server 2008 database? RRS feed

  • Question

  • I am an administrator on a Windows Server 2008 box which is running SQL Server 2008. Someone else created a database that I am trying to access, but for some reason I cannot use my Windows Authenticated user to login to the SQL Server db. 

    I get the following error:

    Login failed for user 'DOMAIN\user'. (.Net SqlClient Data Provider)

    Log:

    Error: 18456, Severity: 14, State: 11.
    Login failed for user 'DOMAIN\user'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

    Any ideas? Thanks.
    Wednesday, March 3, 2010 7:20 PM

Answers

  • Hi josh6847,

    From your description, you can't connect the SQL Server 2008 via Windows Authentication or SQL Authentication, you have no valid login to connect to SQL Server.

    If I misunderstand, please let me know.

    I have test this issue by deleting Windows Authentication login and sa login in my computer.

    And I follow what the following link said, finally, I can connect to SQL Server.

    http://blogs.msdn.com/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

    Note: If you use a Operating System with User Account Control(UAC), you must run sqlcmd by “Run as administrator”.

    Hope this helpful.

    Regards,
    Tom Li - MSFT
    • Marked as answer by Tom Li - MSFT Thursday, March 11, 2010 3:28 AM
    Friday, March 5, 2010 2:31 AM

All replies

  • Yes, user access control (UAC) can do this,

    How about you right click on SSMS and select "Run As administrator"

    OR contact your SQL DBA if he/she is around and have him add give you appropriate SQL server access.
    Wednesday, March 3, 2010 7:46 PM
  • I have tried selecting "Run As administrator" and had no luck.

    Also, the DBA is on vacation till next week. I am an admin on the box so I can make whatever changes necessary, I just can't figure out what is causing it. 

    I tried going to Properties under Services >> SQL Server (MSSQLSERVER) and going to the Log On tab. From here I tried Local System, as well as setting This Account to my Windows Authenticated user.... still no luck. 

    Under Groups in Server Mananger I went to this: SQLServerMSSQLUser$raustpsw0723$MSSQLSERVER, right clicked on it and added my user to the group. Still no luck.

    Is there anywhere else that I can specify who has access to a specific SQL Server instance?
    Wednesday, March 3, 2010 8:06 PM


  • I think login you are using for windows has not been added into SQL Server so you can access Windows Server but not SQL.

    Turn off UAC on your server.
    Wednesday, March 3, 2010 8:11 PM
  • State 11 means that this is a valid login but there was a problem with accessing the server.

    Can you check the following:
    1. SPNs are created for the SQL Server service account (http://support.microsoft.com/kb/321044)
    2. The SQL Server service account is a sysadmin for the SQL instance

    Did you use a Service SID while you were installing the SQL instance?
    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.wordpress.com
    Twitter: www.twitter.com/banerjeeamit
    SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq
    Wednesday, March 3, 2010 8:13 PM
  • How do I turn off UAC on the server?
    Wednesday, March 3, 2010 8:28 PM
  • Click on Start, in the search line type MSCONFIG and hit enter.

    Click on the Tools tab, scroll down until you see Disable UAC, and then click Launch.

    Reboot your machine

    Wednesday, March 3, 2010 8:38 PM
  • Don't think you can login to sql server if your DBA hasn't added your login to it.

    Sys Admins (box) could login before in 2005 when DBA leave the builtin\adm acct in there ( which should be removed by DBA's as best practice) , in sql 2008 it doesn't create builtin admin, which is good.

    Let us know if you could login, one way could be if you can ask around & get the pswd for the sql svc acct & log in through it.
    Wednesday, March 3, 2010 9:35 PM
  • Hi josh6847,

    From your description, you can't connect the SQL Server 2008 via Windows Authentication or SQL Authentication, you have no valid login to connect to SQL Server.

    If I misunderstand, please let me know.

    I have test this issue by deleting Windows Authentication login and sa login in my computer.

    And I follow what the following link said, finally, I can connect to SQL Server.

    http://blogs.msdn.com/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

    Note: If you use a Operating System with User Account Control(UAC), you must run sqlcmd by “Run as administrator”.

    Hope this helpful.

    Regards,
    Tom Li - MSFT
    • Marked as answer by Tom Li - MSFT Thursday, March 11, 2010 3:28 AM
    Friday, March 5, 2010 2:31 AM
  • Good link Tom, Let us know if it works for you Josh.
    Friday, March 5, 2010 4:51 PM
  • Wow, Tom Li, this helped me *finally* get into my database instance.  Talk about a lack of good error messages.  The state even mislead me by saying that the problem wasn't with my login, but rather with being able to connect to the server.

     

    Also, for anyone else who might stumble across this, make sure that the SQL Server Agent service isn't running because it logs into the instance as an administrator, not allowing anyone else to log in (since it is in single-user mode).

    • Proposed as answer by Ollywills Wednesday, February 20, 2019 8:54 AM
    Tuesday, July 20, 2010 10:53 PM