question

AzureSDE-3924 avatar image
1 Vote"
AzureSDE-3924 asked SaurabhSharma-msft edited

User-assigned managed identity and sql failover

Our Azure SQL database is configured with active/passive geo-replication between the primary and secondary regional datacenter. The primary SQL database belongs to a resource group that is in the West US and the secondary SQL database belongs to another resource group which is in the East US.

Creating a user-assigned managed identity requires a resource group and we were wondering how a user-assigned managed identity would work in a failover scenario. Would a user-assigned managed identity created under a resource group in the West US still work with the secondary SQL in the East US when there is a failover from the primary to the secondary?

azure-sql-databaseazure-managed-identity
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @azuresde-3924,

Thanks for using Microsoft Q&A !!

I believe you need to provide user-assigned managed identity access to the secondary server and it should work in failover. I haven't tested this but I will check and update you on the same.
How are you using a user assigned managed identity ? Are you using this in web app to access the SQL instance ?

Thanks
Saurabh

0 Votes 0 ·

1 Answer

SaurabhSharma-msft avatar image
0 Votes"
SaurabhSharma-msft answered SaurabhSharma-msft edited

Hi @azuresde-3924,

I have tested this with user-assigned managed identity for a published web application and it works only when you provide add this user assigned managed identity to secondary server (using CREATE USER [appserviceuser] FROM EXTERNAL PROVIDER) during the failover time.
So, basically you need to provide access to your failover server to make this work.

This even works if the user managed identity resides in a different resource group than the secondary SQL instance.
Please let me know if you have any questions.

Thanks
Saurabh


Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @azuresde-3924,

I have not heard back from you. Did my answer solve your issue? If so, please mark as accepted answer. If not, please let me know how I may better assist.

Thanks
Saurabh

0 Votes 0 ·

Hi @azuresde-3924,

Please let me know if you find above reply useful. If yes, do click on 'Mark as answer' link in above reply. This will help other community members facing similar query to refer to this solution.

Thanks,
Saurabh

0 Votes 0 ·

@SaurabhSharma-msft - Thank you. Our DevOps engineer has confirmed the approach you recommended.

0 Votes 0 ·
Show more comments