Windows Server 2003 R2, SQL Server 2005 with SP4.  SQL Server Service is running, using TCP/IP only (Surface Area Configuration). Network Configuration - Shared Memory and TCP/IP are enabled. SQL Server Browser is disabled. I could login to SQL Server and connect to databases. Everything was fine.  But after restarted a server, I can not login... give me an error.  What can I check?  Strange things... I can not connect to SQL Server on the server SQL Server 2005 is on the OS.  But users *can* use/connec to the database from web applications.  Is the server wrong somewhere? Some more info, example, C drive (OS, SQL Server and system databases...), K and L drive (SAN for user databases and log files)  Please give me advice. 

Cannot connect to [servername]

A network-related or instance specific error occured 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: Name Pipes Provider, error: 40 - Could not open a connection on SQL Server) (Microsoft SQL Server, Error : 53)

coulda swore I replied to this...musta got deleted.

What's different in the connection string that works?

the connection string?

 could you tell us

a) What edition of SQL Server are you using? SQL Express or other edition

b) Is this the named instance or a default instance of SQL Server..?  

c) Are you trying to connect using SQL Server Management stuido? if yes, is this a remote SQL server instance or you are trying to login from SSMS locally?

You are saying that web users can connect to SQL server; in that case make sure you are using correct **Server Name** you can look into web application code for this information.

Answer...

a) SQL Server 2005 Standard

b) Default instance (MSSQLSERVER)

c) SQL Server Management Studio locally (C:OS and SQL Server Software and System databases; K:User databases, L:Log files)

Yes, I am using a currect server name from web applications. I can select and uodate data from the web applications. But I can not login/connect SSMS locally by sysad user... I am really not sure what happned or changed. Do I need re-configure something?  I don't think I need to reinstall SQL Server 2005... Where should I check?

 

What are you using as server name from the local machine?

i.e. When you connect from SQL Server locally

In SQL Server Management Studio, what are you using as server name?

You could either use

a) Localhost

b) ComputerName or HostName of the computer where SQL server is running

to connect to local SQL server.

 

 

I am using the server name (Computername or Hostname) of the computer where SQL server is running. That's the same server name when I could login to the SSMS.

Just other information, not sure if relates... we changed our gatewey settings last weekend, and we will change DNS IP address. Do I need to join or rejoin??? Just I am confusing what's wrong...  Please help.... Thanks.

Do I need to re-install SQLServer 2005 again over this?

Is it possible to fix this connection problem? Is it okay to just re-install SQL Server 2005 after SP4 installed server? I will take all files copies and backups and I can restore System dbs and user dbs.

I installed SQL Server 2005 SP4 last weekend. And After the installation, everything was fine. I could login to databases and look at the data. I could perform backup and restore...  I don't think SP4 did something.

Does anyone think re-install SQL Server 2005 will fix this problem easy way?

How is the script connecting? Connection string or DSN?

Why asking me the script connection? Connection string and DSN from web applications or MDB all they are fine. I am having problem to login/connect to SQL Server Management Studio locally.

Why asking me the script connection? Connection string and DSN from web applications or MDB all they are fine. I am having problem to login/connect to SQL Server Management Studio locally.

What is different from the successful connection and the unsuccessful connection?

If you already have a successful connection somewhere else, compare that string with your string. That will give you the answer.

Now I'm exhausted on this simplicity.

Best of luck,

Adam

You may misunderstand... Or I am telling not well. Sorry.  I could login to SSMS but now I can not to login to SSMS locally on the server. I don't have any successful connection. 

• Edited by Cocoa123 Thursday, April 21, 2011 2:00 AM

Ok, can you ping the server?

Try connecting  using the IPAddress, port instead of the servername

192.168.1.12, 1433

Adam

Yes, I could ping and telnet to the server. I decided to do to connect to the DAC.But I could not connect...I checked DAC in Surface Area Configuration and tried to connect to 'Database Engine', "ADMIN:servername\MSSQLSERVER'.

Error: (the sentences are the same...) (provider: SQL Network Interfaces, error: 25 - Connection String is not valid)(Microsoft SQL Server, Error: 87)

Curious, have you tried Windows Authentication?

Yes. Was it bad?

After revisiting the entire thread, you say "But users *can* use/connec to the database from web applications" but then you say, "I don't have any successful connection. "

so which is it?

Let me try... from outside the SQL Server, other PC or server's DSN or connection string (in code) can access to the SQL server databases data. BUT on the SQL server *locally*, I  open SSMS and try to connect Database Engine. I can *not* connect the databases, gave me the errors.  That's why, I am not sure what's going on.  Why I can not connect SSMS locally on the SQL Server.   The SQL Server configurations changed?

Ah!!!

It's most likely management studio corruption.

Try this:

1. Open Notepad
2. Save the blank file as myConnection.udl
3. Close notepad
4. Double-click the udl file you just saved
5. Fill it in and test it

Does it connect?

Edit: No SQL Server configuration didn't change. Most likely Management Studio corrupted. It happens.

In any event, try uninstalling SSMS and re-installing it. Your server/db engine is

Can I uninstall and re-install *only* SSMS?!  Is it okay now (I mean after installed SPs)?  If so, do I need to put the installation disk and can chose only SSMS? I am just very careful.... If I can keep server/dbs engine as it is and I can install only SSMS, I would like to try it. Please let me know.

Yes you can.

Did the .udl connect?

Hi,

I think windows firewall block the port 1433 / TCP/IP (SQL Server Network Config) is not enable, If so then 

Open SQL Config Manager configure the port settings.

Note: Remember that the default port is 1433.

Dont forgot to restart the service...

I thought I could not get .udl connection. But I went Dat aSource(ODBC) and got connection to the database on the SQL Server. Also, I did try .udl connection from my computer. That was succeeded too!  So, databases are running okay, right?  But still I can not connect databases from SSMS on the SQL Server locally... It's really everything there on the same server. Why I can not connect...  Do you think re-install SSMS will help this situation?

• Marked as answer by Cocoa123 Thursday, April 21, 2011 2:11 AM
• Unmarked as answer by Cocoa123 Thursday, April 21, 2011 2:11 AM

I did try 'ping IP' and 'telnet IP 1433' both were no problem...

you post took me out of problem that i was facing... I just wrote "localhost" in server name.

Thanks

Windows Server 2003 R2, SQL Server 2005 with SP4.  SQL Server Service is running, using TCP/IP only (Surface Area Configuration). Network Configuration - Shared Memory and TCP/IP are enabled. SQL Server Browser is disabled. I could login to SQL Server and connect to databases. Everything was fine.  But after restarted a server, I can not login... give me an error.  What can I check?  Strange things... I can not connect to SQL Server on the server SQL Server 2005 is on the OS.  But users *can* use/connec to the database from web applications.  Is the server wrong somewhere? Some more info, example, C drive (OS, SQL Server and system databases...), K and L drive (SAN for user databases and log files)  Please give me advice. 

Cannot connect to [servername]

A network-related or instance specific error occured 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: Name Pipes Provider, error: 40 - Could not open a connection on SQL Server) (Microsoft SQL Server, Error : 53)

my sql doesn't connects 2005 version...i am sick due to this shit...and i need it very urgently the error to be removed