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

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:

Implementation Details

The job includes the following SQL structures:

Dependency and Cleanup

This job has dependencies on:

Reporting and Visualization

The job setup includes a reporting template under the title “MS SQL Server Authentication Mode” with detailed presentations:

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.