How to connect to an SQL server over the internet?

Question

We are 3 people, and have started learning SQL. Now, we want to apply it on MS SQL server management studio. We understood that one of us needs to manage the server, and two of us need to be the clients. Is there a guide how to implement this MS SQL server management studio step by step?

Answer 1

hello @LienAntim

The recommended way to do this is to set up a VPN between the two sites. The steps to do this vary depending on the particular firewall/router being used. If a VPN cannot be used, the following information is provided for how to set up a connection with port mapping. If port-mapping is used, it should only be used in conjunction with additional firewall rules to restrict what source IP addresses can route through the mapped port so that every hacker on earth doesn't have access to your SQL Server.

1. Server IP Configuration
   Check your Windows Server's IP configuration by running IPCONFIG / ALL. Your results can contain both IP addresses, but at least your internal IP:
   . 192.168.1.24
   . 95.218.133.168
2. Check your routing
   Start a traceroute with the following command: TRACERT <external IP of server>. Verify that you receive the route to your server.
3. Check your SQL Server IP Configuration
   In the SQL Server Configuration Manager open up the branch for SQL Server-network configuration and select your instance. (E.g. Protocols for 'MSSQLSERVER')
   Right-click
   and open up the properties. Verify that you don't have any settings that could inhibit a connection. Close the setting when you have finished.
   In the right-hand pane for the protocol settings verify that the TCP/IP protocol is 'Enabled'.
   Now right-click the TCP/IP setting and open up the properties. In the Protocol tab...
   a) Verify again that Enabled is set to Yes.
   b) Verify that the setting Listen All is set to Yes.
   c) The screen should look like this:
4. Check your router configuration

Your public IP is not the server's, it's the router's. If your server is behind the router then you have to ensure your router is forwarding the request to your SQL Server. This configuration setting can vary from router to router. Here a few examples for configuring routers:

• Port Forwarding (Zyxel)
• Setting up static port sharing (Fritz AVM)
• How do I configure Port Forwarding on my router? (D-Link)

============================================

If the answer is helpful, please click Accept Answer and up-vote, this can be beneficial to other community members.

Answer 2

You can all 3 install developer edition locally and do everything you need to do.

Answer 3

Hi @LienAntim ，

If you want to use SSMS to manage your SQL Server instances, you can directly download SQL Server and SSMS separately, and then open SSMS to connect SQL Server by the account(default is sa) and password you set when you install SQL Server

If you need to connect to your SQL Server over the Internet, the safest and preferred method is to setup a VPN connection between the SQL Server and the clients that need to access it via the Internet

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Some points of clarification:

• SQL Server Management Studio (SSMS) is only a tool that can be used to manage SQL Server instances or databases. Installing SSMS does not provide you with a database to connect to or any way to really learn T-SQL.
• • For all 3 of you to learn together on a common database then one of you needs to create the SQL Server / database and then grant access for the others to use it.
• • This can be complex if you want to install SQL Server on your own computer at home, as often inbound connections are not created for home networks.

Therefore, I would suggest that the easiest way to have a common database is for one of you to create an Azure subscription. There are options to get a database at no cost when you create a new subscription. The way to create your free account is documented here.

Once you have that, then create an Azure SQL Database. I would suggest that when you create it you tell it to use the sample AdventureWorks database. The QuickStart instructions are here. I would suggest however that you create the database using the DTU option and set it to Basic tier. This will minimise your costs. Also ensure that you enable the Public Endpoint so that you can all connect from SSMS on your own computers.

At the Firewalls step, also get the public IP Address of your friends' computers and include that in the permitted addresses along with your own.

You will initially have defined an Administrator login and password to use. I would now suggest that you connect to the database using SSMS and then create 3 new users to match each of you. That way you can also start to learn about permissions when you don't all have admin access. Create these as SQL Users at the database scope following the syntax here (NOTE - do not include the USE command but ensure that you are connected to your database when you run the query.

From there you should be good to go.