Contained databases in SQL Server offer a convenient way to move databases between servers by containing authentication information for the database users. However, it is important to be aware of the security considerations when working with contained databases.
Authentication Process
When using SQL Server authentication against a contained database, the connection attempt must specify the initial catalog as the contained database. Authentication is first attempted against contained users. If the user does not exist, SQL Server falls back to server level authentication. It is important to note that if a contained user and a SQL account in the master database share the same username, connection attempts can fail.
For Windows authentication against a contained database, an initial catalog must also be specified. Authentication is first attempted at the server level. If no matching login or group exists in the master database, SQL falls back to database level authentication. If a contained user with a matching Windows account or group name does not exist in the database, it is considered an authentication failure.
Security Considerations
When working with contained databases, there are several security caveats to be aware of:
- Delegation of access control: Database containment delinks server administration from database maintenance to a certain extent. Administrators should carefully delegate the ALTER ANY USER privilege to contained users, as they can add other users.
- Guest account access: Contained users can access other databases where a guest account is enabled. To prevent this, ensure the guest account is disabled for all user databases.
- Duplicate logins: In cases where SQL authentication is used, a contained user with a different password but the same name as their login ID can intentionally or accidentally cause a Denial of Service to that login. Windows authentication is attempted first at the server level, so it is not as severely affected.
- Password policies: Users with passwords in contained databases cannot take advantage of password policies, making it harder to enforce password lifetimes and history requirements.
- AUTO_CLOSE setting: Contained databases marked for AUTO_CLOSE can significantly increase the cost of authentication, potentially making Denial of Service attacks easier. It is recommended to avoid setting AUTO_CLOSE for contained databases.
It is important for database administrators to be aware of these security considerations when working with contained databases. Regular auditing of users in contained databases is also recommended to ensure proper access control.
As we conclude this blog, we hope that these security nuances surrounding contained databases have been brought to your attention. While not extensively documented or widely known, understanding these considerations is crucial for maintaining a secure SQL Server environment.