Resolving SQL Server Connection issues between the LoadGen Director and SQL Server

In this support article, we will address SQL Server connection issues involving the incorrect use of ports and how to correctly configure the SQL Server client protocols to ensure proper connectivity.

Problem

When attempting to connect to an SQL Server, the LoadGen Director is experiencing issues due to incorrect port usage. The expected port for SQL Server connections, 1433, is not being used, leading to traffic being denied by the firewall.

Context

  • SQL Server Expected Ports:
    • 1433: This is the default port for SQL Server TCP connections.
    • 445: This port is used for SMB (Server Message Block) over IP, which comes into play if SQL Server uses named pipes protocol.

Solution

Step-by-Step Guide to Configure SQL Server Client Protocols

To ensure that the SQL Server uses the correct protocol and port, follow these steps to configure the client protocols in SQL Server Configuration Manager:

Access SQL Server Configuration Manager

  1. Open SQL Server Configuration Manager.
  2. Expand SQL Server Native Client Configuration.

Adjust Protocol Order

  1. Right-click Client Protocols and select Properties.
  2. In the Enabled Protocols box, ensure TCP/IP is listed at the top:
    • Use the Move Up button to prioritize TCP/IP.
    • Use the Move Down button to deprioritize named pipes or other protocols.

Confirm TCP/IP Settings

  1. Expand SQL Server Network Configuration.
  2. Click on Protocols for [Your SQL Server Instance].
  3. Ensure TCP/IP is enabled.

Verify and Adjust TCP/IP Port

  1. Select TCP/IP, then click Properties.
  2. Navigate to the IP Addresses tab.
  3. Scroll down to the IPAll section.
  4. Ensure the TCP Port is set to 1433.

Check Firewall Settings

Ensure that the firewall on both the management server and database server allows traffic on port 1433:

  • Inbound Rule: Allow traffic on port 1433.
  • Outbound Rule: Allow traffic on port 1433.

Example Configuration

SQL Server Configuration Manager
- SQL Server Native Client Configuration
- Client Protocols
- Properties
- Enabled Protocols:
1. TCP/IP
2. Named Pipes
3. Shared Memory
- SQL Server Network Configuration
- Protocols for MSSQLSERVER
- TCP/IP
- Properties
- IP Addresses
- IPAll
- TCP Port: 1433

Deviate from standard TCP port (1433)

To configure your database connection, use a specific port, such as 1450, and append a comma and the port number directly after the database server name in your connection string. For example:

SQL001,1450

This syntax instructs the client to connect to myServerName on port 1450. It's important to note that the comma (,) separates the server name and port number, not a colon (:).

By default, SQL Server listens on port 1433. Specifying a different port is necessary when your SQL Server instance is configured to use a non-default port. To allow successful connections, ensure that the specified port is open and not blocked by firewalls.

Conclusion

The SQL Server connection issues should be resolved by ensuring TCP/IP is the top protocol and that port 1433 is properly configured and allowed through the firewall. Always prioritize TCP/IP over named pipes for SQL Server to avoid SMB port (445) issues and ensure reliable connectivity.

If you continue to experience issues, please refer to additional documentation or contact technical support for further assistance.

Was this article helpful?
0 out of 0 found this helpful