cannot login to sql express 2008 by sql auth

Question

I have just restored a database that has been backed up from MS SQL 2005 to plain (non configured) MS SQL 2008 that is running on Win Std Server 2008. I have recreated users but I cannot login when I try to login with user and pass I created. Windows Auth is ok, I can login to management console by Windows Auth.

Detailed Error Message; Server Name: srvwin1\SQLEXPRESS Error Number: 18456 Severity: 14 State: 1 Line Number: 65536

Thanks all Baris

Answer 1

State: 1

is a generic state which is always returned to the client to prevent information disclosure to unauthenticated clients.

You should find a similar error message in your server log ("C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG") with an accurate state.

More information regarding this, and a list of possible states can be found here.

Answer 2

You should "link" sql accounts in your database (it’s saved inside database and identified by unique SID) with sql server accounts you have created. Use sp_change_users_login for it.

Answer 3

Have you checked for mis-matching security IDs on those SQL logins?

Try a

sp_change_users_login 'report'

to get a list of any that need fixing up and then sp_change_users_login 'update_one', 'username', 'username'

to re-map them.

Answer 4

In additional tries for the last time it gave error and STATE:58 which says that the server is not acception logins other then Windows Auth. So I have switched the mode to Windows Auth to Mıxed from the server properties security tab.

Thanks to you for your answers.