Contained database credentials

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and manage authentication practices within contained SQL server databases by checking if database users are using database-specific authentication (authentication_type = 2).
• Why: This job is important because using USER WITH PASSWORD authentication method moves the authentication boundaries from the Database Engine to the database level, which may introduce security weaknesses and risks. Monitoring this helps in maintaining database security and compliance.
• Manual checking: You can check this manually in the database by issuing these SQL commands:

SELECT name, authentication_type FROM sys.database_principals WHERE type_desc = 'SQL_USER' AND authentication_type = 2;

Detailed Explanation

• This task validates contained database authentication modes and assesses any potential security risks posed by users with database-only authentication, which can circumvent server-level security configurations.
• The script specifically looks for users where the ‘authentication_type’ is set to 2, indicating database-level authentication, across all contained databases that are online and writable.
• If the parameter ‘revoke CONNECT‘ is set to “YES“, the script will revoke the ‘CONNECT’ permission from the users it identifies, increasing security by disallowing database access to these users.

Report Presentation

• A report is generated listing detailed information about the contained database users:
  • Names of the databases and users
  • Whether they have database access
  • Schema associated with each user
  • How many times each user was checked
  • The last date each user was checked

Maintenance Operations

• The task creates two main types of records:
  • “dbw_contained_db_credentials_info” to store the current status of users.
  • “dbw_contained_db_credentials_histr” to maintain a historical log of user checks, with data being pruned based on a history threshold to manage data growth effectively.

• The system also handles errors efficiently, providing detailed error messages in the output, helping in quick debugging and resolution of issues.

Conditions and Dependencies

• The task has several dependencies on various service components and database structures to function correctly. It ensures these components are present and will clean up if any step fails to prevent corrupt data or partial updates.
• The job is applicable only to SQL Server instances later than SQL Server 2008 and includes particular setups such as Azure SQL Managed Instance or instances with specific configurations.

Compliance and Security

• The job addresses the compliance requirement tagged as “Access Control”, ensuring lawful and secure access management within the SQL Server databases.
• Scheduled to run periodically, it supports the enforcement of internal security policies and audit requirements by providing a systematic review of user authentication mechanisms across instances.

By continuously monitoring and optionally adjusting the rights of users who might be using less secure authentication methods directly at the database level, this task plays a crucial role in fortifying the security posture of SQL Server environments managed via dbWatch Control Center.