Build with an Azure free account. Get USD200 credit for 30 days and 12 months of free services.

Start free today

How To Fix Login Failed for User Microsoft SQL Server Error 18456 Step-By-Step

Play How To Fix Login Failed for User Microsoft SQL Server Error 18456 Step-By-Step

The Discussion

  • User profile image
    ahmed

    from login - properties - securable --> mark the permission for ( control server )
    this solve the error ISA

  • User profile image
    matay

    Right click server name from the SSMS and go to server properties. Click security and enable Wndows and SQL Server Authentication mode. This solution is to the point and will fix the problem.

  • User profile image
    jun

    Thank you matay ! I did that before on my local PC but I forgot completely.

  • User profile image
    raps

    Thanks matay. Been a awhile since I set up a new db server.

  • User profile image
    danstolts

    If this helped, Please rate using stars above.

    Thanks. 

  • User profile image
    Val

    Nah, mate its a no go with this method.

  • User profile image
    Val

    Just an update, here is the solution if anyone else has there error with a properly configured login:

    If you're trying to connect using "SQL Server Authentication" then you may want to modify your server authentication:

    Within the Microsoft SQL Server Management Studio in the object explorer:

    Right click on the server and click Properties

    Go to the Security page

    Under Server authentication choose the SQL Server and Windows Authentication mode radio button

    Click OK

    Restart SQL Services

  • User profile image
    DRepain

    Thanks This was very helpful

  • User profile image
    Yavuz

    Matay! thank you, it worked for me too :)

  • User profile image
    sandipapatel
    Just enable dual authentication mode and restart the service - it works like charm!
    Thanks.
  • User profile image
    Freddy

    Hey everyone! I am receiving the error code 18456 using local administrator & domain administrator so i cannot even login into SSMS. Any ideas? Thanks

  • User profile image
    Nathan

    Freddy - I was running into this issue too. Have you tried launching SSMS using "Run as Administrator" then signing in?

  • User profile image
    Ryan

    Nathan - Launching SSMS using "Run as Administrator" resolved the issue for me! I'm not certain as to why that fixes it but it works!

  • User profile image
    Todd McDaniel SQL DBA

    An application should NEVER log into a database as administrator and application users should never have administrator or even DB_OWNER level permissions to a database. This is very bad coding practice to do this and a big security risk.

    Application users should have Read or Read/Write/Execute permissions to the specific tables and procedures necessary, preferably controlled by user groups.

  • User profile image
    obeid

    when i finsh install SQL 2014 then i want run it i get this error i can't login how i can' do all solv taking about chang permmition but i cant connect with server any idea

  • User profile image
    Anup T Roy

    I just perform the below step:
    and it work for me.

    If you are trying to connect using your administrator credentials, start you application by using the Run as Administrator option. When connected, add your Windows user as an individual login.

    Ref:
    https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error?view=sql-server-ver15

  • User profile image
    Harish

    Matay!

  • User profile image
    yugene
    In my case, I needed to login to MSSQL that bundled in a Microsoft VHD which I attached as an OS disk to a VM so the only option I had was to use Windows authentication which was failing.
    Nathan's solution worked the Magic. Once I gained access, I could then add another user like Anup suggested.

    Thanks Nathan.
  • User profile image
    reza

    Thanks guys. After changing login authentication mode to mixed mode you have to restart SQLSERVER. Go to Services in windows and find SQLSERVER then restart it Solved the problem.

  • User profile image
    Dom

    Hello,
    I cannot get in SSMS even with the Built-In account.
    I tried several Local Administrators:
    1. the installer account
    2. the service account used for SQL Server
    3. the service account used for SQL Server Agent

    All these accounts failed after the Reporting Service was installed!!

    Any idea?
    Thanks,
    Dom

  • User profile image
    Tony

    It works! Changed both SQL to MixMode and wala!

  • User profile image
    Nick Armstrong

    Thanks, Matay! That worked. I tried just about everything but overlooked that setting.

  • User profile image
    Jiten Pansara

    I am getting this screen while login and not allowing me to enter to change Authentication Mode. I have checked "Users and Groups" but i don't see any such user by which i can login to the server. Please help.

  • User profile image
    Runor

    I faced this same problem after downloading and installing Microsoft SQL server/studio.

    I had to uninstall the application severally but all to no avail. I followed different recommendations from various sites online, all to no avail. For good 3days, I was uninstalling and installing to the extent during the installation I was told the database engine no longer exist. I went to services to start the servers, some run but some could not. Whenever I tried to connect to my server instance in the sql studio, it didn't connect no matter how I tried

    You know, I faced:
    Could not connect to server instance, database engine not found, Login failed for user, error code 945, error code 2, etc. Each of these was coming up after each installation and deletion of their installed files.

    I had to do a total clean up of all the Microsoft installed files and started the installation setup again.

    When it got to installation features:

    I tried as much as possible to select the database engine, the SDK connectivity is automatically checked, and only few features because I noticed the problem was arising from one of the numerous features but I couldn't tell which.

    As at when I was doing the installation, I initially checked (selected) all the features and that was what was causing the problem. One of the features was having problem but one cannot easily tell which.

    So the best is to select Database engine, and very few others of what you know you need and progress.

    This should solve your problem. I noticed one of the features for R, Python, Java and Polybase are the cause of the problem. But knowing which is the main problem.

    You can decide to test each of them to confirm which of them is causing the problem, that is if you need all of them, otherwise you can just ignore them and proceed with installing the database engine features, data analysis, SDK connectivity and others you think.

    Note: whenever you have any of the challenge I listed at the start of this writeup, please know it's one of the installed features that's causing it.

  • User profile image
    Dzaka

    Thanks. This was very helpful.

  • User profile image
    Rob

    This worked perfectly. Thanks. The key is to restart SQL Server Services using SQL Server Configuration Manager. :-)

  • User profile image
    Deepak

    Here is the solution if anyone else has there error with a properly configured login:

    If you're trying to connect using "SQL Server Authentication" then you may want to modify your server authentication:

    Within the Microsoft SQL Server Management Studio in the object explorer:

    Right click on the server and click Properties

    Go to the Security page

    Under Server authentication choose the SQL Server and Windows Authentication mode radio button

    Click OK

    Restart SQL Services

  • User profile image
    Harpreet

    After run SSMS as administrator issue got fixed.....thank all

  • User profile image
    Maricel

    How can I select something in Object explorer if I'm not able to login so it can bring the server in Object explorer?

Add Your 2 Cents