June 15, 2012 at 9:57 am
I have 2 servers with SQL Server 2008 R2 installed. The Prod server is standard edition. The Test server is developer edition. Both were installed with a named instance. Both have no firewall, the SQL Server Browser is running on both and they both have TCP/IP and named pipes enabled. They both have "Allow remote connections to this server" checked and each server has just the one instance only.
Using SQL Server Management Studio I am able to connect to the Prod server with OR without specifying the instance name (server\instance or server) from my desktop. When I try to connect to the test server using only the server name and not specifying the instance name I get the following error:
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) (Microsoft SQL Server, Error: 2)
If I include the instance I am able to connect just fine. I cant figure out what the difference is that is making me include the instance name. Does anyone have any suggestions on what else I can check that is forcing me to include the instance name when connecting to the server?
June 15, 2012 at 10:55 am
Your prod server is probably listening on port 1433, meaning you don't need to provide an instance name to connect.
Your test server is probably listening on a port other than 1433 meaning you would have to provide either the instance name or the port number to connect. You can fix this by hard setting the port number in configuration manager, however before you do that, check to see if there are any other SQL instances installed on your test server (including express editions) that might be already using 1433.
June 15, 2012 at 12:27 pm
I checked and the port for both test and prod in the configuration manager are using the default port 1433 in the properties for TCP/IP. These are the only instances that have been installed on the servers.
June 15, 2012 at 12:44 pm
And you've confirmed TCP/IP is enabled on the test server?
If you attempt to telnet to the test box on port 1433 do you get a connection?
June 15, 2012 at 2:04 pm
My apologies! You were correct. I am still learning how to use the Configuration Manager!
I was looking at the wrong TCP/IP protocol. I saw port 1433 under the Native Client Config Client Protocol TCP/IP properties. When I looked at the TCP/IP properties under the Protocols for my named server, I didn't scroll down to the bottom of the TCP/IP properties to see that the TCP Port under the IPAll section did not have a Port defined. I then confirmed that the server wasn't listening on port 1433 by using the netstat -a command (it WAS defined on the Prod server). Once I added 1433 to the TCP Port under the IPAll section and restarted the Server, all was well.
Thanks for your help!
June 15, 2012 at 2:14 pm
Glad you're up and running.
June 15, 2012 at 2:55 pm
Is this something I should be configuring every time I do a SQL Server install of one instance?
June 15, 2012 at 3:52 pm
You'll need to enable TCP/IP, yes.
Here's a link to a powershell script that will take care of that for you.
June 15, 2012 at 4:00 pm
Thanks, Again!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply