Cannot log into SQL Mgmt Studio

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Cannot log into SQL Mgmt Studio

Author

Topic

ph1sh99
Starting Member

7 Posts

Posted - 2009-07-30 : 14:53:41

Hi -

We are running SQL 2005 9.0.3077 on a Windows 2003 Enterprise R2 SP2 server. All the databases are functioning as they should, no issues. When I am logged onto the local SQL server and launch SQL Mgmt Studio using the servername, I get the following error message:

Cannot connect to 'servername'. A network-related or instance specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40-Could not open a connection to SQL Server) (MSSQLServer, Error: 53)

If I launch Mgmt Studio using local host or the IP, it opens fine. Maybe a DNS issue? If so, where do I check? Network settings look fine.

This error appears in Windows logs when opened in Mgmt Studio: Date:7/30/2009 11:46:09 AM
Log:Windows NT (Application)
Source: MSSQLSERVER
Category: Server
Event: 1073758876
Computer: 'Servername'

Message
The description for Event ID '1073758876' in Source 'MSSQLSERVER' cannot be found. The local computer may not have the necessary registry information or message DLL files to display the message, or you may not have permission to access them. The following information is part of the event:'Severity: 16 Error:0, OS: 0 '

In addition to this problem, my maintenance jobs are failing. The following is one of my errors:
Date: 7/29/2009 7:00:00 PM
Log: Windows NT (Application)
Source: SQLSERVERAGENT
Category: Job Engine
Event: 1073742032
Computer 'Servername'

Message
SQL Server Scheduled Job 'MashupDB.Subplan_1' (0xF37E50E767659B4881CF4DC91BFC6448) - Status: Failed - Invoked on: 2009-07-29 19:00:00 - Message: The job failed. The Job was invoked by Schedule 18 (MashupDB.Subplan_1). The last step to run was step 1 (Subplan_1).

I also have this error in the SQL Agent log:
Date: 7/30/2009 11:30:01 AM
Log: SQL Agent (Current - 7/30/2009 11:30:00 AM)

Message
[382] Logon to server 'SCOOP' failed (ConnAttemptCachableOp)

Does anyone know how to resolve this?

Ty

wshawnmelton
Starting Member

17 Posts

Posted - 2009-07-30 : 14:58:25

Ping the servname from the machine you are trying to connect from to see if it resolves the IP address.

Would also check the SQL Server configuration to make sure all that is configured properly for your environment.

w s melton

ph1sh99
Starting Member

7 Posts

Posted - 2009-07-30 : 15:24:57

I am able to ping the server and have the servername resolved. I am also able to open SQL Mgmt Studio on my local computer using the servername, but not on the actual server itself. I have also checked the configurations and the box is checked to allow remote logins.

wshawnmelton
Starting Member

17 Posts

Posted - 2009-07-30 : 16:06:56

You are able to ping the servername *from the server* and resolve an IP?

w s melton

ph1sh99
Starting Member

7 Posts

Posted - 2009-07-30 : 17:42:32

Yes, I am able to ping the servername and IP from the server and have them resolve.

wshawnmelton
Starting Member

17 Posts

Posted - 2009-07-31 : 08:44:34

Well I don't know if this will work but I have had one problem that I had to go into Connection Properties on the 'Connect to Server' box when you open SSMS and set the Network protocol to be TCP/IP.

The fact that this is the only instance of SSMS that cannot connect with servername would tell me it is something with the installation of SSMS.

w s melton

ph1sh99
Starting Member

7 Posts

Posted - 2009-07-31 : 11:53:44

I tried your suggestion, but I get the same error:
Cannot connect to 'servername'. A network-related or instance specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40-Could not open a connection to SQL Server) (MSSQLServer, Error: 53)

wshawnmelton
Starting Member

17 Posts

Posted - 2009-07-31 : 20:37:20

Came across this post (http://sql-server-performance.com/Community/forums/p/5326/19664.aspx).

w s melton

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-31 : 21:10:52

select * from sys.servers. is server listed?

did you change the service account for sql server? or change server name?

if so, may need to set SPN

http://technet.microsoft.com/en-us/library/cc773257(WS.10).aspx

ph1sh99
Starting Member

7 Posts

Posted - 2009-08-03 : 17:51:09

I tried the query you posted - select * from sys.servers and yes my server is listed. I did not change the service account or the servername. Any other ideas?

ph1sh99
Starting Member

7 Posts

Posted - 2009-08-04 : 19:10:09

creating an alias in the sql server configuration manager solved my problem. Thank you for your time and suggestions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources