Sql-server – Azure Managed Instance – Is it possible to send Sql Logs To Log Analytics

I shared the case scenario with the Azure SQL Database team at Microsoft via an Exchange Mailing list for Azure SQL Databases contributors/MVPs. The answer provided by one of the managers of the team is the following: "Yes, transactional replication will be the best way at this point if they want to keep the replication running after the migration to Managed Instances."

Hope this helps.

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.