Home / server / Questions / 119146
In Process
Harun Baris Bulut
Asked: 2010-03-05 04:51:30 +0800 CST

cannot login to sql express 2008 by sql auth

  • 772

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

windows-server-2008 sql-server-2008 sql

4 Answers

  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.

    • 1

  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.

    • 1

  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.

    • 1

  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.

    • 0