Enable Network Access to SQL Express

Once you have SQL Express set up on your local computer, you can allow remote connections for members of your network. There are different ways to do this and these steps may not work for your existing network environment or authentication methods. Below is a simple approproach for SQL Server Express Edition that is set up on a local computer and SQL Server authentication is used for members of the same network to remotely connect.

Security & Connections

Open SQL Server Management Studio (SSMS)
Connect to your server and right click your server and click Properties.
Go to the Security page and for Server Authentication, select SQL Server and Windows Authentication mode.
Then, go to the Connections page and ensure that "Allow remote connections to this server" is checked and click OK.

SQL Server Authentication

Now that we've ensured your server is set to allow remote connections, you can set up a login for the server and the specific Calibration Control database (apecal). The following steps will explain how to create a universal SQL Server Authentication login for all Calibration Control users. (You can create individual logins using SQL Server Authentication or Windows Authentication if that works best for your work environment.)

First, expand the Security folder under your server
Then, right click the Logins folder and select New Login...
Type in a generic user name that all users will use, such as apeuser.
Select SQL Server Authentication.
Enter a strong password. It is entirely up to you if you want to keep "Enforce password policy" checked, however deselecting it will deselect the other checkboxes below it.
Select your apecal database as the default database.
Next, under the Server Roles, public will automatically be selected and cannot be deselected. You can optionally select additional roles to grant to this user.
Then, open User Mapping and check the apecal database
Under the database roles, select db_datawriter and db_datareader
Next, expand the Databases folder by double clicking it or clicking the plus sign.
Expand your apecal database and then its Security folder
Expand the Users folder and double click the login you just created.
Click the Membership page and double check that the user has the minimum required roles (at least db_datareader and db_datawriter). Click OK.

SQL Server Configuration

Your server is set up to allow remote connections with a SQL Server login but now you must enable TCP/IP protocols for your server.

Open SQL Server Configuration Manager
Expand SQL Server Network Configuration and Protocols for {Your server name}.
Right click TCP/IP and click Enable, then click OK. You'll see a message that the service needs to be restarted before changes take effect.
Open the SQL Server Services and right click your server and Restart. Alternatively, you can restart your server in SSMS by right licking the server name and clicking Restart.

Windows Firewall Configuration

TCP/IP is now enabled on your server. The next step requires allowing specific ports to connect to your server.

Open Windows Defender Firewall with Advanced Security
Click on Inbound rules
Then, select New Rule... located on the right under the Actions menu.
Select Port and click Next.
Select TCP and for Specific local ports, enter 1433. For more information, Microsoft lists other ports used by SQL Server here: https://docs.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?view=sql-server-ver15#ports-used-by-
Continue clicking Next all the way through and specify a name for your rule and Finish.
Next, create a new inbound rule.
Select Custom to create a custom rule and click Next.
Under the Services section, click the Customize... button.
Select Apply to this service and scroll through to select your SQL Server and click OK.
Continue clicking Next all the way through and specify a name for your rule and Finish.

Test the connection

If the SQL Server is hosted on your local computer, the server name is the computer's IP address followed by a comma and the port number from the earlier step. (Example: 10.1.12.13, 1433)

On another user's computer, test the connection to your server by simply using Calibration Control and entering the required SQL Server connection information. If the connection is not successful, try using a UDL file to troubleshoot the connection.

Last Updated: 27 July 2020