I am having a interesting issue which is kind a hybrid between SQL server 2017 (Server A) and Azure SQL Managed Instance (Server MI).
I have a SQL agent job. Within inside the job I have a step that runs a SSIS package that extracts data hosted on Server MI. Server MI was on a VM SQL Server but the database has now been migrated to a Azure SQL managed instance. The job step set in the previous setup ( when Server MI was hosted on a VM) was running under sql server service account. I had given the service account readonly permission on the database hosted on SQL Server on VM MI and the step was executing just fine.
My issue is how can I give the permission to run this setup when the database is hosted on server MI which is no longer in VM model but on Azure SQL Manged Instance.
service account ( AD ID ) is not supported on Manged Instance but rather only VIA Azure active directory ID.
Any other way to run the SSIS VIA agent job without requiring the agent service account? Any one can recommend any other alternate is much appreciated.
Thanks in advance