Remote admin connections
Job details
| Name: | Remote admin connections | |
| Platform: | Sqlserver | |
| Category: | Security | |
| Premium package: | Security and compliance package | |
| Description: | Checks whether a client application on a remote computer can use the Dedicated Administrator Connection (DAC). | |
| Long description: | Checks whether a client application on a remote computer can use the Dedicated Administrator Connection (DAC). | |
| Version: | 1.2 | |
| Default schedule: | 24 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 the “remote admin connections” parameter is enabled. |
| return status if failover cluster | 0 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when Server instance is configured in a failover cluster regardless of enabled “remote admin connections” parameter value. |
| disable remote admin connections | NO | If set to “YES“ the alert will disable “remote admin connections” (if it is enabled) by running sp_configure stored procedure. |
| 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 manage remote administrator connections on a SQL server, specifically addressing the use of the Dedicated Administrator Connection (DAC).
- Why: This job is crucial for maintaining the security and integrity of SQL Server instances. It provides oversight on whether the DAC is enabled and offers administrative control to disable it if necessary, thereby preventing unauthorized or potentially harmful remote administrative access.
- Manual checking: You can manually check the status of remote administrator connections in the database using the following SQL commands:
SELECT value_in_use FROM sys.configurations WHERE name = 'remote admin connections'Detailed Description
- This monitoring job is a part of the “Security” category, focusing on the “remote admin connections” status.
- It includes a default schedule for checks, set to run monthly.
- The job uses procedures and tables to track and manage the status of remote admin connections:
- Tables created:
Dependencies and Implementations
- The job includes dependencies that are essential for its execution:
- Main procedure `dbw_remote_admin_connections_proc`
Output and Reporting
The job report provides insight into:
- Current configuration: Displays current settings of remote admin connections.
- Configuration history: Visualizes changes over the last day to track how parameters have been adjusted.
- Aggregated statistics: Shows summarized data on how frequently the configurations have been checked or altered.
Upgrade Scenarios
- The upgrade includes altering the existing procedure to adapt to new checks or business requirements, ensuring that the job remains relevant and efficient.
Compliance and Security
- The task aligns with security compliance focusing on “Access Control”, ensuring that only authorized operations are permitted on critical administrative features within SQL Server environments.
Overall, this monitoring job is critical for organizations looking to uphold strong security practices concerning remote administrative access in SQL Server instances. It automates checks and actions, reducing the need for manual intervention and increasing the responsiveness to potential security risks.