SQL SERVER – Security Considerations for Contained Databases

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. 

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 (http://blog.sqlauthority.com)

SQL SERVER – How to Migrate Existing Database to Contained Databases

Most of the queries that land into my inbox are based on something I have already written or something people want to explore more on. When it comes to learning and exploring the skills, there is no better way compared to people asking me some tough questions. This blog is an extension to couple of blogs I have already published on a concept called as Contained databases.

SQL SERVER – ‘Denali’ – A Simple Example of Contained Databases

Beginning Contained Databases – Notes from the Field #037

The question asked was, how can we convert an existing database to contained database. What is the process and how do I need to plan? I thought this blog will be a direction in that journey.

There are some initial considerations to determine if there are any existing containment breaches in the existing database. SQL Server makes this easy by providing a DMV and a XEvent to highlight the containment breaches for the database. The complete steps to convert a database to a contained database are as follows:

Identify database containment breaches

There are two tools to help identify the containment status of your database. The sys.dm_db_uncontained_entities (Transact-SQL) is a view that shows all the potentially uncontained entities in your database. The database_uncontained_usage Xevent fires when any actual uncontained entity is identified at run time. Once the containment breaches have been identified and resolved, you are ready to go to the next step as part of migration.

View : sys.dm_db_uncontained_entities

This view shows any entities in your database that have the potential to be uncontained. This includes those user entities that may use objects outside the application model. However, because the containment of some entities (for example, those using dynamic SQL) cannot be determined until run time, the view may show some entities that are not actually uncontained.

1	SELECT * FROM sys.dm_db_uncontained_entities

XEvent : database_uncontained_usage

This Xevent fires whenever an uncontained entity is identified at run time, including entities originating in client code. Since this XEvent will fire for actual uncontained entities at run time, it will not identify any uncontained user entities that you have not run.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

---------------------------------------------------------- -- Setup the Xevent to log into a ring buffer as follows. -- -- Create the event session CREATE EVENT SESSION track_uncontained_usage ON SERVER ADD EVENT sqlserver.database_uncontained_usage (     ACTION(         sqlserver.session_id,         sqlserver.session_nt_username,         sqlserver.client_app_name,         sqlserver.client_pid,         sqlserver.client_hostname,         sqlserver.database_id,         sqlserver.database_name,         sqlserver.sql_text     )     WHERE         sqlserver.database_name='MyDatabase' ) ADD TARGET package0.ring_buffer GO       -- Start the event just created ALTER EVENT SESSION track_uncontained_usage ON SERVER STATE=START GO           -- Dump events logged so far DECLARE @X XML SELECT @X=CAST(XET.TARGET_DATA AS XML) FROM SYS.DM_XE_SESSION_TARGETS XET     JOIN SYS.DM_XE_SESSIONS XE     ON (XE.ADDRESS = XET.EVENT_SESSION_ADDRESS) WHERE XE.NAME = 'track_uncontained_usage' SELECT EVENTS.VALUE('(@timestamp)[1]','datetime') AS EVENT_TIME     , D.VALUE('(@name)[1]','varchar(100)') AS FIELD     , D.VALUE('(value)[1]', 'varchar(max)') AS VALUE FROM (SELECT @X AS RINGBUF ) B CROSS APPLY B.RINGBUF.NODES('//RingBufferTarget/event') AS RB(EVENTS) CROSS APPLY RB.EVENTS.NODES('data') AS EV(D)

Convert the database to a contained database

The steps here are super simple as follows:

1. Using Management Studio
   1. In Object Explorer, expand Databases, right-click the database you need to convert, and then click Properties.
   2. On the Options page, change the Containment type option to Partial
   3. Click
2. Using T-SQL, we take advantage of the new CONTAINMENT option of the ALTER DATABASE command

1 2 3 4

USE [master] GO ALTER DATABASE [Accounting] SET CONTAINMENT = PARTIAL GO

Determine if the containment option has been taken

The sys.databases view has two columns, namely containment and containment_desc, which can be used to determine the containment state of the databases. The following T-SQL will select all databases with containment enabled:

1	SELECT * FROM sys.databases WHERE containment > 0

As I conclude this blog, I hope this will give a rough process for you to work with as you would like to take an existing database to contained database in your environments.

On a side note, would love to know if any of you is using contained databases in your environments? What are some of the usecase for using these? Let me know via your comments.

Reference: Pinal Dave (http://blog.SQLAuthority.com)