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 Sql server 2005 on my local machine. During the studies of SQL server network configuration, I login my sql instance by my M/C IP (server name) and login 'sa' and run the following script.

USE Master;

GO
ALTER ENDPOINT [TSQL Default TCP]
STATE=STOPPED;

After that I am unable to login. I can't even login by windows authentication.

How can I revert or is there any another way to handle this situation?

share|improve this question
    
Can you try restarting the SQL server services? It might help. –  Sky Sep 5 '12 at 12:34
    
Yes, I have tried this..but still the problem exist.. –  JP Chauhan Sep 5 '12 at 13:06
    
What have you tried? –  Edward Dortland Sep 5 '12 at 13:16
    
STATE = STOPPED on the [TSQL Default TCP] endpoint prevents the SQL Server service from listening for incoming connections via TCP. –  Max Vernon Sep 5 '12 at 18:19

2 Answers 2

up vote 2 down vote accepted
  1. Connect with the DAC dedicated admin connection. You should be able to start the endpoint again.

    ALTER ENDPOINT[TSQL Default TCP] state = started
    
  2. Use named pipes to connect to SQL server. Use SQL Server Configuration Manager to enable naped pipes if disabled.
share|improve this answer
    
Have you got DAC enabled? –  Edward Dortland Sep 5 '12 at 13:09
    
I tried but problem still exist. In error log Message: " Error: 17813, Severity: 20, State: 1. The requested service has been stopped or disabled and is unavailable at this time. The connection has been closed. [CLIENT: xxx.xxx.9.xxx] " and during connection from sqlcmd: "Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired. " –  JP Chauhan Sep 5 '12 at 13:39
    
how to enable DAC? I tried to connect from sqlcmd mode and Management studio only . Is there any another mechanism to enable the DAC. While connecting from Managment studio "Microsoft Error 64 reported" –  JP Chauhan Sep 5 '12 at 13:48
    
Open sql server configuration manager on the sql server, look for server protocols, look for named pipes, enable it. Right click an select properties, copy the named pipe string (for later),now restart the sql server service. Open management studio, paste the named pipe string in the server name box. Connect. This should work –  Edward Dortland Sep 5 '12 at 13:58
    
Thanks Mr.Edward. The above solution "named pipe string in the server name box" works.... –  JP Chauhan Sep 6 '12 at 5:18

Can you edit your post to include the exact error message you are getting?

It sounds like your sa account is completely locked out. If that is the case you may need to install a new instance of SQL Server 2005 and connect all of your databases back to it.

share|improve this answer
    
I tried but problem still exist. In error log Message: " Error: 17813, Severity: 20, State: 1. The requested service has been stopped or disabled and is unavailable at this time. The connection has been closed. [CLIENT: xxx.xxx.9.xxx] " and during connection from sqlcmd: "Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired. ". While connecting from Managment studio "Microsoft Error 64 reported" –  JP Chauhan Sep 5 '12 at 13:45

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.