Configuring SQL Server to allow remote connections is a common requirement for distributed applications and centralized database management. By default, a fresh installation of Microsoft SQL Server listens only on the local machine, blocking external network traffic for security reasons. This guide walks through the necessary steps to enable secure and reliable remote access, ensuring your database server is reachable from other machines.
Prerequisites and Initial Checks
Before modifying the SQL Server configuration, verify the network environment and service status. The SQL Server instance must be running, and the machine hosting it should have a stable network connection with appropriate firewall rules. Ensure that the account used for configuration has administrative privileges on the server. You should also determine the specific SQL Server instance name and the port it is configured to use, typically 1433 for the default instance.
Verify the SQL Server Service
Open the Services management console (services.msc) and locate the SQL Server (MSSQLSERVER) service for the default instance or a named instance. Confirm that its status is "Running" and that its startup type is set to "Automatic". Restarting the service after enabling remote connections can help apply the new network settings correctly.
Enable Protocols via SQL Server Configuration Manager
The SQL Server Configuration Manager is the primary tool for managing network connectivity settings. It allows you to activate communication protocols and manage the listening ports for your database engine. Enabling TCP/IP is the most critical step for standard remote connections over a network.
Activate TCP/IP and Configure IP Addresses
In the Configuration Manager, expand the "SQL Server Network Configuration" section and select "Protocols for [Instance Name]". Right-click on "TCP/IP" and choose "Enable". Then, navigate to the "IP Addresses" tab. Scroll down to the "IPAll" section and ensure that "TCP Port" is set to 1433. It is good practice to explicitly set the "TCP Dynamic Ports" field to blank to prevent the server from assigning a random port on restart.
Adjust Windows Firewall Settings
Even with SQL Server configured to listen on a port, the Windows Firewall will block incoming traffic unless an explicit rule is created. This step is often the cause of failed connection attempts after enabling protocols.
Create an Inbound Rule for Port 1433
Open Windows Defender Firewall with Advanced Security and create a new inbound rule. Select "Port" and specify "TCP" with the specific local port "1433". Set the action to "Allow the connection" and ensure the rule applies to Domain, Private, and Public profiles. Assigning a descriptive name, such as "SQL Server Remote Access," helps with future maintenance and troubleshooting.
Configure SQL Server Authentication Mode SQL Server supports two authentication modes: Windows Authentication and Mixed Mode. Windows Authentication relies solely on domain credentials, while Mixed Mode allows SQL Server logins with usernames and passwords. Remote clients often require SQL Server authentication to connect independently of the Windows domain structure. Enable Mixed Mode Authentication Run the SQL Server Management Studio (SSMS) while connected to the server using Windows Authentication. Right-click the server root in Object Explorer, select "Properties," and navigate to the "Security" page. Under "Server authentication," select the "SQL Server and Windows Authentication mode" option. Click "OK" and restart the SQL Server service to enforce the new security policy. Create a Login for Remote Users
SQL Server supports two authentication modes: Windows Authentication and Mixed Mode. Windows Authentication relies solely on domain credentials, while Mixed Mode allows SQL Server logins with usernames and passwords. Remote clients often require SQL Server authentication to connect independently of the Windows domain structure.
Enable Mixed Mode Authentication
Run the SQL Server Management Studio (SSMS) while connected to the server using Windows Authentication. Right-click the server root in Object Explorer, select "Properties," and navigate to the "Security" page. Under "Server authentication," select the "SQL Server and Windows Authentication mode" option. Click "OK" and restart the SQL Server service to enforce the new security policy.
Enabling remote access is meaningless without granting permissions to specific users. You must create a SQL Server login and associate it with a user in the target database. This ensures that only authorized personnel can access the data.
