• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 830
  • Last Modified:

How do I connect to a remote instance of SQL Server 2008

I have an instance of SQL Server 2008 installed on Windows Server 2008 and am trying to set it up for remote access from computers on the same network. The steps I have tried out so far are as follows:-

- Configured the TCP protocol for both IPn (n=internal IP of the server) as well as IPAll to use a static port 1640 and have set Dynamic Ports to blank
-The protocol priority is 1-Shared Memory, 2-TCP, 3-Named Pipes
-Configured Windows Advanced Firewall to allow incoming and outgoing connections on port 1640 for any computer on all the 3 trust zones - Domain, Public and Private.
-On one of the client computers (on the same network) I have an instance of SQL Server 2008 running and on that machine's Surface area config. manager i have configured native client configuration with an alias for port 1640 with the name of the DB Server that I want to connect to via TCP.
-Allowed outgoing/incoming connections via port 1640 on  my client machine via its firewall
-Configured the router to direct tcp connections via port 1640 to my DB Server

However on the client machine I still cannot see my Server's Database instance show up in the network servers list when I open SQL Server Management Studio.

Am I missing something ?
0
cranialsurge
Asked:
cranialsurge
  • 5
  • 4
  • 3
  • +2
1 Solution
 
aibusinesssolutionsCommented:
Do you have the SQL Server Browser running on the server?  Look in services.msc and make sure the service is running.  The Browser is what displays the SQL server instances to the network.
0
 
souvik2008Commented:
1. First Check that if the server machine, DB server is accessible by LAN or not . Try to find it out with windows : My Network Places. If you can't find it check the domain and IP of the client and Server machine and check is it on the same workgroup with same default gateway.

2. Open up the SQL Server Configuration Manager.( Programs / Microsoft SQL Server 2008 / Configuration Tools / SQL Server Configuration Manager.) and select the network configuration for remote connection.

3. Check the surface area configuration for SQL server to verify that remote connection to the instance of SQL server is enabled.

4. Start the SQL server Browser service if it is not already started.

5. Add exception to the windows firewall of the server  . You'll need to select the SQLServer.exe in Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\ and the SQLBrowser.exe in Program Files\Microsoft SQL Server\90\Shared\. Then select Properties for each of them and select the "Change Scope" button. Then select the proper scope.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to register remote instances, they won't show up automatically.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
cranialsurgeAuthor Commented:
How do you register a remote instance ?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
cranialsurgeAuthor Commented:
You can only access server explorer if you have visual studio installed.I am trying to access the Server instance via SQL Server Management Studio on the client machine. I need it to show up in the list of servers.
Also, Souvik ... I cannot find a sqlbrowser.exe under Program Files\Microsoft SQL Server\90\Shared\.
0
 
aibusinesssolutionsCommented:
The SQL Browser Service should be listed under Administrative Tools > Services on the Server itself.  Make sure that is running.
0
 
cranialsurgeAuthor Commented:
Yes, I verified that the service is running. I am searching for that executable file because Souvik instructed me to add it to the firewall. However, the serner still won't show up in my list of Servers for the Database engine in SSMS. The ports are configured, firewall rules are in place.
0
 
aibusinesssolutionsCommented:
The default location for Sqlbrowser.exe is

"C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe"

Did you install it on another drive, or as an additional instance?
0
 
aibusinesssolutionsCommented:
The sqlbrowser.exe would be on the server by the way, not your local machine.

0
 
cranialsurgeAuthor Commented:
Once again .... these are the steps I have followed

- Configured the TCP protocol for both IPn (n=internal IP of the server) as well as IPAll to use a static port 1640 and have set Dynamic Ports to blank
-The protocol priority is 1-Shared Memory, 2-Named Pipes, 3-TCP
-Configured Windows Advanced Firewall to allow incoming and outgoing connections on port 1640 for any computer on all the 3 trust zones - Domain, Public and Private.
-On one of the client computers (on the same network) I have an instance of SQL Server 2008 running and on that machine's Surface area config. manager i have configured native client configuration with an alias for port 1640 with the name of the DB Server that I want to connect to via TCP.
-Allowed outgoing/incoming connections via port 1640 on  my client machine via its firewall
-Configured the router to direct tcp connections via port 1640 to my DB Server
I do not want to add the sqlserver.exe directly to the firewall on the server as this is not secure. I am not listening to dynamic ports. I have specified a port that I want to use and hence shouldn't need to directly authenticate the executable in the firewall.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
You need to open a firewall UDP port for port no 1434 as you havent added an exception for sqlserver.exe. This will help the SQL server to check for incoming connections.. And make sure Browser Service is up and running.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>You can only access server explorer if you have visual studio installed
looks like I posted the wrong link.

but the principle is the same: in the management studio, you register a (remote) sql database instance. they won't be listed automatically, during the registration you also have to specify which login you want to connect with.
0
 
cranialsurgeAuthor Commented:
How do you register an instance in the management studio when that server won't show up in the list of network servers ? I can connect to my local instance just fine.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
To register a connected server

1. In Object Explorer, right-click a server to which you already are connected, and then click Register.
2. In the Register Server dialog box, in the Server name text box, type the name that you want to appear in Registered Servers for this server. This does not have to be the name of the server.
3. In the Server description text box, optionally type additional information to help you identify the server.
4. In the Select a server group box, click a server group, and then click Save.
0
 
souvik2008Commented:
You can find sqlserver.exe and the other necessary exe files in this location :
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
Now please add these exceptions in your windows firewall. Alternatively you can add exception using the add port button instead of addProgram button and assign the port which will not be blocked by the windows firewall.But in my opinion adding the programs exception to the windows firewall would be a much better option and will provide much better, faster and safer connectivity remotely.

P.S Most Importantly, Check the surface area configuration for SQL Server in START -> ALL PROGRAMS -> MICROSOFT SQL SERVER -> CONFIGURATION TOOLS ->SQL SERVER SURFACE AREA CONFIGURATION. Now select Surface Area Configuration for services and connection. Now expand the MSSQLSERVER tree and the database engine tree it these are not already expanded.Now select the remote connection under the database engine and select the radio button for local and remote connetions. Under that select Using both TCP/IP and Named Pipes. Restart the sqlserver services and the sqlbrowser services. The connection will be ready for use in remote connection.

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now