none
SQL Server 2008 Windows Authentication Mode fails for Database Engine, error 18456

    Question

  • I installed SQL Server 2008 Developer Edition (10.0.1600.22.080709-1414) on a Windows Vista 32-bit development machine with Windows Authentication Mode only (no SQL Server Authentication).   Since this is a development machine, I saw no need for Mixed Mode when I did the install. 

    The SQL Server Management Studio allows me to login to Analysis Services, Integration Services, Reporting Services, BUT NOT the Database Engine!

    Windows Authentication for the Database Engine gives the following error:

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

    The 'fix' I have found online is to login as SA in SQL Server Authentication mode & add MYDOMAIN\MYLOGIN as a administrator for the Database Engine.  Unfortunately, I can't since I didn't install SQL Server Authentication mode (only Windows Authentication Mode).  It appears my only recourse is to uninstall, then reinstall in Mixed mode, then to login as SA in SQL Server Authentication mode & add MYDOMAIN\MYLOGIN as a administrator for the Database Engine. 

    Before I do so, does anyone know of a better approach?

    Scott D Duncan
    Friday, December 26, 2008 4:10 PM

Answers

  • Raul, a DBA friend suggested that I login under the network admin account that was used for the initial install, then delete the MYDOMAIN\MYLOGIN Login, and re-add the MYDOMAIN/MYLOGIN Login, which I did and it worked!  I have no idea why this worked.  Thanks for your help.
    Scott D Duncan
    • Marked as answer by Scott D Duncan Wednesday, December 31, 2008 2:42 AM
    Wednesday, December 31, 2008 2:41 AM

All replies

  •  

      Login as SA is simply a common way to describe login as any member of sysadmin. When installing SQL Server 2008 the system always asks for at least one Windows user/group to be added as a member of the sysadmin server role.

      You should be able to try the workaround you found by connecting as a Windows user with sysadmin privileges without reinstalling SQL Server and without switching to mixed mode.

      I hope this information helps,

      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, December 26, 2008 6:45 PM
  • Thanks for the reply Raul.  I made sure the acount MYDOMAIN\MYLOGIN had Administrator privileges for the Windows Vista PC, but that did not make any difference.  I also tried using the built in MYPC\Admin account for the Windows Vista PC, but that did not help either.  I am sure that SQL Server 2008 was installed using one of these two accounts. 

    Since I cannot login to the Database Engine, I cannot add any Login accounts to the SQL Server sysadmin group, and being a member of the local machine Administrators group didn't help.  It seems to be a chicken & egg situation.  None of the Windows user accounts has sysadmin privileges, so I can't login to change my Login to allow me to login. 

    Interestingly, I can log in to Analysis Services, Integration Services, Reporting Services, and even the local MS SQL Server 2005 Express instance on this machine,  BUT NOT the SQL Server 2008 Database Engine (the default instance).
    Scott D Duncan
    Friday, December 26, 2008 7:49 PM
  • Raul, some more information.  I logged in as Network Admin, and was then able to login to the SQL Server 2008 Database Engine.  I must have installed it with this account.

    Unfortunately, the acount MYDOMAIN\MYLOGIN already was a member of the sysadmin group.  I did note that it didn't have any database associations, so I made it the dbo for all of the databases.  No luck. I still can't login with MYDOMAIN\MYLOGIN.  I still receive the error:

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

    I also tried turning on Named Pipes & TCP/IP protocols, but that didn't help.

    Any ideas on what to try next would be appreciated.


    Scott D Duncan
    Friday, December 26, 2008 8:17 PM
  • Did you try logging in from an elevated SSMS?

    Right click on SSMS icon and say "Run As Administrator" -- This is required on Vista and Windows 2008 clients.
    Matt Neerincx [MSFT]
    • Proposed as answer by I. SALOM Wednesday, September 15, 2010 9:34 AM
    Sunday, December 28, 2008 10:07 PM
  • Thank you for the suggestion Matt, but unfortunately that did not work.


    Scott D Duncan
    Monday, December 29, 2008 9:41 PM
  • Raul, some additional information.  The errorlog entry is:

    2008-12-29 18:04:06.79 Logon       Error: 18456, Severity: 14, State: 11.
    2008-12-29 18:04:06.79 Logon       Login failed for user MYDOMAIN\MYLOGIN'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

    I am told that State: 11 indicates that the Login is valid, but server access failed or was denied.

    I have read many forum entries that suggested:

    1) set the Login's default database to some other than "master"

    2) turn off filesharing for the computer (under Network > Properties)

    3) Start the SQL Server Browser service

    4) Ensure the account's password meets the strict password guidelines

    5)  Create a shortcut for SSMS with the setting to "run as Administrator"

    Unfortunately, none of these worked.  I suspect it is a Domain Controller issue, maybe some kind of Kerberos issue.  Any ideas?

    Scott D Duncan
    Tuesday, December 30, 2008 12:11 AM
  •    Unfortunately I don't know the answer for this one, but I have contacted the experts in protocols about this one. Hopefully they will be able to reply soon.

      Thanks a lot,
     
      -Raul Garcia
       SDE/T
       SQL Server Engine
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, December 30, 2008 8:27 PM
  • Raul, a DBA friend suggested that I login under the network admin account that was used for the initial install, then delete the MYDOMAIN\MYLOGIN Login, and re-add the MYDOMAIN/MYLOGIN Login, which I did and it worked!  I have no idea why this worked.  Thanks for your help.
    Scott D Duncan
    • Marked as answer by Scott D Duncan Wednesday, December 31, 2008 2:42 AM
    Wednesday, December 31, 2008 2:41 AM
  • Scott, I am having this exact problem with my 2008 in Vista. I was using the 2005 sqlserve with no issues, however the trial expired and I was shutout. I deinstalled 2005 and loaded 2008 and I now have this problem also. I setup the system admin user as the logn account for admin access, however my user login will not access the database server.

    What is it that you did to fix this??

    Let me know.
    Bud Carroll
    • Proposed as answer by ChinhDang Sunday, June 21, 2009 6:45 AM
    Friday, February 20, 2009 12:47 AM
  • Guys, has anyone found a resolution to this issue, I am having this issue with a windows Server 2008 x64 RC2 and SQL Server 2008 SP1 slipstream install.
    I have tried the above solution, and have had no joy to date.
    Wednesday, June 24, 2009 4:22 AM
  • I was able to solve this issue by doing the following (NOTE: I only have Windows Authentication enabled, not mixed)

    1. Logon as a local administrator.

    2. Stop the "SQL Server" service and run "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -m" (as described in http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=353451 to start SQL Server in Local Only mode.

    3. Logon to SSMS from the server itself - no password required.

    4. Add my own domain account as a Login, with sysadm server role.

    5. Restart the SQL Server service

    Now I am able to login to Database Engine as myself. There is still an issue however. During the install I specified Users\Domain Admins (which I am a member of) as the group with DBA privs. If I follow the above procedure and specifiy this (or any other group which I'm a member of) I still get the original issue ocurring. Only when I specify my account directly does it work.

    Hope this helps!
    • Proposed as answer by Anonymous2726 Sunday, March 07, 2010 6:08 AM
    Monday, June 29, 2009 6:18 AM
  • I was able to solve this issue by doing the following (NOTE: I only have Windows Authentication enabled, not mixed)

    1. Logon as a local administrator.

    2. Stop the "SQL Server" service and run "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -m" (as described in http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=353451 to start SQL Server in Local Only mode.

    3. Logon to SSMS from the server itself - no password required.

    4. Add my own domain account as a Login, with sysadm server role.

    5. Restart the SQL Server service

    Now I am able to login to Database Engine as myself. There is still an issue however. During the install I specified Users\Domain Admins (which I am a member of) as the group with DBA privs. If I follow the above procedure and specifiy this (or any other group which I'm a member of) I still get the original issue ocurring. Only when I specify my account directly does it work.

    Hope this helps!

    Thank you!  This solution worked for me and saved lot of time.
    Sunday, March 07, 2010 6:09 AM
  • I think this issue is related to the way that Vista, Windows 7 and Windows 2008 / 2008 R2 treat users who are logged on to the system with an account that is a member of the local administrators group when SQL is running locally.

    If your SQL setup has left you with BUILTIN\Administrators being a member of the sysadmin server role and you start up SQL Management Studio you'd expect to be mapped to the sysadmin role if your user account is in the local administrators group, however these OS disable this ability and when you try to connect to the database engine SQL server doesn't know you are a member of the local administrators group.

    To get round this, close all your open SQL management studio windows and then start a new window by right clicking the icon in the start menu and chosing to run as administrator. This time when you try to connect to the SQL database engine, windows doesn't "hide" the fact that you are an administrator. If you need to do this a lot you can go to the compatibility tab on the properties of the shortcut and set it to always run as administrator.

    Alternatively you can install the admin tools remotely and you don't get this effect.

    ------------------------------------------------------------------

    Tim

    • Proposed as answer by skurfer Monday, April 26, 2010 6:38 PM
    Friday, April 23, 2010 1:54 PM
  • Tim,

    I started to test out a Windows 2008 server with SQL Server 2008 on a remote virtual server.  The server is in our company's domain, my domain account account has been added to the local administrators group on the server, and I added the BUILTIN\Administrators to SQL with sysadmin privileges.  But I can not log in to access the database.  I think you are correct that even having the above (which worked fine in SQL 2000) may not work.  This does not seem right, is there something we are missing or is this just the way Microsoft changed their security?

    [Edit] I found the Microsoft documentation that explains the change in security.  It looks like either add the individual domain account that needs admin access and grant sysadmin or use the right click and choose Run as administrator to open SSMS.  The BUILTIN\Administrators group does not automatically elevate the privileges for SQL Server 2008.

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

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

     

    Monday, April 26, 2010 6:43 PM
  • Actually I believe any system admin on that machine can log in as an admin.  The system admin is absolute since it already has full access to the contents of the machine...
    Tuesday, June 08, 2010 10:24 PM
  • o/s=windows server 2008

    sql version = sql server 2008

    Received error 18456 when attempting to login as domain admin. *** Note: No severity logged ***

    I inherited this server from our previous, fearless admin and could not log in through Microsoft SQL Server Manager.

    reviewed details in the application log and found byte data of <server-name> master.

    SOLUTION:

    Right click on Microsoft SQL Server Management Studio and runas administrator

    Logged in fine this time around :)

     

     

     

    Monday, September 13, 2010 3:28 PM
  • Thanks for your suggestion, it did it for me.
    Wednesday, September 15, 2010 9:34 AM
  • Thanks Andrew,

    Your steps worked for me. I was facing Login failed issue even when local administrator tried to logon to SQL Server 2008 R2. Server was installed from other network account.

    I followed above steps and added local admin and other network accounts to database.

     

    Thursday, June 02, 2011 5:57 AM
  • This exact thing has happened to us twice, once on SQL 2008, once on SQL 2008 R2.  I have posted a bug report in SQL Server Connect as "Login Fails for Windows Authentication", ID 676672.


    Thank you, Dan
    Thursday, June 23, 2011 5:00 PM
  • This blog post gives you information to help you to troubleshoot the
    problem:
    http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, June 23, 2011 9:51 PM
  • Thanks Tim

    you solution worked for me. I had the same promblem and it works fine with your solution.

    Thank very much

    ken

    Thursday, July 21, 2011 7:32 PM
  • Thanks a lot. It worked for us, made life easy.
    Wednesday, February 15, 2012 4:11 PM
  • If you are running Vista or Windows 7, you need to run the programs 'Run as Administrator'  I did this an that solved my issue for this error with a version of MS SQL2005 Developers Edition that came with my Visual Studio 2005 pro.  Mater of fact it seems all 2005 products need to run at this elevated privilege to work correctly regardless of login/user settings. 

    Matt you saved me a bunch of time!

    Wednesday, August 01, 2012 3:01 AM
  • Thanks, in my case using SQL 2008 R2 on Windows 2012 server.

    Although I have BUILTIN\Administrators set to sysadmin on the SQL Server I still needed to add my domain user as sysadmin explicitly.  Happened to be a SCVMM 2012 SP1 install.


    Tonster

    Monday, May 20, 2013 1:19 AM
  • Turn Off the UAC on Windows.

    Thanks

    Monday, July 08, 2013 7:13 PM
  • I was able to solve this issue by doing the following (NOTE: I only have Windows Authentication enabled, not mixed)

    1. Logon as a local administrator.

    2. Stop the "SQL Server" service and run "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -m" (as described in http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=353451 to start SQL Server in Local Only mode.

    3. Logon to SSMS from the server itself - no password required.

    4. Add my own domain account as a Login, with sysadm server role.

    5. Restart the SQL Server service

    Now I am able to login to Database Engine as myself. There is still an issue however. During the install I specified Users\Domain Admins (which I am a member of) as the group with DBA privs. If I follow the above procedure and specifiy this (or any other group which I'm a member of) I still get the original issue ocurring. Only when I specify my account directly does it work.

    Hope this helps!

    Helped me too, thanks!

    Josh

    Wednesday, August 07, 2013 12:39 AM
  • This is all that is needed. NO need to turn on SQL Server Browser service.
    • Proposed as answer by Brad007 Friday, December 06, 2013 4:51 PM
    Friday, December 06, 2013 4:51 PM
  • Thanks Matt it's working!!!!!!! :) God bless U!!!!!! :)
    Sunday, March 30, 2014 7:52 PM
  • Hello Scott...

    Check this Link.

    https://www.youtube.com/watch?v=aU8RhjdkCoE

    Monday, May 19, 2014 10:14 PM