Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

5 Answers 5

up vote 14 down vote accepted

Check if the SQL Server is running SQL Server Authentication

share|improve this answer
    
Ah thank you, Properties tab on the SQL Server instance revealed it was not running "Mixed mode authentication". –  GONeale May 3 '09 at 10:41
add comment

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
share|improve this answer
add comment

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

share|improve this answer
add comment

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!

share|improve this answer
add comment

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

Try:

CREATE USER myUserName FOR LOGIN myLoginName

Hope that helps ;-)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.