Cannot see other databases in SQL Server Managed Instances using Azure AD user

Question

we are running PoC on SQL Server Managed Instance but it doesn't seem to be walk in the park and we're hit some blockers. Hoping some of you may have resolved this already and can share some experiences :)

Our Business Acceptance Criteria:

• Users must be able to run standard SQL queries- OK
• Users must be able to correlate data between databases on cloud-based server- BLOCKED
• Users must be able to access from and to on-prem linked servers- BLOCKED
• Users must be able to access data with existing tools
• SSMS - OK
• Spotfire- OK
• PowerBI - BLOCKED, AAD user doesn't work!
• Excel - BLOCKED, AAD user doesn't work!
• Tableau - Pending

Given I have SQL Server Managed Instance with these setup

• SQL Managed Instance, General Purpose
• MI on private VNet, we have Azure AD synced with our ON-PREM AD
• Lifted two databases DB1, DB2 from ON-PREM instances
• MI Containment = Disabled, DB1 and DB2 Containment = None
• Mapped existing user groups from Azure AD (AAD) DB_ADM_USERS
• Granted DBO role on group DB_ADM_USERS on each database DB1, DB2
• Granted DBO role on group DB_ADM_USERS on MASTER
• Added linked servers to ON-PREM instances using option #4 Security Context with remote user and password
• I have USER1 who is part of AAD Group DBM_ADM_USERS
• USER1 connects via latest version of SSMS
• USER1 connects using Active Directory - Integrated

Questions: When user logs in Uses Active Directory - Integrated

1. If USER1 doest not specify databases in connection, we get connection error

Cannot connect to xxxx.xxxx.database.windows.net. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)

2. If USER1 specifies the database to connect to such as DB1

User can successfully connects, DB1 objects are accessible User CANNOT see DB2 is not accessible and all other objects including the Linked Servers

3. If USER1 specifies the database to connect to such as DB2

User can successfully connects, DB2 objects are accessible User CANNOT see DB1 is not accessible and all other objects including the Linked Servers

Thanks in advanced - Ardi

Answer 1

What you are seeing here is by design and my guess is things will change with future releases. Behind the scene managed instance still uses same authentication model as Azure SQL Database.

Managed Instance supports Azure AD authentication as cloud alternative to Windows authentication. The link will take you to the page where it explains Use Azure Active Directory Authentication for authentication with SQL. Which is for Azure SQL Database and SQL Data Warehouse.

If USER1 does not specify databases in connection, we get connection error

Azure Active Directory authentication requires database users to be created as contained database users. A contained database user based on an Azure AD identity, is a database user that does not have a login in the master database, and which maps to an identity in the Azure AD directory that is associated with the database. The Azure AD identity can be either an individual user account or a group.

This article explains about Contained Database Users.

This section of Books Online clearly explains that you will need to mention database name (Because it is a contained user) in your connection string.

Azure AD limitations related to Managed Instance:

• Only Azure AD admin can create databases, Azure AD users are scoped to a single DB and do not have this permission

• Database ownership:

• Azure AD principal cannot change ownership of the database (ALTER AUTHORIZATION ON DATABASE) and cannot be set as owner.

• For databases created by Azure AD admin no ownership is set (owner_sid field in sys.sysdatabases is 0x1).
• SQL Agent cannot be managed when logged in using Azure AD principals.
• Azure AD admin cannot be impersonated using EXECUTE AS
• DAC connection is not supported with Azure AD principals.

• These system functions return NULL values when executed under Azure AD principals:

  SUSER_ID() SUSER_NAME(<admin ID>) SUSER_SNAME(<admin SID>) SUSER_ID(<admin name>) SUSER_SID(<admin name>)

For now as a workaround you will need add your user into an AD group and make that group an administrator. I agree that is not ideal as you are giving more privilege then you need. But there is no other way to solve your problem with current build.

Answer 2

If USER1 doest not specify databases in connection, we get connection error Cannot connect to xxxx.xxxx.database.windows.net. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)

As per MSDN BOL documentation here

Reason:

Generally the user does not have permission to connect to a database (i.e. Azure AD user has not been granted CONNECT permission to a database he tries to connect to.

Solution:

Please check user connect permission

For further your ref Quickstart: Azure SQL Database: Use SQL Server Management Studio to connect and query data , How to create a linked server to an Azure SQL database and Azure SQL Database and SQL Data Warehouse access control