Although I am little slow and late to the party of testing SQL Server 2008 features, I realized “Its better late than never!”.
Well, with this I hit into one of the first issues where in I had to connect to a SQL Server 2008 running on a remote (remote in here means a different box), and I was not able to. I was getting this error “Named Pipes Provider, error: 40 – Could not open a connection to SQL Server” constantly. I was little confused and I went into analytical mode to understand the problem. Here is a quick list I did to ensure that I logon to this SQL Server 2008 instance.
1. Checked if I was able to ping the ip address of the host.
2. Check if SQL Server service is up and running. I usually don’t let any services start off automatically while I do installation, and I thought I had some company.
3. Tried checking if the option of “Allow Remote Connections” was ON. Well, here is the first hit I received where in I was searching for SAC (Surface Area Configuration Tool). In SQL Server 2008 this is discontinued, and to my surprise I couldn’t find pleasing reason to stop this tool. Now one will find it by Right Click Instance -> Click Facets -> New window with dropdown box of options where in one should choose Suraface Area Configuration. I was not comfortable using it, because I don’t have an exclusive tool to use this, instead everything looks pushed into one corner (“Facet”). Now that is a different reason to grumble about.
To find out what is wrong while trying to connect I checked the configurations options under SAC and found everything looks good and set to go. But, what on the earth is happening that the instance is not letting me in from my machine?
Also here is a link which talks more about – Where is the SAC tool?
5. Checked the Windows Firewall status.
4. Checked the Configuration Manager to see the protocols that are enabled and disabled. And there it was, both the named pipes and TCP/IP were disabled. As soon as I enabled these protocols (its not necessary you will want to enable both to connect) I was able to get through the connectivity and I was set to start using the new instance.
There is an interesting blog entry that talks exclusively about the connectivity issues which you guys may want to look and keep it in handy.
Okay moral of the story or this exercise. I told myself its just common sense, that you couldn’t go and check the protocols instead you were running through many steps and spending away valuable time. Thank God, this didn’t happen in prod environment! Silly me, if I had to do these in prod boxes 
. Sometimes, thinking at 20,000 ft level (which I tend to do often these days), I am forgetting my basics.
Happy reading, and cheers!
