Disabled sa account
Job details
| Name: | Disabled sa account | |
| Platform: | Sqlserver | |
| Category: | Security | |
| Premium package: | Security and compliance package | |
| Description: | Checks if the ‘sa’ login account (principal_id=1 and sid=0×01) is set to ‘disabled’. | |
| Long description: | Checks if the ‘sa’ login account (principal_id=1 and sid=0×01) is set to ‘disabled’. This is the original login created during installation with sysadmin privileges. | |
| Version: | 1.2 | |
| Default schedule: | 10 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 “sa” login account is enabled. |
| disable sa login | NO | If set to “YES“ the alert will disable “sa” login (if it is enabled) by running “ALTER LOGIN“ DDL command. |
| 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 the status of the “sa” login account in SQL Server to ensure it is disabled. The “sa” account is a high-privilege account created during SQL Server installation and it can represent a significant security risk if left enabled.
- Why: This job is critical to maintaining database security by regularly checking and affirmatively maintaining the “sa” account in a disabled state. If the account is found enabled, the job can also disable it to prevent unauthorized access. Moreover, monitoring changes in its status could detect potential security breaches.
- Manual Checking: To manually check the status of the “sa” login account, issue the following SQL command:
SELECT name, is_disabled FROM sys.server_principals WHERE name = 'sa';Implementation Details
The job involves several key steps, implemented in SQL code:
- A check is performed to determine the current ‘disabled’ status of the “sa” account.
- If the “sa” account is enabled and the corresponding setting is active, a command is issued to disable the account.
- Information on the status of the “sa” account is logged into two tables: dbw_disabled_sa_account_info and dbw_disabled_sa_account_histr.
- Based on configured thresholds, history data older than a specified number of days is cleaned up from the tables to manage historical data effectively.
The dependencies defined in the XML include:
- The main procedure that carries out the checking and logging functionality.
- A table for current status logging.
- A history table to keep track of changes over time.
Reporting
Reporting configurations are also part of the job setup:
- A report template is defined to display the current configuration of the “sa” account, including its status as “enabled” or “disabled”.
- Historical data presentation shows the account’s status over time in both table and graph formats.
- Aggregated daily statistics are displayed graphically, representing the historical data in a summarized form, providing an insight into the frequency and timing of status changes.
The job is scheduled by default to run daily, ensuring constant monitoring and immediate action if an insecure configuration (i.e., the “sa” account being enabled) is detected. This scheduling can be essential for compliance with security policies and standards that require strict management of privileged accounts.
Upgrade and Compatibility
The job also includes an upgrade path from version 1.1 to 1.2, indicating maintenance of the script and possible enhancements for better performance and error handling. The compatibility section specifies that this job is designed for installations running SQL Server version greater than 2005 and includes Azure SQL Managed Instances, ensuring broad applicability across diverse SQL environments.