Login failed and successful setting
Job details
| Name: | Login failed and successful setting | |
| Platform: | Sqlserver | |
| Category: | Security | |
| Premium package: | Security and compliance package | |
| Description: | Checks if the SQL Server login audit is set to value ‘3′ (none = 0, successful logins only = 1, failed logins only = 2, both failed and successful logins = 3). | |
| Long description: | Checks if the SQL Server login audit is set to value ‘3′ (none = 0, successful logins only = 1, failed logins only = 2, both failed and successful logins = 3). After changing this option the SQL Server Instance must be restarted. | |
| Version: | 1.2 | |
| Default schedule: | 36 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) if the SQL Server login audit is not set to value “3″. |
| history threshold | 365 | The maximum number of days to keep statistics for in the historic tables. |
Job Summary
- Purpose: To monitor and enforce the setting of SQL Server login audits to include both successful and failed logins.
- Why: This setting is critical for security and compliance purposes. Ensuring that both successful and failed login attempts are audited helps in detecting unauthorized access and maintaining robust access control measures.
- Manual checking: You can manually check the current settings for SQL Server login audits by querying the registry or using the SQL Server Management Studio (SSMS) interface.
Implementation Details
- The job involves creating and maintaining tables to store historical data on login audit settings and procedures to check and enforce the desired audit setting.
- It checks the SQL Server configuration to ensure that the login audit is set to audit both successful and failed logins, corresponding to the setting value “3″.
- If the audit setting is found to not be in compliance (not set to “3″), the job updates the status to reflect this, potentially triggering alerts or notifications based on the configuration.
Tables and Operations
- The job uses three primary database objects:
- “dbw_login_failed_and_successful_setting_info” to store the current configuration.
Dependencies and Cleanup
- The job defines dependencies on its main objects to ensure proper execution sequencing and cleanup actions on failure to keep the database environment clean and consistent.
- Cleaning actions are defined to prevent the storage of unnecessary or corrupt data which might result from a failed job execution.
Reporting
- A report template is included to show the current configuration and historical changes of the login audit settings.
- It provides visualization through:
- Presentation of the current setting and the last checked date.
Deployment and Compatibility
- Suitable for SQL Server instances version 2005 and above, including both Microsoft SQL Server and Azure SQL Managed Instance types.
- The job includes upgrade scripts to handle transitions from previous job versions, ensuring that the implementation is current without requiring a full re-installation.
Compliance and Metadata
- Classified under “Access Control” in compliance categories, helping in aligning the job with security governance frameworks that require strict access controls and audit capabilities.
- Ensures that database instances that meet the job’s compatibility criteria are automatically considered for monitoring, based on their configuration and capabilities.