SQL SERVER – Security Considerations for Contained Databases

I have written a number of blogs around contained databases in the past which you can search. In today’s blog we will talk about security considerations when working with contained databases. With contained authentication, the database can contain authentication information for the database users. This makes it considerably easier to move databases between servers.

Contained Databases

Some of the other blogs for reference are:

Importance of User Without Login – T-SQL Demo Script

How to Migrate Existing Database to Contained Databases

SQL Authentication process

For SQL Server Authentication against a contained database, the connection attempt must specify an initial catalog, which should be the contained database. If so, authentication is first attempted against contained users. If no such user exists, SQL Server falls back to server level authentication.

If the user exists and the password does not match, this is a typical authentication failure. No second chances are provided. SQL Server does not attempt a server level authentication in this situation. Therefore, if a contained user and a SQL account in master share the same username, connection attempts can fail.

Windows Authentication process

For Windows authentication against a contained database, an initial catalog must be specified. This initial catalog should be a contained database. If so, authentication is first attempted at the server level. If no matching login or group exists in the master, SQL falls back to database level authentication.

If a contained user with matching windows account or group name does not exist in the database, it also is an authentication failure.

Note: The two levels of authentication possible are database level and server level. The order is opposite for SQL Server authentication and Windows authentication. This is important knowledge while troubleshooting login failures.

Contained authentication brings with it some additional security caveats about which a database administrator has to be aware. Here are some of the important considerations:

• Delegation of access control – Database containment delinks Server administration from database maintenance to a certain extent. Administrators need to be aware that contained users with ALTER ANY USER privilege can add other users. This privilege should be carefully delegated. The users in contained databases should be periodically audited.
• Guest account can allow access to other databases – Contained users can access other databases where a guest account is enabled. To avoid this, ensure 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 with the same name as his login ID can intentionally or accidentally cause Denial of Service to that login. Windows authentication is attempted first at server level, so that it is not as severely affected.
• Users with password cannot take advantage of password policies – This makes it harder to enforce password lifetimes and history requirements.
• Contained Database should not have AUTO_CLOSE set – Contained databases marked for AUTO_CLOSE can significantly increase the cost of authentication, possibly making Denial of Service attacks easier.

As I conclude this blog, I wanted to bring out some of these nuances to readers as it is not very well documented or known when working with contained databases.

Reference: Pinal Dave (https://blog.sqlauthority.com)