1

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?

1 Answer 1

0

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

3
  • So it turns out that once you create a user with an AD account, and associate a USER to that LOGIN, they DO have access. I'm not sure why it wasn't working for me. Some type of weird caching issue is all I can figure out. May 24, 2022 at 17:34
  • I thought you wanted to create a contained user (without login in the Master database). May 25, 2022 at 15:47
  • 1
    Ah, I understand. I didn't want to create USERs in the master database. Logins I could understand, because they can't do anything in master other than connect. I appreciate you comment regarding contained databases, but there was something else strange going on that seems to have been gremlins in the system. May 25, 2022 at 20:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.