0

BACKGROUND

  • I have created a Linked Server on an Azure Managed Instance and secured it to specific logins only.
  • I have a SQL Agent job that wants to use the Linked Server.

QUESTION How do I grant the Azure MI SQL Agent execution account the permission to use the Linked Server?

RESULTS In on premises SQL Server I would add the service account I assigned to SQL Agent as a linked server login using proc sp_addlinkedsrvlogin. However, my SQL Agent service account appears to be [User Manager\ContainerAdministrator] (REF 1). If I try adding that login using the above proc I get the error 'User Manager\ContainerAdministrator' is not a valid login or you do not have permission.'.

When my SQL Agent jobs tries to use the linked server I get this error as expected : Executed as user: User Manager\ContainerAdministrator. Access to the remote server is denied because no login-mapping exists.

WORK AROUNDS

  1. Remove security on the Linked Server and let every login use it. This is unacceptable from a security stand point.
  2. Move my SQL Agent job off the Managed Instance onto an regular installation of SQL Server where I can grant the SQL Agent execution account permission to use the linked server. Refactor the job to write the results back to the Managed Instance using a second linked server. This is what I think I need to do but it is disappointing as our research on Azure Managed Instance indicated that SQL Agent and Linked servers were supported, just not at the same time apparently.

REF 1 : https://johnmccormack.it/2020/09/how-do-i-find-the-agent-service-account-for-azure-sql-database-managed-instance/

Improve this question

2 Answers 2

Reset to default
0

Have you considered configuring the SQL Agent Job step in question to run as SQL login for which you already configured appropriate permissions on the Linked Server?

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for the suggestion, SQL Chess. I did try that but I still got the same error message referencing the SQL Agent service account even though the job was to be run as the specified login. I assume the impersonation does not extend to accessing linked server. Given that Azure SQL MI is always changing I will retest your suggestion.
0

Had a similiar issue but not using "Linked Servers". On two out of three of our SQL Managed Instances the SQL Jobs randomly started executing the SQL Jobs as: Executed as user: User Manager\ContainerAdministrator. I have SQL jobs that when they execute EXEC msdb.dbo.sp_send_dbmail it would fail with the following error: Message Executed as user: User Manager\ContainerAdministrator. Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050). The step failed. No clue again why it is executing as this year (not even anythign setup for it). If I add this line of code and create a Login for it, it works:
execute as login = 'SQLAgentUser'

Improve this answer

1 Comment

Hi DanielP, I too incurred a few of those "Failed to initialize sqlcmd library" errors on SQL Agent jobs that we migrated from older servers. I found that by dropping the job and recreating it the errors no longer occurred. Some of those older SQL Agent jobs could have come from SQL2000, although most came from SQL2012 migrated to SQL2019 then to Azure SQL MI.

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.