As developers, we often prefer to write SQL code on our local machines because it offers faster execution and more control over the data. However, there may be situations where we need to develop using a centralized database hosted on a remote server. In this article, we will discuss the steps to configure remote access to a SQL Server instance and connect to it from a developer’s machine using SQL Server Management Studio (SSMS).

Allowing Remote Connections

The first step is to configure the SQL Server instance to allow remote machines to connect to it. Follow these steps:

1. Right-click on the SQL Server instance name and select “Properties”.
2. Select “Connections” on the left-hand pane.
3. Under “Remote Server Connections”, check the box next to “Allow remote connections to this server”.
4. Leave the default value for the “Remote query timeout” as 600.
5. Click “OK”.

Add a Remote User to the Database

Once the SQL Server is configured, we need to allow specific users to connect and access the database objects. Follow these steps:

1. Connect to the SQL Server instance as a server admin.
2. Expand “Security” and right-click on “Logins”.
3. Select “New Login”.
4. In the “Login – New” dialog box, click “Search”.
5. Type the username in the object name text box and click “Check Names”.
6. Keep the authentication mode as Windows Authentication.
7. Select “Server Roles” on the left-hand page and select the checkbox next to “Public”.
8. Navigate to “User Mappings” and select the database on which the user needs access to.
9. Assign any specific role as security demands.
10. On the “Status” page, select “Permission” as “Grant” and “Login” as “Enabled”.
11. Click “OK”.

Configuring the SQL Server Instance

Now that the database instance is configured for remote connections, we need to allow remote traffic on the server. Follow these steps:

1. Select “SQL Server 2016 Configuration Manager” from the Start menu.
2. Navigate to “Protocols for <<YOUR SERVER NAME>>” under “SQL Server Network Configuration” on the left-hand pane.
3. Make sure that the TCP/IP Protocol Name is enabled.
4. Right-click on the TCP/IP Protocol Name and select “Properties”.
5. Navigate to the “IP Addresses” tab and scroll down to the section named “IPAII”.
6. If the TCP Dynamic Ports is set to 0 (indicating the Database Engine is listening on dynamic ports), remove the 0 and set it to blank.
7. Update the value for TCP Port to 1434 (the default port used by the SQL Server Database Engine).
8. Click “OK”.
9. Restart the SQL Server service by right-clicking on the SQL Server (Instance Name) and selecting “Restart”.

Configuring Windows Firewall

Once the database instance is configured to accept remote connections, we need to add an exception in the Windows Firewall for the port (1434) to allow TCP/IP traffic on this port. Follow these steps:

1. Click “Start” and select “Administrative Tools”.
2. Double-click “Windows Firewall with Advanced Security”.
3. Click “Inbound Rules” on the left-hand panel.
4. Click “New Rule” under “Actions” on the right-hand panel.
5. Select the “Port” option under “Rule Type” and click “Next”.
6. Under “Protocols and Ports”, select “TCP” and specify the local port as “1434”.
7. Click “Next”.
8. Select “Allow the connection” under “Action” and click “Next”.
9. Select the appropriate options under “Profile” depending on whom you want to allow access to the database instance.
10. Click “Next”.
11. Provide a name for this new rule and click “Finish”.

Fetching the Connection Details

Now that everything is set up properly to allow inbound traffic over the firewall, we are ready to connect to the remote machine. Follow these steps:

1. Open Command Prompt and type “ipconfig”.
2. Copy the IPv4 Address.
3. Open SQL Server Configuration Manager.
4. Select “SQL Server Services” on the left-hand panel and copy the name of the instance of the database (only required for named instances).

Connecting to the Remote Machine

Once the remote server is configured, we can connect to the SQL Database Instance from any other PCs. Follow these steps:

1. Open SQL Server Management Studio.
2. Provide the Server name in the format “<<REMOTE MACHINE IP>>\<<INSTANCE NAME>>”.
3. Choose the appropriate authentication mode (Windows Authentication or SQL Server Authentication).
4. Click “Connect”.

Now you should be able to browse all the databases that the user has been granted access to. By following these steps, you can configure remote access to a SQL Server instance and connect to it from your local machine.

For more information, you can refer to the official documentation provided by Microsoft: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-access-server-configuration-option.