MS SQL ServerMS SQL Server SQL Windows Server 

How to configure SQL server to access it remotely

How to configure SQL server to access it remotely?, Actually this is not a big issue there are some straightforward steps to configure, but there might be a possibility of giving problem after configure using the same steps also. So go through the below steps carefully.

To establish a successful remote connection is to set up ports through the Window Firewall. In SQL Server there are two types of instances. First is a default instance and the second is a named instance. To connect to the default instance of Database Engine, or named instance that is the only instance installed on the machine, the TCP port 1433 is the only port that you need to specify.

But if you have multiple named instances installed on your machine, to connect with one of them, we must provide a port number which corresponding to appropriate instance name. By default named instance used a dynamic port, which means that every time when the Database Engine starts new port number is assigned. Because of that, it is difficult to configure Windows Firewall to enable access.

In order to resolve this problem, there is a SQL Browser service, which provides the TCP port number that corresponds to the named instances. The SQL Browser services use UDP port 1434. Now we met with the basic matters relating to the remote connection, let’s continue with the setup procedure.

Enabling TCP/IP protocol

The first step is to enable TCP/IP protocol on the SQL Server service. Open the SQL Server Configuration Manager in the Configuration Tools folder under the Microsoft SQL Server folder:

Also Read:  How to Auto Restart SQL SERVER when its crashed?

From the SQL Server Network Configuration node, select the Protocols item for the appropriate SQL Server. In the details pane, right-click the TCP/IP item and select the Enable option:

Note: After this please check whether the TCP/IP Ports are configured properly or not, for that before configuring inbound rules below check this step. In some SQL installation by default takes 1433 as TCP port if not change all to IP TCP Port Rules to 1433 as shown below:

Right click on “TCP/IP”  and select properties:

In Properties put 1433 as TCP port and make dynamic port as blank to all IP properties from IP1 to IPAll as Shown Below:

After this step, the Warning box pop up in which informs us that changes that are made won’t take effect until the next time service is started.

For the changes to take effect, from console pane, select the SQL Server Services and from the details panel right-click the SQL Server (SQLEXPRESS) database engine instance and click the Restart option:

Now the service started with TCP/IP enabled, but still can’t connect remotely until we configure the Windows Firewall.

Click on Page 2 to continue

Arjun is is a web developer/designer, who is fond of the web. He lives in Manipal, Karnataka, India.

Related posts