Remote Connection

Remote Connection

Please read the following carefully to get an idea on how to access SQL Server remotely with no extra cost using the Internet and eliminating all costs for cloud at the same time. The process is very simple once you get the idea behind it. Kindly save this document in OneDrive for your future references.

General

A computer can have one or more IP (Internet Protocol) addresses depending on the number of network cards (physical) installed. IP address is used to identify a network card in a network of computers. Most computers have only one card and therefore can have only one IP address. But big servers can have many.

All programs communicating over a network have to use the same network card (same IP) attached to the computer. Therefore another software concept called Port, which is a number, is used to find the final destination, the program. So the combination of IP number and Port enables one to one communication between programs over a connected network of computers.

For example, 198.65.1.56 could be the IP address of the network card of your computer and 456 could be the port number of a program, like Google client, running on your computer. So the combination 198.65.1.56, 456 can be used to communicate with Google client instances running on your computer. On the other hand Google Server IP and its Port Number are fixed and well known. A service called DNS service can resolve any external well known IP/Port combination from its english names like Google, Facebook etc.

LAN (Local Area Network) : This is a private network of computers and the IP number for each card is assigned by the network administrator. Once assigned these numbers will not change until the network administrator edits it later.

Internet : Here the IP address is assigned to your network card by your ISP (Internet Service Provider) from a pool of numbers assigned to them (ISP) to make your IP address unique on the Internet. The programs running on your computer use this dynamic IP number and assigns a Port number, separate for each program, to communicate with the external world. This is useful when your program communicates with servers (Google, Facebook, Twitter etc.) whose numbers are well known. But you can not make your computer as a server of some services since your IP address changes with every restart and therefore can not publish your IP address. But you can make your IP address permanent by changing some settings in your modem.

To find the current IP address of your computer type COMMAND in cortana and then type ipconfig at the command prompt. A number in the line starting with IPv4 is your current dynamically assigned IP address. Mine is now 192.168.1.65. I am using this number to communicate with the external world.

You can communicate with my computer over the internet with this IP address and a valid Port of a program running on my computer so long as I do not restart my computer and I have set the firewall to enable the communication through this IP,Port combination. The protocol by which programs communicate over the internet is called TCP/IP Protocol. This is the crux of computer connectivity in a nutshell.

SQL Server

For using the services of SQL Server running on one computer by others on LAN or Internet, you have to take the following simple steps which enables SQL Server TCP/IP communication. It assigns Port numbers to SQL Server service running on your computer.

Type SQL Server Configuration in cortana and select SQL Server Configuration Manager from the list and start it.

Select SQL Server Network Configuration > Protocols for SQL

Right Click on TCP/IP and select Enable. Now SQL Server is enabled to communicate over a network using TCP/IP Protocol.

Next you have to assign a Port Number to SQL Server running on your machine. Right click again on TCP/IP and select Properties. Select IP Addresses tab and scroll to bottom for IPAll. I have given port numbers as 44444. You can give something different. Note that if there is another existing instance with its port number, give a port number that's different from the one used in the pre-existing instance. This may not be needed as there will only be the one we create in new client’s systems.

Click on Apply and then OK. Select SQL Server Services and right click on SQL Server Browser

Now you have to make sure that SQL Server and another related service called SQL Server Browser are automatically started on every computer restart. Click on SQL Server Services > SQL Server or SQL Server Browser and select Properties from the context menu. Select Service tab. Select Start Mode as automatic. Click on Apply first then on OK. Please make sure that you have done these on both the services.

Once you have finished, restart or start services (SQL Server and SQL Server Browser) by right clicking on it and then selecting the correct option from the context menu to make the changes made effective.

Now your SQL Server is available from your computer to others on the same network (internet) on your assigned IP and Port number (44444).

All that remains is to enable connection on this IP/Port combination using windows firewall.

Windows Firewall

Type Firewall in cortana and start Windows Defender Firewall. Select Advanced Settings in the left pane.

Now you have to add a new Inbound Rule and an Outbound Rule. Select Inbound Rules in the left pane and click on New Rule in the right pane. This opens the ports for outside connections. We are opening all ports in this example, but you can be specific (44444) to be more secure.

Select Port and click on Next. Select TCP and All Local Ports.

Click on Next. Select Allow the connection and again click on Next.

Click on Next again and give a name. I gave SQL. Click on Finish.

Do similarly for a new Outbound Rule. Now the firewall is also ready. The SQL server running on your computer is now accessible from any computer on the internet.

Using Core

Start Core in another computer (client) connected to the same network to access SQL Server running in the first computer (now the server).

Open the Lead Accounting Plus application in the new computer from which you are accessing the SQL Server running in the first computer (server).

Now when the application opens for the first time this is the screen that will appear. Click the Connect to Server button.

The following was copied in the connection string field and it worked.

Both computers are connected to the same WiFi modem and there is no physical connection between them. So they are communicating through the modem and both computers have now become part of the Internet with dynamically assigned IP numbers. Since the server system does not have a permanent IP number the connection will be lost on the restart of the server.

The connection string used is

Data Source=192.168.1.65\SQL,44444;User Id=sa;Password=Ram

It is divided into three parts using ;. Each part is a Key/Value pair.

Data Source is the IP Address (it is dynamically assigned in this case) and port number of the SQL Server. 192.168.1.65 is the IP address of the server machine and SQL is the name of the SQL instance running on that computer. By the way you can have different versions of SQL Server running, though rare, in parallel in the same computer. Hence the instance name (SQL or SQLSERVER or whatever) is important to connect to the desired server instance. Yours could be SQLEXPRESS, the only instance running. In the case above SQL was chosen as the server instance when installing SQL Server. 44444 was the Port number configured when installing the SQL Server. Do note that if there are multiple instances on your system, give a separate port number for each instance. That way you can connect to the correct server without fail.

User Id is sa , the default, and Password is Ram which was chosen at the time of installation of SQL Server. If you cannot remember them you can use certain commands to create new user ID’s and passwords.

Once you get the idea, you can install SQL Server on any computer on the internet and access it from any other computer, also connected to the internet, from anywhere.

This helps you to avoid cloud systems altogether and the related expenses. Everything is free except the internet connection which is anyway required to get connected.