ODBC Data Source SQL Server Connection - Login Failed For User
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. When I hit next after entering the username and password I get:
Connection failed: SQLState: '28000' SQL Server Error: 18456 [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'myUser'.
After reading this: http://support.microsoft.com/kb/555332 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.
My answer... 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: http://support.microsoft.com/kb/555332