Configuring SQL Server to Connect to AE and CDA DB

This section describes how to unlock port 1433 to connect to the AE & Continuous Delivery Automation databases.

To Configure SQL Server

  1. Enable remote connections.

    1. Open SQL Management Studio.
    2. Right-click the server name and select Properties.
    3. Select Connections in the left pane.
    4. Ensure that the Allow remote connections to this server checkbox is selected.

  2. Configure SQL to listen on a static port.

    1. Open SQL Server Configuration Manager.
    2. Click SQL Server Services in the left pane.

    3. In the central pane, look for the process Id of the SQL Server.
    4. To identify the port this process ID is listening on, type the following command in the command prompt:

      netstat -ano | find /i “PID-Number-Of-SQL-Server”

      Example:

      netstat -ano | find /i “120”

    5. If no results are returned, ensure that the TCP/IP protocol is enabled. To enable it:
      1. Click SQL Server Network Configuration in the left pane.
      2. Select Protocols for <instance_name>.
      3. Right-click the TCP/IP protocol.
      4. Select Enable.
    6. Restart SQL Server service.
    7.  Identify the new process ID assigned to the SQL Server.
    8. Type the following command in the command prompt:

      netstat -ano | find /i “PID-Number-Of-SQL-Server”.

      Example:

      netstat -ano | find /i “9154”

    9. Select SQL Server Network Configuration in the left pane.
    10. Right-click the TCP/IP protocol and select Properties.
    11. Open the IP Addresses tab.
    12. In the IPAll section, replace the value displayed for TCP Port with 1433.
    13. Restart SQL Server service.

    14. In the command prompt execute the following command to check if SQL has been configured correctly:

      netstat -ano | find /i “3948”

  3. Enable SQL Server Browser Service.

    1. Open SQL Server Configuration Manager.
    2. Click SQL Server Services in the left pane.
    3. Right-click SQL Server Browser service and select Properties.
    4. Open the Service tab.
    5. Display the drop-down list by Start Mode and select Automatic.
    6. Click Apply.
    7. Click Start in the SQL Server Browser Properties window to start the SQL Browser service.

  4. Configure the firewall to allow SQL-related network traffic.

    1. Click Advanced Settings in the left pane. The Windows Firewall with Advanced Security window is displayed.
    2. Click Inbound Rules.
    3. Click New Rule in the Actions pane.
    4. Select the Port option.
    5. Select TCP.
    6. Select Specific local ports and enter 1433 in the field.
    7. Select Allow the connection.
    8. Select all options (Domain, Private & Public).
    9. Enter a Name for the rule.
    10. Click Finish.
    11. Click New Rule in the Actions pane.
    12. Select the Port option.
    13. Select UDP.
    14. Select Specific local ports and enter 1433 in the field.
    15. Select Allow the connection.
    16. Select all options (Domain, Private & Public).
    17. Enter a Name for the rule.
    18. Click Finish.
    19. Click New Rule in the Actions pane.
    20. Select the Program option.
    21. Select This program path and click Browse.
    22. Navigate to this location: [C:\Program Files\Microsoft SQL Server\MSSQL11.<INSTANCE_NAME>\MSSQL\Binn\sqlservr.exe] and select sqlservr.exe.
    23. Select Allow the connection.
    24. Select all options (Domain, Private & Public).
    25. Enter a Name for the rule.
    26. Click Finish.
    27. Click New Rule in the Actions pane.
    28. Select the Program option.
    29. Select This program path and click Browse.
    30. Navigate to this location: [C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe] and select sqlbrowser.exe.
    31. Select Allow the connection.
    32. Select all options (Domain, Private & Public).
    33. Enter a Name for the rule.
    34. Click Finish.