locked
Cannot logon as (local or Domain) Administrator (Error # 18456) RRS feed

  • Question

  • I am running SQL Server 2008 R2, on a Windows Server 2003 R2.

    I am not able to logon (using Windows Authentication) by neither the local 'Administrator' nor the domain 'Administrator' account.

    I am able ONLY to logon with a userid that has the 'Public' role but NOT the 'sysadmin' one !!

    Unfortunately, I have lost the password for 'sa' account!!!

    How can I be able to logon, by using Windows Authentication' , by using any 'Administration' windows account ?

    Best regards,

    Tom

     


    New to SQL Server
    Monday, February 28, 2011 8:29 PM

Answers

  • mismatch of login credentials . As you said earlier the paswword that you are providing is wrong .

    Refer the other link provided in the earlier post to fix the issue .

    ----------------
    Thanks,Suhas V

    • Marked as answer by TomS_ Friday, March 4, 2011 7:46 PM
    Friday, March 4, 2011 5:30 PM

All replies

  • Tom,

    If I remember correctly you can start sql server in single user mode and that will allow local admins to connect to the instance. As the name implies, only 1 connection can be made to sql server. Once connected you can change the SA login information or add domain accounts and set the permissions correctly.

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

    Hope this helps,
    Sean

    Monday, February 28, 2011 9:26 PM
  • Hi Sean, thank you for your reply!

    I have read the above link (startup SQL Server in 'single mode').
    This is a production db and I cannot bounce it, for now at least.

    I have to wait for the next maintenance cycle.

    I wonder if there is a 'quick' fix for enabling either 'Administration' account to logon, by using Windows Authentication.

    Best regards,

    Tom

     


    New to SQL Server
    Monday, February 28, 2011 10:13 PM
  • When attempting to login using the local administrator account, are you using the "Run as Administrator" option for starting the SSMS or the command line where you use SQLCMD? In other words, are you activily telling Windows to use your administrator credentials, because they won't be passed to the program by default.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Monday, February 28, 2011 11:10 PM
  • Hi Rick, thank you for your reply!

    This is Windows Server 2003, so when I 'right-click', I am having the option to 'Run as'  but the options I am having are:

    1) 'Current User (which is the local Administrator)

    2) 'The following User' where I can select any domain user.

    Best regards,
    Tom

     


    New to SQL Server
    Monday, February 28, 2011 11:39 PM
  • Hi Tom,

    I guess you have removed builtin\Administrators from the sysadmin server role and thats the reason it is nto allowing you to login


    Well i dont think that there is no other option and need to make chaanges in the maintaince window. Please refer the following blog where Raul Garcia have provided detailed steps as what to be done when Sa password is lost . 


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

    Can you even check for the  details of 18456 error ? 18456 is genral error and the state of it describes and what the login failure issue is all about .

    Kindly refer following article .
    http://sql-articles.com/articles/troubleshooting/troubleshooting-login-failed-error-18456

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

    Thanks,Suhas V


    Tuesday, March 1, 2011 7:01 AM
  • Sorry, I missed that you were on Windows Server 2003. That doesn't need the Run as administrator option. It will always pass the administrator credentials if you are an admin.
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Tuesday, March 1, 2011 4:54 PM
  • Using SQL Server Configuration Manager check to see what account the Database Engine is running under. If it is a domain account (not something like local system) then you should be able to login under that domain account. Of course that means you need to know that password.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Tuesday, March 1, 2011 4:57 PM
  • Hi Rick, thank you for your reply!

    The database/server has been started by 'LocalSystem', not a domain account.

    Best regrads,

    Tom


    New to SQL Server
    Friday, March 4, 2011 2:06 PM
  • Hi Suhas, thank you for your reply!

    Yes, I have read Raul Garcia's blog.

    The sql-articles link is very useful, thank you!

    Now, when I try to connect as 'sa', WITHOUT password I get this error:

    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

    A connection was successfully established with the server, but then an error occurred during the login process.

    (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

    (.Net SqlClient Data Provider)

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

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476

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

    Server Name: W3KSRVR\FINDB

    Error Number: 233

    Severity: 20

    State: 0

    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

    Now, when I try to connect as 'sa', WITH password I get this error:

    <><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>

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

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

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

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

    Server Name: W3KSRVR\FINDB

    Error Number: 18456

    Severity: 14

    State: 1

    Line Number: 65536

    <><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>

    Best regards,

    Tom

     


    New to SQL Server
    Friday, March 4, 2011 2:20 PM
  • mismatch of login credentials . As you said earlier the paswword that you are providing is wrong .

    Refer the other link provided in the earlier post to fix the issue .

    ----------------
    Thanks,Suhas V

    • Marked as answer by TomS_ Friday, March 4, 2011 7:46 PM
    Friday, March 4, 2011 5:30 PM
  • When attempting to login using the local administrator account, are you using the "Run as Administrator" option for starting the SSMS or the command line where you use SQLCMD? In other words, are you activily telling Windows to use your administrator credentials, because they won't be passed to the program by default.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    That did the trick... I guess it is better to create a SQL Admin Security Group and not to use the Domain Admins group.  Thanks!

    Thanks/Regards, Mike DePouw

    Thursday, March 8, 2012 1:49 AM