debugsqlMinor
SQL Error while connecting with SQL server Authentication
Viewed 0 times
errorwhilesqlconnectingwithauthenticationserver
Problem
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
As I browsed through , I got the following steps:
-
In start menu, go to sql server 2005/ Configuration tools/ Surface
area configuration
-
Select Surface area configurations and connections Remote Connections
-
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
A Connection was established with server but then error occurred during the login process
As I browsed through , I got the following steps:
-
In start menu, go to sql server 2005/ Configuration tools/ Surface
area configuration
-
Select Surface area configurations and connections Remote Connections
-
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
Solution
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.
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.
Context
StackExchange Database Administrators Q#18428, answer score: 2
Revisions (0)
No revisions yet.