Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Here is the answer since I am not allowed to answer my own question yet: –  jpsnow72 Dec 7 '12 at 18:59
    
The issue was that the server was set to "Windows Authentication Mode" only. To fix this I 1. Right click the server - > Properties 2. Click "Security" in the left side of the "Server Properties" dialog 3. Changed server Authentication to "SQL Server and Windows Authentication mode" 4. Clicked "OK" 5. 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: support.microsoft.com/kb/555332 –  jpsnow72 Dec 7 '12 at 19:00
    
Just a note. When you change the Server Authentication from Windows Authentication to Mixed Mode. You'll need to manually enable the SA login if you want it available. Personally I would keep it disabled in your case since you have access through your Windows acount. –  Brandon leach Dec 7 '12 at 23:04

1 Answer 1

up vote 2 down vote accepted

My answer... From my comments:

The issue was that the server was set to "Windows Authentication Mode" only. To fix this I

  1. Right click the server - > Properties
  2. Click "Security" in the left side of the "Server Properties" dialog
  3. Changed server Authentication to "SQL Server and Windows Authentication mode"
  4. Clicked "OK"
  5. 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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.