8

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?

5 Answers 5

19

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.

1
  • Ah thank you, Properties tab on the SQL Server instance revealed it was not running "Mixed mode authentication".
    – GONeale
    May 3, 2009 at 10:41
4

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
1

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

0

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

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

Try:

CREATE USER myUserName FOR LOGIN myLoginName

Hope that helps ;-)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .