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
andDB2
Containment = None - Mapped existing user groups from Azure AD (AAD) DB_ADM_USERS
- Granted DBO role on group
DB_ADM_USERS
on each databaseDB1
,DB2
- Granted DBO role on group
DB_ADM_USERS
onMASTER
- 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 GroupDBM_ADM_USERS
USER1
connects via latest version of SSMSUSER1
connects using Active Directory – Integrated
Questions:
When user logs in Uses Active Directory – Integrated
- 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)
- 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
- 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
Best Answer
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 forAzure SQL Database
andSQL Data Warehouse
.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:
Database ownership:
Azure AD principal cannot change ownership of the database (ALTER AUTHORIZATION ON DATABASE) and cannot be set as owner.
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.