Issue running a SQL agent job setup against a database hosted on Azure MI

• BoobyB

  SSCertifiable

  Points: 5470

  More actions

  January 14, 2020 at 8:49 pm

  #3715412

  Hi All,

  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

   

• Ant-Green

  SSC Guru

  Points: 113445

  More actions

  January 15, 2020 at 12:06 pm

  #3715568

  There are ways and means to sync your local AD domain credentials up to Azure AD and allow a fully bridged authentication route.

  If that's something that's not in place, the alternative would be to use a SQL login instead for the authentication to the MI server