Cannot connect to SQL Server Express with SSMS and SQL Server Authentication

Question

I've been using SQL Server Express with Windows authentication for many years with no problems. However, for a different application I need to use server authentication. I'm running SQL Server 2008 R2 Express.

Here is what I've done so far in SSMS:

• Enabled "SQL Server and Windows authentication mode" in the "Security" section of Server Properties

• Created a new server Login with 'public' and 'sysadmin' server roles

• Created a new database user with the above login and assigned it db_accessadmin, db_datareader and db_datawriter.

When I try to connect to the server using SSMS and SQL Server authentication I get the "Login failed..." with 18456 error.

I'm definitely providing a correct password.

Is there anything else that needs to be done for the server authentication to work?

Thanks.

Answer 1

It is required to restart SQL Server after enabling the SQL Server authentication mode.