Cannot Create Contained Database on Azure SQL Managed Instance

Question

In my Azure SQL Managed Instance, I'm unable to create CONTAINED databases and use ordinary logins and passwords with them (not Azure AD users).

In SSMS > New Database > Options, the Containment type option is grayed out.

If I create a database with T-SQL:

CREATE DATABASE [Test] CONTAINMENT = PARTIAL

creation succeeds but containment always shows as none and I cannot create users.

I did enable contained database like so:

EXEC sys.sp_configure N'contained database authentication', N'1'
GO
RECONFIGURE WITH OVERRIDE 
GO

and even restarted the instance.

And SSMS, [Instance Name] > Properties > Advanced > Containment > Enable Contained Databases is True.

I also verified that creating users - exactly as described in the documentation (see the "Managed Instance" section) - fails:

 Create user [TestUser] With password = 'Test.12345' 

fails with the message

You can only create a user with a password in a contained database.

Microsoft says:

Azure SQL Managed Instance behaves like SQL Server on-premises in the context of contained databases.

However this either is clearly not the case or I'm missing some configuration setting somewhere.

---

Update 2023-03-15

I killed the instance and created a new one, this time selecting ONLY SQL Server Authentication as an option at the instance-level, rather than mixed-mode authentication.

I was finally able to use contained databases with SQL logins but I first had to enable contained databases for the instance like I normally would. Then I was able to create the database with CONTAINMENT=PARTIAL and use CREATE USER normally (although oddly the database still shows as Containment: None in SSMS).

Would appreciate if someone in the know could confirm if the reason it wasn't working before was because I enabled mixed-mode authentication. Also it seems the answer below is incorrect insofar as saying that containment doesn't have to be explicitly enabled. But what I would really like is clarification so that we know how to configure instances properly in the future. Clearly there is some erroneous or at minimum undocumented behavior going on with respect to contained databases - which my application relies on extensively for row-level security - in a managed instance. Thanks.

Answer 1

I believe I found the issue. The documentation is very vague on this but it appears that in the statement:

CREATE DATABASE [MyDatabase] CONTAINMENT = PARTIAL

the CONTAINMENT = PARTIAL line is indeed ignored. This doesn't appear to be documented anywhere and is likely a bug in the Azure infrastructure.

I have to manually set:

ALTER DATABASE [MyDatabase] SET CONTAINMENT = PARTIAL;

after creation, which I would not normally have to do in an on-prem SQL instance.

After this I can finally use CREATE USER.

This is frustrating as I now have to do a code update to support a managed instance. If anyone is aware of a better workaround I would be glad to hear it.

Answer 2

You don't need to change that setting to create contained database users: SQL Managed Instance supports Azure AD database principals with the syntax CREATE USER [AADUser/AAD group] FROM EXTERNAL PROVIDER. This feature is also known as Azure AD contained database users.

There is this note in the doc: Create a user account in the database to which a user needs access (also called a contained user).

With SQL Database, you can always create this type of user account.

With SQL Managed Instance supporting Azure AD server principals, you can create user accounts to authenticate to the SQL Managed Instance without requiring database users to be created as a contained database user.

Hope that helps.