Windows LOCAL groups
Job details
Name: |
Windows LOCAL groups |
Platform: |
Sqlserver |
Category: |
Security |
Premium package: |
Security and compliance package |
Description: |
Checks that Windows LOCAL groups are not SQL Logins. |
Long description: |
Checks that Windows LOCAL groups are not SQL Logins. These groups should not be used for any level of access into a SQL Server Database Engine instance. |
Version: |
1.2 |
Default schedule: |
33 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 Windows LOCAL groups is a SQL Login. |
disable login |
NO |
If set to “YES“ the alert will disable the login by running “ALTER LOGIN [login_name] DISABLE“. |
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 ensure that Windows LOCAL groups are not configured as SQL Logins in a SQL Server Database Engine instance.
- Why: This job is crucial to maintain security protocols by ensuring that local Windows groups, which should not have database access, are not mistakenly given SQL login rights. This prevention helps mitigate unauthorized access and potential security breaches.
- Manual checking:
- To manually check this information, you can issue the following SQL commands:
SELECT pr.[name], pe.[permission_name] FROM sys.server_principals pr JOIN sys.server_permissions pe ON pr.[principal_id] = pe.[grantee_principal_id] WHERE pr.[type_desc] = 'WINDOWS_GROUP' AND pr.[name] LIKE CAST(SERVERPROPERTY('MachineName') AS nvarchar) + '%'
Implementation Details
- The job involves creating tables to store current statuses and historical data regarding Windows LOCAL groups acting as SQL logins.
- Procedures are implemented to insert, update, and clean these tables based on the current status and history threshold.
- SQL Cursors are used to iterate over server principals to identify any LOCAL groups configured as SQL Logins.
- An option exists within the parameters to disable the SQL login if it is incorrectly set up as a LOCAL group.
- Historical data management is incorporated through parameters which dictate the longevity of the recorded data.
Dependencies and Code Structure
- Main Dependencies:
- “dbw_windows_local_groups_proc” (Type 2 – Main procedure)
Reporting and Visualization
- Report Template v2:
- Title: “Windows LOCAL groups”
Upgrade and Compatibility
- The job contains upgrade scripts ensuring compatibility and correct functionality with updates.
- Designed to work with Microsoft SQL Server versions greater than 2005 and includes compatibility for Azure SQL Managed Instances.
- A compatibility query ensures that the job is deployed only on suitable SQL Server instances to prevent execution errors.
- Express Installation: False
- Force Installation: True
Compliance and Security Framework
- This job aligns with access control compliance by ensuring that only appropriately authorized groups and users have SQL login capabilities, safeguarding against potential unauthorized access that violates corporate security policies.