SQL Server 2008 R2 Unable to connect remotely

0
votes

Please help, I have been working on this for days.

I am trying to connect to my database in Visual Studio 2010 using the named instance by

123.123.123.123/instanceName

however, i encounter the

error provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server

I have enabled the following:

  • open Port 80, 1433, 1434 on router
  • allow Port 80, 1433, 1434 through Firewall (UDP, TCP)
  • allow Program Sqlsvr.exe, SqlBrowser.exe through Firewall
  • server server (instanceName), SQL Server Browser, Named Pipes, TCP/IP
  • enabled Remote Connection in SQL Server Management

I have followed the guide in http://technet.microsoft.com/en-us/library/cc646023.aspx

What is it that I am missing? Any ideas?

1
sqlsql-serversql-server-2008
did you start sql agent from the services ? – Saddam Abu Ghaida
yes sir, for my instance but not the sqlexpress – Bloopie Bloops
Review blog.sqlauthority.com/2009/05/21/… – Mike Sherrill 'Cat Recall'
have seen and done every step in this before, is there anyway to check what i've missed out? – Bloopie Bloops

1 Answers

0
votes

We had the same problem, finally figured out that a dynamic port entry has to be given along with the SQLExpress login .. ie 192.168.1.25\SQLEXPRESS,45490... then it allowed the login to happen.

We had installed a new SQLEXPRESS 2008 R2 (Windows 7 Professional Edition) in a new machine & was trying to connect to this DB from another machine from the mgmt studio and it was not connecting, nor was it connecting from any of the client machines.

We tried to check the SQLEXPRESS Browser / TCPIP was enabled and spent couple of hrs before we we figured out that the Dynamic port was causing this issue.

You can find this information, Open the SQLEXPRESS Configuration Manager, Select SQL Server Network Configuration on the left menu![Configuration Manager][1] Select Protocol for SQLEXPRESS You will find the TCPIP Enabled on the right side, click on the TCPIP and select properties go to IPALL .. you will find the dynamic port info there.

btw, we tried installation on two HP PCs had the same issue & was solved with the dynamic port, while when we tried the installation on the ACer PC - did not get this dynamic port issue - so not really sure if it had anything to do with the OEM OS setup !?

However, the above solved our situation.