CREATE LOGIN for AD user in master not allowing connection

Question

I am struggling to figure out the best method to set up security on an Azure SQL Managed instance. First, I tried to set up SQL logins, but since MUST_CHANGE isn't supported, I would then have access to everyone's password when I created the login, clearly an unacceptable solution. I then made myself the AD admin, and set up AD accounts. If I CREATE USER <user_name> FOR EXTERNAL USER in the master database, people can connect, but if I CREATE LOGIN <user_name> FOR EXTERNAL USER and then perform the CREATE USER commands in the other dbs linking to the login, people still get a login error.

Since I cannot define a DEFAULT_DATABASE on the CREATE LOGIN command, I am really confused about the security context. Am I really supposed to create users for everyone in the master database? That seems like a security hole. Am I missing something here?

Answer 1

You will need to change the containment option first before you can create a contained user in Azure SQL Managed Instance. It is done by default for the Azure SQL database.

Azure SQL Managed Instance behaves like SQL Server on-premises in the context of contained databases. Be sure to change the context of your database from the master database to the user database when creating your contained user. Additionally, there should be no active connections to the user database when setting the containment option.

Example code:

USE MASTER;
GO 

ALTER DATABASE Test
SET RESTRICTED_USER
WITH ROLLBACK IMMEDIATE;

ALTER DATABASE Test
SET containment=partial;

ALTER DATABASE Test
SET MULTI_USER;

USE Test;  
GO 

CREATE USER Carlo  
WITH PASSWORD='Enterpwdhere*'  

SELECT containment_desc FROM sys.databases
WHERE name='Test'

Reference: Contained Database Users - Making Your Database Portable