HOW TO ACCESS MS SQL IN WINDOWS SERVER FROM A REMOTE SQL SERVER MANAGEMENT STUDIO

arunvk (25)in #sqlserver • 6 years ago

It is always troublesome to access MS SQL running in windows server from a location system. In this post I am explaining how to do it easily in less time. The main things to be done are first we need to allow to remote access to the sql server we are using.Then set a port to access the sql server through TCP/IP connection. Next step is to create an inbound rule in firewall to accept connection to that port. You can follow the steps given below to do this.

Connect with windows server
Open sql server management studio. And connect to the sql server instance.
Right click on the SQL server instance and select properties.
Select "Connections" from side menu. Make sure the "Allow remote connections to this server" Checkbox is selected. then press OK.

Open SQL server configuration manager
In the side menu expand "SQL server network configuration"
Double click on the SQL instance name.
Ensure that "Shared memory" an TCP/IP are enabled. If not right click and enable it.

Double click on the TCP/IP. A popup with tow tabs will appear.
Open tab "IP addresses" and scroll down to IPAII section. clear "TCP dynamic ports" section. add port number to "TCP port" section and Apply changes.

Now select Sql server services from side bar. From the services list restart the SQL Server instance to apply changes.
Now open "firewall with advanced security" from control panel.
Select Inbound rules from side panel. and select "New Rule" from right side.
First in Rule Type select "Port"
In protocol and ports select TCP" protocol. And enter your port number below after selecting specific ports
Select all three options (Domain, Private, Public) under Profile
In the "Name" section give a name and description to the rule then finish.

Now everything is set. Close the window server now in the local system open sql server management studio in server name enter IP address of windows server and the port number separated by a coma(,) and enter username and password.