Prerequisites for Working with IRIS Cluster and Shared SQL Tables

Overview

To work effectively with an IRIS Cluster and manage Shared SQL Tables, certain prerequisites must be met. This guide outlines the necessary configurations for both users and the SQL Server to enable seamless remote connections and manipulation of Shared SQL Tables. Additionally, PowerShell scripts and recommended user permission settings are included for a streamlined setup.

User Access and Permissions

To work with IRIS Cluster and Shared SQL Tables, users must meet the following requirements and configurations:

1. Remote Connection Setup

• Access Permissions: Ensure the user can remotely connect to the SQL Server instance. Proper firewall rules must allow connections to the server's port (default is 1433).
• Note: The SQL Server port may vary based on the server configuration. To confirm the correct port number:
  • Open SQL Server Configuration Manager.
  • Navigate to SQL Server Network Configuration and select Protocols for [Your Instance Name].
  • Right-click TCP/IP and select Properties.
  • Under the IP Addresses tab, check the TCP Port value in the IPAll section.

2. SQL Server Authentication and Mapped Settings

• Enable Mixed Authentication Mode: Allow both SQL Server and Windows Authentication:
  • In SQL Server Management Studio, right-click the server and select Properties.
  • Go to the Security page and select SQL Server and Windows Authentication mode.
• Create Logins and Map Permissions: Follow these steps to configure user logins and database permissions:
  1. In SQL Server Management Studio, expand the Security folder and right-click Logins, then select New Login....
  2. For SQL Authentication:
     • Select SQL Server Authentication, enter a username and password, and uncheck Enforce password expiration.
  3. For Windows Authentication:
     • Select Windows Authentication and browse for the Windows account to map.
  4. Under Server Roles, assign roles as needed (e.g., sysadmin, public).
  5. Under User Mapping, map the login to the desired database and assign appropriate roles:
     • db_owner: Full administrative control of the database.
     • db_datareader: Read-only access to all tables.
     • db_datawriter: Write-only access to all tables.

SQL Server Configuration

To manage the Shared SQL Tables remotely, the SQL Server must be properly configured. The following steps outline the required settings:

1. Enable Remote Connections

Ensure the SQL Server is configured to accept remote connections:

• Open SQL Server Configuration Manager.
• Navigate to SQL Server Services.
• Locate the relevant SQL Server instance (e.g., SQL Server (SQLEXPRESS)) and ensure the service is running.

2. Enable SQL Server Browser

The SQL Server Browser service must be running to allow clients to connect to named instances or dynamic ports:

• In SQL Server Configuration Manager, navigate to SQL Server Services.
• Locate SQL Server Browser.
• Right-click on it and select Properties.
• Set the Start Mode to Automatic and click OK.
• Right-click the service again and select Start.

3. Configure Network Protocols

Enable the necessary network protocols for remote connections:

• In SQL Server Configuration Manager, go to SQL Server Network Configuration.
• Select Protocols for SQLEXPRESS (or the relevant instance name).
• Ensure TCP/IP is enabled.

4. Specify TCP/IP Settings

Adjust the TCP/IP settings for your SQL Server instance:

• Right-click on TCP/IP and select Properties.
• In the IP Addresses tab, ensure the following:
  • IP1 and IPAll sections have the correct TCP Port (default is 1433, or the port confirmed earlier).
  • Set TCP Dynamic Ports to blank.

5. Configure the SQL Server Firewall Rules

Ensure the server’s firewall allows incoming traffic on the SQL Server’s port:

• Open Windows Defender Firewall with Advanced Security.
• Create a new inbound rule:
  • Select Port as the rule type.
  • Specify 1433 (or the port configured for SQL Server).
  • Allow the connection.
  • Apply the rule to the required profiles (Domain, Private, Public).

PowerShell Example: Port Forwarding

To automate firewall configuration for SQL Server's default port (1433), use the following PowerShell script:

# Add inbound rule for SQL Server port 1433
New-NetFirewallRule -DisplayName "SQL Server Port 1433" `
    -Direction Inbound `
    -Protocol TCP `
    -LocalPort 1433 `
    -Action Allow `
    -Profile Domain,Private,Public
    

Conclusion

By completing the above configurations, both the user and the SQL Server will be prepared for efficient remote management and manipulation of Shared SQL Tables in an IRIS Cluster environment. These steps ensure secure and reliable connectivity while maintaining the performance and integrity of the system. PowerShell scripts and clear role-based permissions make this setup efficient and robust.