Instance Authentication Mode
Job details
| Name: | Instance Authentication Mode | |
| Platform: | Sqlserver | |
| Category: | Security | |
| Premium package: | Security and compliance package | |
| Description: | Checks if the Server Authentication property is set to ‘Windows Authentication Mode’ or ‘Mixed Mode’ authentication. | |
| Long description: | Checks if the Server Authentication property is set to ‘Windows Authentication Mode’ or ‘Mixed Mode’ authentication. | |
| Version: | 1.2 | |
| Default schedule: | 14 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 “Windows Authentication Mode” is not in use. |
| history threshold | 365 | The maximum number of days to keep statistics for in the historic tables. |
Job Summary
- Purpose: This job monitors the Server Authentication property in MS SQL Server to check whether it is set to “Windows Authentication Mode” or “Mixed Mode” and records historical changes.
- Why: Monitoring the authentication mode is crucial for maintaining security and compliance within an IT infrastructure. If the system is not set to the desired authentication mode, unauthorized access could occur, leading to potential security breaches.
- Manual checking: To manually check the current authentication mode, use the following SQL command:
SELECT CASE WHEN CAST(SERVERPROPERTY('IsIntegratedSecurityOnly') AS INT) = 1 THEN 'Windows Authentication Mode' ELSE 'Mixed Mode' END AS "Instance Authentication Mode"Job Description
The monitoring job, defined within the dbWatch framework, involves several key operations:
- Checking Authentication Mode: The system checks the current authentication mode using the ServerProperty function.
- Recording Historical Data: Changes in the authentication mode are recorded. This includes updating tables designed to store the current status and historical changes.
- Cleansing Historical Data: The system maintains historical data for a specified number of days (default is 365 days), after which older data is purged to prevent excessive data accumulation.
Implementation Details
The job includes the following SQL structures:
- Tables: Two tables are created to store the current authentication mode and its history:
- dbw_instance_authentication_modes_info (current status table)
- Procedure: A stored procedure named dbw_instance_auth_mode_proc is implemented to:
- Check the current authentication mode.
- Indices: Clustered indices on the history date are created for both tables to optimize querying and data management.
Dependency and Cleanup
This job has dependencies on:
- Main procedure: The dbw_instance_auth_mode_proc performs the main execution logic.
- Status and History Tables: Dependencies include the current status and historical data tables where data cleanup is triggered on job failure to maintain data integrity.
Reporting and Visualization
The job setup includes a reporting template under the title “MS SQL Server Authentication Mode” with detailed presentations:
- Instance Authentication Mode: Presents the current mode in a table format.
- Last Day Configuration History: Displays changes in configuration over the last day in graph format.
- Aggregated Statistics: Shows statistics on how often each authentication mode has been checked, visualized through a bar graph.
Job Execution and Parameters
The job execution is conditional based on the existence of specific server properties and version constraints. It targets instances where Microsoft SQL Server or Azure SQL Managed Instance is present, and skips where not applicable.
This monitoring job enhances both operational visibility and security compliance by automating the tracking and reporting of authentication modes in MS SQL Server environments.