Home / server / Questions / 2218
Accepted
GONeale
Asked: 2009-05-01 23:35:05 +0800 CST

SQL Server Issue: Login failed for user 'username'

  • 772

I am running a copy of SQL Server 2008 Standard and trying to setup a new user login for access to a DB through a web app.

  1. I am using Security -> Logins -> Add login to create the user.
  2. Login name and password is specified
  3. Default database has been selected
  4. I am using a "public" server role
  5. I am mapping to the DB I require in "User Mapping" and selected it as "db_owner"

...The login/password combination is repeatedly failing on the app, and even when I try to login through SQL Server Management Studio I am receiving:

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

What could be the cause of this?

sql-server sql-server-2008

5 Answers

  1. Best Answer

    Check if the SQL Server is running SQL Server Authentication. In Microsoft SQL Server Management Studio, right click the database server, choose Properties and see the Server authentication section in the Security page.

    • 19

  2. This blog post could help finding what is causing the error:

    Understanding "login failed" (Error 18456) error messages in SQL Server 2005

    To determine the true reason for the failure, the administrator can look in the server's error log where a corresponding entry will be written.

    Look at the error state:

    ERROR STATE   ERROR DESCRIPTION
2 and 5       Invalid userid
6             Attempt to use a Windows login name with SQL Authentication
7             Login disabled and password mismatch
8             Password mismatch
9             Invalid password
11 and 12     Valid login but server access failure
13            SQL Server service paused
18            Change password required
    • 4

  3. Thanks alot,

    It was only set to windows authentication.

    Right click on your instance -> goto Server Properties -> Security -> then Change to SQL Server and Windows Authentication Mode

    You might have to restart your intance.

    Thanks again, Baskar

    • 1

  4. Create an additional test login to establish whether the problem is limited to just your original login. If your password uses special characters, type the password into Notepad and check that what you see in Notepad is the expected password. I had a similar issue and it was down to the keyboard being setup for US layout instead of UK. Good luck!

    • 0

  5. "User" and "login" in SQL Server are two different things.

    Try:

    CREATE USER myUserName FOR LOGIN myLoginName

    Hope that helps ;-)

    • 0