login failed for user (Microsoft SQL Server error: 18456)

So you had some problems with your installation of the SQL Server 2005 installation on your machine and now that you have un-installed and installed it, you can't seem to log on to your own machine using basic windows authentication. You can log into other servers but you just can't seem to log on to localhost. So you search the internet for the error message and after going through a whole lot of issues that are vaguely related, like changing authentication type from windows to sql and so on and so forth, you land on my blog.

The Solution: Install Visual Studio 2005 SP2

This was a bug in SQL Server 2005 that was fixed in SP2.

35 Comments

  • thank you..

  • Yeah, and why can't i find this damn thing anywhere on the net!?

  • I installed service pack 3 for SQL 2005 and still getting the same error message.
    Please help what can be possible reasons for this?

  • You have to one thing;
    go to Management studio/enterprise manager=>rightclick on it go to Properties =>
    go to security => Server authentication=> select SQL Server and windows authentication mode.

    That it.
    cheers

  • I installed VS 2008 n' MSSQLserver2008 and gettin d error as login failed for user 'sa'
    so pls help me sort out d prblm.
    thnx a lot.

  • Have you installed Visual Studio 2008 Service Pack 1 and SQL Server 2008 Service Pack 1?

  • There's no bug in SQL Server 2005. You just need to run it as administrator..
    Right Click on short cut and select "run as administrator" It will work nicely

  • You are mistaken. I have UAC down. Everything runs as administrator.

  • This is pitiful. Here's the solution for SQL Server 2005, running from Windows 7:

    Start
    All Programs
    Microsoft SQL Server 2005
    Configuration Tools
    SQL Server Surface Area Configuration (come on!)

    Once this app is running, click on "Add New Administrator"
    On to the top right-hand side it'll say "user to provision". Enter your domain \ account info there. Next, just drag what you want from "available privileges" to "privileges that will be granted to [user]"


    This is so intuitive I can't believe you people haven't figured this out already!
    .

    This worked for me. If you think about it, this is MS solution to the pervasive "sa" accounts with no password. Anyhow, good luck.

  • Thanks to Faizan Ali, just followed his advise submitted on May 04, 2010 3:49 PM

    # re: login failed for user (Microsoft SQL Server error: 18456)

    There's no bug in SQL Server 2005. You just need to run it as administrator..

    Right Click on short cut and select "run as administrator" It will work nicely

    FURTHER: after running as admin and login in successfully, you will create have to create a new user, on SECURITY> LOGIN> , right-click login and add new user ie the user you just login. Then you wont have to run-as-admin all the time.
    Kwaambwa

  • Just try to restart your server to refresh your connection.

  • Thanks every one, it just worked right click run as administrator

  • It just worked with Run as Administrator. Thanks a lot.

  • I use visual studio 2010 and sql server 2008. I can't define a new user. when I try
    (Security->logins), error:18456 occured.
    Does any one know what should I do?

  • I use visual studio 2010 and sql server 2008. I can't define a new user. when I try
    (Security->logins), error:18456 occured.
    Does any one know what should I do?

  • thanks aloooooooooooooooottttttttttt

  • thanks...worked with run as admin

  • It looks simple but very very useful answer and the exact answer and i thank u a lot... i really gone mad because of this error

  • erver Authentication. The 'sa' login details are correct but still getting the following error message:

    Quote:
    Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)

    In order to resolve the issue, please refer to the following steps:

    1. Login to the MSSQL Server Management Studio with Windows Authentication.
    2. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
    3. Under the Server Properties, select a page of "Security".
    4. Select the Server authentication as "SQL Server and Windows Authentication mode" and click Ok.



    5. Restart the SQL Services and then try to login with 'sa' details.

  • please give me the step by step answer for this one ,,,,

  • ALTER LOGIN sa ENABLE ;
    GO
    ALTER LOGIN sa WITH PASSWORD = '' ;
    GO

  • Hai anil verma ,

    Thanks for Posying. My Problem is Solved a Lot because previously we use 2005 and now we are using Sql Server 2008 R2

  • I dont know who put following solution. Its a brilliant solution for Error:18456...though I dont know his name, however many many thanks to reduce my stress.....my hug for you...
    *********************************************
    This is pitiful. Here's the solution for SQL Server 2005, running from Windows 7:

    Start

    All Programs

    Microsoft SQL Server 2005

    Configuration Tools

    SQL Server Surface Area Configuration (come on!)

    Once this app is running, click on "Add New Administrator"

    On to the top right-hand side it'll say "user to provision". Enter your domain \ account info there. Next, just drag what you want from "available privileges" to "privileges that will be granted to [user]"



    This is so intuitive I can't believe you people haven't figured this out already!

    .

    This worked for me. If you think about it, this is MS solution to the pervasive "sa" accounts with no password. Anyhow, good luck.
    ***********************************************

  • What should I insert in server name?
    I want to connect to a remote sql server

  • It just worked with Run as Administrator. This is great. Saved me a reinstall

  • thanks for the comment that to open ssms using "Run as Administrator"

  • Thank a lot
    I restart the SQL Services
    It worked.

  • Thanks alot run as administrator worked for me

  • i inform to you, when you face this type problem so pls go in sa property and uncheck enforce password policy and alter database password or give same password.

    sa user as same.

  • In order to get rid from this problem follow the following minimal steps

    1- Start.
    2- All Programs
    3- Right Click on SQL Server Management Studio
    4. Go to Compatibility Tab.
    5. Click on the check box of "Run This Program as an Administrator under the Privilage Level

    HOpe fully you have resolved. Pls remembers me in ur prayers

    Br. Habib, Programmer,PARC,

  • how to solve the error for specified login from admin login

  • That's solve my problems too
    Thanks

  • Restart SQL Server
    It helps to me...

    Thanks

  • thanks anil verma xD

  • Hi. .
    Just enable the hidden administrator account then you can get rid if login error :)

Comments have been disabled for this content.