Standard ports
Job details
| Name: | Standard ports | |
| Platform: | Sqlserver | |
| Category: | Security | |
| Premium package: | Security and compliance package | |
| Description: | Verify the usage of standard ports, with TCP port 1433 being the default. | |
| Long description: | Verify the usage of standard ports, with TCP port 1433 being the default. Conducting a comprehensive examination of the active port configurations is a crucial step in ensuring a robust and secure network infrastructure. | |
| Version: | 1.23 | |
| Default schedule: | 29 1 1 * | |
| Requires engine install: | Yes | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’ & eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’ | engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name | Default value | Description |
|---|---|---|
| return status | 1 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when ports listed in parameter “not permitted ports” are in use. |
| not permitted ports | 1433 | List of not permitted ports. |
| history threshold | 365 | The maximum number of days to keep statistics for in the historic tables. |
Job Summary
- Purpose: The purpose of this job is to monitor and verify the usage of standard and non-standard TCP ports, specifically focusing on TCP port 1433, which is commonly used by SQL Server.
- Why: This job is important because ensuring only authorized ports are used can prevent unauthorized access and potential breaches. Monitoring port usage assists in maintaining compliance and security standards.
- Manual checking: You can check this manually in the database by issuing the following SQL commands:
SELECT local_tcp_port, count(*) FROM sys.dm_exec_connections GROUP BY local_tcp_port ORDER BY 2 DESCJob Details
- Object Type: The monitoring involves objects such as stored procedures and tables that handle data regarding port usage and history.
- Description: The job includes creating tables to store current port usage and historical data, as well as a procedure to manage the retrieval and logging of TCP port usage information.
Implementation
The tasks performed by the job implementation are as follows:
- Creation of two tables:
- “dbw_standard_ports_info” to store current port and process count.
- “dbw_standard_ports_histr” for historical port usage data.
- A procedure named “dbw_standard_ports_proc” is created to:
- Retrieve and record the current port usage.
- Summarize historical data.
- Conditionally raise alerts if unauthorized port usage is detected.
Dependencies
This job depends on the following database objects for its operation:
- Main procedure (dbw_standard_ports_proc), which handles most of the logic for port monitoring.
- Two support tables:
- “dbw_standard_ports_info” – Temporary storage for current data.
- “dbw_standard_ports_histr” – Holding historical data. Cleanup occurs based on the defined threshold.
Reporting
- The “dbwatch report template” section describes how the output data is to be formatted and presented:
- Reports will display current port usage and historical port statistics.
- Visual representations such as graphs for easier interpretation of the port’s usage over time.
Scheduling
- The job is scheduled to run periodically, as defined by the “default schedule” in the XML. It checks for port usage and updates the tables accordingly.
- Standard monitoring setup involves periodic checks to keep data up-to-date and provide timely alerts.
Upgrade Path
- The upgrade section elaborates on changes to the procedure between versions, maintaining backward compatibility and updating logic as necessary.
Compliance and Security
- This job also ties into security and compliance frameworks, ensuring that the database configuration adheres to defined compliance standards related to port usage.
In conclusion, this monitoring job is crucial for maintaining the security and compliance of the SQL Server environment by tracking and managing port usage effectively.