SQL Server 2008 can't login with newly created user

Question

Using Vista...
1) I open SQL Server Management Studio.
2) I create a new Login by right-clicking on Security->Logins.
Check: SQL Server Authentication
Login name: tester
Password: test
Click OK
3) Click File -> Connect Object Explorer, select SQL Server Authentication and enter tester/test and click Connect. 4) Get "Login failed for user 'tester'. (Microsoft SQL Server, Error: 18456" with Severity = 14 and State = 1.

Any ideas?

EDIT: I forgot to mention that I added this user to User Mapping to my database of choice.

Answer 1

You'll likely need to check the SQL Server error logs to determine the actual state (it's not reported to the client for security reasons.) See here for details.

Answer 2

Hi Travis,

Lots of things can go wrong, but it looks like you only added the user to the server. You need to add them to the database too. Either open the database/Security/User/Add New User or open the server/Security/Logins/Properties/User Mapping.

Joe

Answer 3

I figured it out. SQL Server was not configured to allow mixed authentication. Here are steps to fix:

1) Right-click on SQL Server instance at root of Object Explorer, click on Properties
2) Select Security from the left pane.
3) Select the SQL Server and Windows Authentication mode radio button, and click OK.
4) Open up Services and restart the SQL Service (SQLEXPRESS) Windows service.

Is it bad form to answer your own question?