Error message when you try to authenticate an ODBC connection to your SQL Server 2005 or 2008 SQL Instance by using the ‘sa’ credentials: "Login failed for ‘sa’.”


Symptoms


When you enter in the ‘sa’ credentials for a connection to a SQL Server 2005 instance and click ‘Next’, you receive the error below if SQL Server or SQL Native Client driver is selected: 

Connection failed:

SQLState: ‘28000’

SQL Server Error: 18452

[Microsft][SQL Native Client][SQL Server]Login failed for user ‘sa’. The user is not associated with a trusted SQL Server connection.

When you enter in the ‘sa’ credentials for a connection to a SQL Server 2008 instance and click ‘Next’ you receive the error below if SQL Server, SQL Native Client or SQL Native Client 10.0 driver is selected:

SQL Server or SQL Native Client:

Connection failed:

SQLState: ‘28000’

SQL Server Error: 18456

[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ‘sa’.

SQL Native Client 10.0:

Connection failed:

SQLState: ‘28000’

SQL Server Error: 18456

[Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user ‘sa’.

Resolution


To resolve this problem, change the Server authentication from Windows Authentication mode to SQL Server and Windows Authentication mode.

To change the authentication mode to SQL Server and Windows, follow these steps:

1. On the computer that is running Microsoft SQL Server, open SQL Server Management Studio 2005.

To open SQL Server Management Studio:
a. Click Start, and then click All Programs.
b. Point to Microsoft SQL Server 2005 or Microsoft SQL Server 2008, and then click SQL Server Management Studio. The Connect to Server window opens.
c. In the Server name box, type the name of the instance of SQL Server.
d. In the Authentication list, click Windows Authentication.
e. Click Connect.

2. In the Object Explorer window pane, right-click the SQL Instance and select Properties.

3. Click Security under the select a page options.

4.  Change Server Authentication to SQL Server and Windows Authentication and click ok.

5.  Click ok to the prompt message about changes not taking affect until the instance is restarted.

6.  Right-click the SQL Instance in the Object Explorer and select Restart.

7.  Click Yes to restart the instance and if prompted to restart the SQL Server Agent.

After these changes have been applied and the SQL Instance has been restarted, attempt to verify the ODBC connection with the ‘sa’ account credentials.