MSSQL Over LAN | rohzart

Setting up Microsoft SQL Server (MSSQL) to work over a Local Area Network (LAN) requires configuring the server and firewall settings properly to allow external devices to connect. Below are the steps needed to enable SQL Server over a LAN:

Step-by-Step Configuration

Enable TCP/IP and Named Pipes in SQL Server Configuration
- Open SQL Server Configuration Manager.
- Navigate to SQL Server Network Configuration > Protocols for [Your Instance].
- In the right pane, enable TCP/IP and Named Pipes. These protocols are necessary for SQL Server to communicate over the network.
Restart SQL Server

After enabling TCP/IP and Named Pipes, you need to restart the SQL Server instance for the changes to take effect.
- In SQL Server Configuration Manager, go to SQL Server Services.
- Right-click on your SQL Server instance and click Restart.
Allow sqlservr.exe Through the Firewall

To ensure that SQL Server can communicate over the network, you must allow the SQL Server executable (sqlservr.exe) through the firewall.
- Open Windows Firewall settings.
- Click on Advanced Settings and then Inbound Rules.
- Choose New Rule and select Program.
- Browse to the location of sqlservr.exe (typically located at C:\Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\Binn\sqlservr.exe).
- Allow this program to communicate on both Private and Public networks.
This ensures that SQL Server can accept incoming connections from other machines.
Configure SQL Server to Listen on Specific Ports (Optional)

By default, SQL Server listens on port 1433 for TCP connections. However, if you need to configure a custom port, follow these steps:
- Open SQL Server Configuration Manager.
- Under SQL Server Network Configuration, right-click on TCP/IP and select Properties.
- Go to the IP Addresses tab.
- In the IPAll section, configure the TCP Dynamic Ports and TCP Port settings.
Be sure to update the firewall rule to allow the new port if it’s different from the default.
Test Connectivity Over the LAN

Once the above steps are completed, you should be able to connect to your SQL Server instance from another computer on the LAN using the following connection string format:

Server=IPAddress\InstanceName; Database=YourDatabase;
User Id=YourUser; Password=YourPassword;

Replace IPAddress with the IP address of the SQL Server.
Replace InstanceName with the SQL Server instance name.
Make sure that the client machine has the appropriate SQL Server client tools installed.

Understanding SQL Server Network Configuration

When configuring SQL Server to communicate over a network, you should be aware of several concepts that could impact your setup:

1. SQL Server Protocols

SQL Server uses several protocols for communication, including TCP/IP, Named Pipes, Shared Memory, and Via. By default, TCP/IP is the most common protocol used for remote connections, which is why it needs to be enabled in SQL Server Configuration Manager.

Named Pipes is used for local area networks and is a connection-oriented protocol used in environments where TCP/IP may not be available.

2. Dynamic Ports vs Static Ports

SQL Server can use either dynamic ports or static ports for communication:

Dynamic Ports are assigned automatically by the operating system, and you typically don’t need to configure anything.
Static Ports are manually assigned, allowing you to specify which ports the SQL Server will listen on (often used when setting up more controlled network configurations).

It’s essential to allow the relevant ports through the firewall and ensure that any external clients are configured to use the correct port.

3. SQL Server Authentication Modes

There are two authentication modes in SQL Server:

Windows Authentication: Uses Windows accounts for login.
SQL Server Authentication: Uses SQL Server-specific accounts.

To allow remote connections, ensure that the SQL Server authentication mode is configured correctly. You can check this under SQL Server Management Studio (SSMS) by right-clicking the server instance and choosing Properties > Security.

4. Verifying Connectivity

To verify whether SQL Server is accepting remote connections, use the SQL Server Management Studio (SSMS) to attempt a remote connection. If the connection fails, check your firewall settings, ensure that SQL Server is listening on the correct IP/Port, and verify the authentication credentials.

Additional Resources

For further troubleshooting, refer to the SQL Server documentation.