none
Remote Connection, Linked Server 2008

    Frage

  • Hello all, 

    We have a requirement. We have a SQL Server 2008 with many Database instances in it. We need to expose one of the databases so that user can run queries against tables. What is the best way to do this? I've followed http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/ I'm not sure where the problem is I try to connect to our server specifying IP address and SQL username and password in SSMS and I get

    "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) (.Net SqlClient Data Provider)" this error. Tried to use TCP/IP as default before connecting I get different error some thing like

    "....(provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (.Net SqlClient Data Provider)"

    Not sure if I'm missing server side settings or if I'm missing any client side(SSMS) configuration to access the remote server.

    Also I was thinking about exposing our database so that user can create a Linked Server at his end so that he can consume our data. How can we achieve that? 

    Please advice. 

    Thanks

    Donnerstag, 12. April 2012 02:33

Antworten

  • Just Wanted to Update I found what was happening in my case. Our servers are hosted in Soft Layer and they are using some firewall settings other than Windows firewall this was blocking remote communication. The Issue is resolved after we mapped our client IP to make remote calls to the server. 
    Donnerstag, 12. April 2012 20:44

Alle Antworten

  • Hi,

    If you are using user instance on SQL Server Express edition it will not work. Try to attach the database and try to connect again.

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    Donnerstag, 12. April 2012 02:43
  • Hey Janos, thanks for you message. What do you mean "using user instance on SQL Server Express edition" ? Our Production Server is not NOT Express edition and the machine I'm using to connect with is Express edition. I was able connect to the server from my Office machine(Installed SQL Server Express) but not from my home computer. 
    Donnerstag, 12. April 2012 04:49
  • Hi,

    Sorry, I thought that it is express edition. 

    Error message says that the server is not available. Do you have named pipes protocol enabled on the server? You may try to specify explicitly tcp protocol when connection to the server using: tcp:servername (I assume remote connection enabled along with tcp/ip protocol)

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    Donnerstag, 12. April 2012 04:52
  • Yes TCP/IP, Named Pipes are enabled on the Production server. I specified tcp:ServerIP at SSMS server name and I got this error "TITLE: Connect to Server

    Cannot connect to tcp:173.193.216.19.

    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: TCP Provider, error: 0 - No such host is known.) (Microsoft SQL Server, Error: 11001)" Gosh this is killing me there is something I'm missing 


    Donnerstag, 12. April 2012 05:04
  • Just Wanted to Update I found what was happening in my case. Our servers are hosted in Soft Layer and they are using some firewall settings other than Windows firewall this was blocking remote communication. The Issue is resolved after we mapped our client IP to make remote calls to the server. 
    Donnerstag, 12. April 2012 20:44