Remote Access Of The Database Using SQL Server

• Go to Windows firewall as I have Windows 10 in my case.
• Control Panel\All Control Panel Items\Windows Firewall.
• Click “Advanced Setting”.
  
• Click InBounded Rules and click New Rule
  
• Select the port and click next.
  
• Select TCP radio select the specified port and give the name of the port, which is “1433” in my case. You can set any port which is not reserved by the system.
  
• Click the next action tab but don’t change anything. Use the default and click next in the profile. Make sure, we select all the checkboxes and then click next. Give identity to it by naming it and clicking Finish.
  
• Again, click on the new rule and select the “Port” radio click, next click the “UDP” radio, click the specified port, and assign 1434, which is the default port for UDP in SQL Server. Click next in the Action pane but don’t change anything. Click next in the profile tab and make sure all the checkboxes are checked and click next to give it to the “SQL Server UDP port” and click finish.
  
• Again, click on the new rule at that time and select the program followed by clicking next.
  
• Give the complete program path, where we add the “SQLbrowser.exe”, which is located at that path in my case.
• C:\Program Files (x86)\Microsoft SQL Server\90\Shared
  
• Click next but don’t change anything. Click next to make sure all the checkboxes are checked and then click next to assign the name “SQL server browser agent” and click finish.
  
• Again, click the new rule and select the program. This time, we add the SQLsrvr.exe, which is located at C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Bin
  
• Click next but don’t change anything, followed by clicking next to make sure you have checked all the check boxes on the Profile tab.
• Click next, name it “SQL server exe” and click finish.
• Make sure you have 4 new rules in the firewall.
• Your work is done. Set the port enabled to the TCP/IP protocols to the Server and add 2 ports 1433-TCp and 1434-UDP and 2 programs SQLbrowser.exe and SQLsrvr.exe.

Step 5

• For the port we use the canyouseeme.org.This Web detects the IP to auto enter the port 1433.
  
• It gives an error because it is unable to listen to port 1433 on the respective ID and we know how to resolve it.
• In Pakistan/the rest of the world, many ISPs provide internet facilities but the issue is that they don’t assign the “Static-ip” address on every restart of the router, as these ISPs provide different IP addresses, which is annoying most of the time.
• It is the PTCL (Pakistan Telecommunication Company Ltd) that provides the static-iP 500 per month.
• We can check the ISP static-IP services, according to the country which provides the cheap rates (if for production purposes) but here, I demonstrated how to access remote databases.
• Go to router settings. In my case, I have a PTCL router. Hit 192.168.1.1 and 192.168.1.X and in the case of any other brands of routers, the user needs to pass and click OK.
• Find Out the “NAT” services and in my case:
  
• Click add and then under custom services, it gives the name like SQL Server TCP.
• Open run type CMD and enter “ipconfig”. Note the IP address
  
• We can add UDP, which follows the same procedure.
• After adding NAT, we again opened the canyouseeme.org and checked the port at the time we listened to port 1433.
  
• With all the steps shown now, we are able to log in successfully.
• The connection string format will be: IP-Address/InstanceName, PortNumber;
• In my case, it is182.185.184.158\MSSQLSERVER,1433;
• Make sure to log in with SQL Server authentication.
  
• Click Connect, to log in successfully.

Conclusion

By followings the steps listed above, we are able to access the SQL Server database remotely but for production use, we must buy the static-IP asby using the static-IP, we can host the Web on the local PC, make FTP Server, and we can host the database also.