Sql Server Error 18456
I have moved my database from an SQL 2005 to a server with SQL 2008. I am now trying to create an ODBC Data Source. I am using 'With SQL Server authentication using a login ID and password entered by the user' and have entered my Login and password. The Login is visible in SQL Server Management Studio under Security- Logins.
I have moved my database from an SQL 2005 to a server with SQL 2008.I am now trying to create an ODBC Data Source.I am using 'With SQL Server authentication using a login ID and password entered by the user' and have entered my Login and password. The Login is visible in SQL Server Management Studio under Security- Logins.The login also contains the exact properties of the same login in my old server.
Sql State: 28000
When I hit next after entering the username and password I get: Connection failed:SQLState: '28000'SQL Server Error: 18456MicrosoftODBC SQL Server DriverSQL ServerLogin failed for user 'myUser'.After reading this: I realized that the server properties were set to 'Windows Authentication Mode' only, but even after changing to 'SQL Server and Windows Authentication Mode' I am still having problems connecting. I can connect with my Windows account though.
Sql Server Error 18456 Odbc
From my comments:The issue was that the server was set to 'Windows Authentication Mode' only. To fix this I. Right click the server - Properties. Click 'Security' in the left side of the 'Server Properties' dialog. Changed server Authentication to 'SQL Server and Windows Authentication mode'. Clicked 'OK'.
Restarted Associated services. At first I forgot to restart the services, so I was still getting the error, but now I am able to connect without an issue. This was very helpful.