1

I have been looking at the managed instance link feature. Specifically, using the DAG feature to replicate a database on SQL Server 2019 Enterprise Edition, to SQL Managed Instance.

I have hit a wall.

  • SQL Server 2019 does not support Azure AD logins, only SQL Server 2022 does at the time of writing, and we are not licensed for v2022 :(
  • Our end users (non-sysadmins) access the Azure SQL Managed Instance using their Azure AD logins only.
  • The replication of the database using managed instance link, from SQL 2019 to MI is one way only, so the SQL MI has only a read only copy of the database (so cannot add \ alter users there).
  • The database users on the read\write version of the DB on SQL 2019 are in Domain\User format, which then means that on the SQL MI those same users are not in the required format, [email protected], and there is the problem - users on the MI have no access to the read-only copy with their Azure AD logins.

If I want to keep managed instance link, I can't see how to get around this apart from our end users using SQL logins (where I would then script out a created matching SID).

Has anyone got any other thoughts that I might not have considered?

1 Answer 1

0

Has anyone got any other thoughts that I might not have considered?

Create AAD Logins for the users and grant those logins CONNECT ANY DATABASE and SELECT ALL USER SECURABLES.

Or only grant them CONNECT ANY DATABASE and put views in a read-write database that refer to tables in the read-only database.

3
  • Thanks very much for your help. I think this could work for our setup and needs. I will test and come back.
    – NoNoYepNo
    May 26, 2023 at 14:50
  • Hi. I thought this through from the security perspective, around CONNECT ANY DATABASE and SELECT ALL USER SECURABLES. This would be fine for those AAD Users as they could have those wider permissions. That permission set was fine for one SQL MI but not the other.
    – NoNoYepNo
    Jun 7, 2023 at 9:41
  • Additionally, all went well enough with test DB's on the same platforms, but then I encountered a problem setting up the managed link for my Prod DB. It never restored the databases over on the MI and restoring just looped. The error was not transparent to me as it was on the Azure side, but Azure found in logs the error, we had TDE prior to the MI link and although that was removed it failed on the MI side. So I had to abort and never got MI link into Prod. Leaving that here in case anyone finds that info useful as MI link is so new.
    – NoNoYepNo
    Jun 7, 2023 at 9:42

Your Answer

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

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