How can I host SQL Server in an office network for multiple workstations to connect with?

Troy Wolbrink — Thu, 01 Sep 2022 00:59:57 GMT

Current revision posted to Frequently Asked Questions by Troy Wolbrink on 9/1/2022 12:59:57 AM

DonorWise was developed with the possibility that multiple workstations in an office networked environment to be able to work on the same database at the same time. The central point of this is a computer that will be running SQL Server. This computer is called the "server".

Once you have a server setup with SQL Server, and a DonorWise database has been created on this SQL Server, there are some steps to make it possible for others in the office to connect to this database from their computer as well.

Remote Connections enabled

SQL Server must be configured to allow "Remote Connections". In SQL Server Management Studio, connect to your instance of SQL Server. Right-click on the server node and click on "Properties". In the Connections section, check the box to enable Remote Connections.

Discover Listening Port and Enable Firewall

In SQL Server Configuration Manger, find your instance of SQL Server, and make sure that TCP/IP protocol is enabled. Within the TCP/IP configuration, under the IP Addresses tab, scroll down to "IP All". Take note of TCP Dynamics Ports and TCP Port.

If TCP Port is blank, but TCP Dynamics Ports is not blank: You'll need to add a Firewall inbound rule to allow for dynamic ports:

To open access to SQL Server when using dynamic ports

On the Start menu, select Run, type WF.msc, and then select OK.
In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then select New Rule in the action pane.
In the Rule Type dialog box, select Program, and then select Next.
In the Program dialog box, select This program path. Select Browse, and navigate to the instance of SQL Server that you want to access through the firewall, and then select Open. By default, SQL Server is at C:\Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\Binn\Sqlservr.exe. Select Next. The MSSQLXX version will be specific to your version of SQL Server.
In the Action dialog box, select Allow the connection, and then select Next.
In the Profile dialog box, select any profiles that describe the computer connection environment when you want to connect to the Database Engine, and then select Next.
In the Name dialog box, type a name and description for this rule, and then select Finish.

If TCP Port is not blank, you'll have to open up port that static port (usually port 1433)

To open a port in the Windows firewall for TCP access

On the Start menu, select Run, type WF.msc, and then select OK.
In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then select New Rule in the action pane.
In the Rule Type dialog box, select Port, and then select Next.
In the Protocol and Ports dialog box, select TCP. Select Specific local ports, and then type the port number of the instance of the Database Engine, such as 1433 for the default instance. Select Next.
In the Action dialog box, select Allow the connection, and then select Next.
In the Profile dialog box, select any profiles that describe the computer connection environment when you want to connect to the Database Engine, and then select Next.
In the Name dialog box, type a name and description for this rule, and then select Finish.

SQL Browser service requirement

If you're using a named instance (like SQLEXPRESS), you'll also need to also ensure the SQL Browser service is enabled and running. Also, you'll need to enable an inbound rule to allow UDP port 1434 on your firewall.

Restart SQL Server

At this point, you'll need to restart your SQL Server instance, so that it can bind to the appropriate TCP/IP port and prepare for listening for connections.

Take Note of Server Name and Ping it from the Workstation

On the server, open up a Windows Command Prompt and run the following command:

hostname

This will tell you what the server name is. From the user's workstation, run this command:

ping <hostname>

This test will confirm if the workstation can see the server via TCP/IP or not. If it can, you're ready to start DonorWise and login to the database on the server.