User-assigned managed identity and sql failover

Question

User-assigned managed identity and sql failover

AzureSDE 116

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?

Saurabh Sharma 23,866 Reputation points Microsoft Employee Moderator

2021-06-16T01:26:30.35+00:00

Hi @AzureSDE ,

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

Answer accepted by question author

0 additional answers

Your answer

Saurabh Sharma 23,866 Reputation points Microsoft Employee Moderator

2021-06-16T01:26:30.35+00:00

Hi @AzureSDE ,

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

Answer 1

Saurabh Sharma 23,866 Microsoft Employee Moderator

Hi @AzureSDE ,

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.

Saurabh Sharma 23,866 Reputation points Microsoft Employee Moderator

2021-06-21T20:25:26.91+00:00

Hi @AzureSDE ,

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
Saurabh Sharma 23,866 Reputation points Microsoft Employee Moderator

2021-06-24T23:33:47.79+00:00

Hi @AzureSDE ,

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
AzureSDE 116 Reputation points

2021-06-25T04:32:36.297+00:00

@Saurabh Sharma - Thank you. Our DevOps engineer has confirmed the approach you recommended.
Saurabh Sharma 23,866 Reputation points Microsoft Employee Moderator

2021-06-25T16:00:27.04+00:00

Hi @AzureSDE ,

Great. Thanks for the confirmation and accepting the answer.

Thanks
Saurabh

Share via

User-assigned managed identity and sql failover

0 additional answers

Your answer