Sql-server – SQL Error while connecting with SQL server Authentication

sql serversql-server-2005

While I am trying to connect to SQL Server using SQL server Authentication , I am getting the following error.

A Connection was established with server but then error occurred during the login process

enter image description here
As I browsed through , I got the following steps:

  1. In start menu, go to sql server 2005/ Configuration tools/ Surface
    area configuration

  2. Select Surface area configurations and connections Remote Connections

  3. On the right see named pipes is selected or not. You can go for
    either named pipes only or named pipes and TCP/IP.

I did select Both TCP/IP and Named Pipes and then restarted the SQL Server. But still I am getting the same error.

I checked the SQL error log and it has following message:

2012-05-24 17:42:50.27 Server The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted.
If you would like distributed transaction functionality, please start this service.
2012-05-24 17:42:50.27 Server Database Mirroring Transport is disabled in the endpoint configuration.
2012-05-24 17:42:50.29 spid4s Starting up database 'master'.
2012-05-24 17:42:50.45 spid4s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2012-05-24 17:42:50.46 spid4s SQL Trace ID 1 was started by login "sa".
2012-05-24 17:42:50.51 spid4s Starting up database 'mssqlsystemresource'.
2012-05-24 17:42:50.62 spid9s Starting up database 'model'.
2012-05-24 17:42:50.72 spid9s Clearing tempdb database.
2012-05-24 17:42:50.91 spid9s Starting up database 'tempdb'.
2012-05-24 17:42:50.97 spid12s The Service Broker protocol transport is disabled or not configured.
2012-05-24 17:42:50.97 spid12s The Database Mirroring protocol transport is disabled or not configured.
2012-05-24 17:42:50.99 spid12s Service Broker manager has started.
2012-05-24 17:42:57.23 Server The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030d. Check certificates to make sure they are valid.
2012-05-24 17:42:57.26 Server Error: 17190, Severity: 16, State: 1.
2012-05-24 17:42:57.26 Server FallBack certificate initialization failed with error code: 1.
2012-05-24 17:42:57.26 Server Warning:Encryption is not available, could not find a valid certificate to load.
2012-05-24 17:42:57.26 Server Server is listening on [ 'any' 2036].
2012-05-24 17:42:57.26 Server Server local connection provider is ready to accept connection on [ \.\pipe\SQLLocal\SQLEXPRESS ].
2012-05-24 17:42:57.26 Server Server named pipe provider is ready to accept connection on [ \.\pipe\MSSQL$SQLEXPRESS\sql\query ].
2012-05-24 17:42:57.26 Server Server is listening on [ 127.0.0.1 3051].
2012-05-24 17:42:57.26 Server Dedicated admin connection support was established for listening locally on port 3051.
2012-05-24 17:42:57.28 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2012-05-24 17:42:57.29 spid4s Starting up database 'msdb'.
2012-05-24 17:42:57.37 spid4s Recovery is complete. This is an informational message only. No user action is required.
2012-05-24 17:43:08.42 Logon Error: 18452, Severity: 14, State: 1.
2012-05-24 17:43:08.42 Logon Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. [CLIENT: ]
2012-05-24 17:43:19.27 Logon Error: 18452, Severity: 14, State: 1.
2012-05-24 17:43:19.27 Logon Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. [CLIENT: ]
2012-05-24 17:50:25.63 Logon Error: 18452, Severity: 14, State: 1.
2012-05-24 17:50:25.63 Logon Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. [CLIENT: ]
2012-05-24 17:50:35.72 Logon Error: 18452, Severity: 14, State: 1.
2012-05-24 17:50:35.72 Logon Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. [CLIENT: ]

Best Answer

It looks like you have to enable sql authentication. Start the management studio and connect to your database. Right click porperties and in the security tab, allow sql authentication in addition. Then you can connect with the user sa. Atm it looks only on windows accounts.