Azure SQL Database - Elastic Jobs - Getting a Failed Login

Question

I am in the process of testing a migration from SQL Server on an Azure VM to an Azure SQL Database and have scheduled jobs that need to be reproduced on the Azure SQL Database so configured Elastic Jobs as per the information contained in Kate Smith's series of blogs (https://techcommunity.microsoft.com/t5/azure-sql-blog/elastic-jobs-in-azure-sql-database-what-and-why/ba-p/1177902) and the series of Data Exposed videos (https://www.youtube.com/watch?v=JIMgqkXZFOQ)

As this is a POC, I did not set up the Elastic Job Agent on a different server but otherwise performed all of the steps detailed in the blog posts, however, when attempting to run a job I am receiving a login failed error and I cannot figure out why, and this particular issue is not covered in Kate's troubleshooting.

Any help would be greatly appreciated as right now this is a show-stopper.

TIA

Michael MacGregor

Answer 1

When you create a Job it requests for credentials, username and password, make sure the username exists and the password is correct. Make sure the credential with the appropriate rights exist on the target database you are trying to execute the job. See this documentation about credentials for executing jobs.

You can update the Job to use a different credential, using PowerShell's Set-AzureSqlJob or you can update the credential if needed using Set-AzureSqlJobCredential. For more information how to use PowerShell with Elastic Jobs please visit this documentation.

Answer 2

As I mentioned I followed the steps documented in Kate Smith's blog posts which follow the documentation outlined in the pages you referenced.

Answer 3

Figured it out. The job user credential secret contained a $ character so I recreated the credential using a secret not containing $ and it works fine now.