Azure Automation Test Fails with Login Failed for User...

Question

Tried creating an automation account with credentials, proceeded to runbooks, imported runbook from gallery (powershell, reindex-sql-workflow, runtime version 5.1). When I go to test run, I enter parameters: sqlserver - [mycompany].database.windows.net, databasename - [db], and sqlcredentialname - [automationuser]. It always throws the error 'Login failed for 'automationuser'. I know I'm using the correct credential, because if I change the name of that parameter to something else it fails with an error that it doesn't recognize the credential. I've tried removing all components and starting over again, but I get the same error. Is there something I'm doing wrong or is this just a bug?

Answer 1

@IT CDM ELECTRONICS , thank you for posting this question on Microsoft Q&A.

Based on the error mentioned in the question, it clearly seems to be an issue with incorrect password set in the credential asset of Azure Automation. The following points should help troubleshoot it further:

1. As the workflow uses credentials (username and password) in the connection string, it is using SQL Authentication. Therefore, ensure that your sql server has SQL Auth enabled and that you are able to connect to that DB using SQL Auth (the username, password) from SQL client (like sqlcmd, powershell, Sql Server Management Studio etc.)
2. Once you have verified the step 1., I would suggest updating the password of Azure Automation Credential, in case there was a typo when it was created.
3. If you are still facing this issue, you can create a test runbook and use the sample code below to check connectivity (this is similar to the script used in the workflow. However, the credentials here will be shown in plain text for debugging purpose):

#--update the variables below
$SqlServer = '<<<---------SERVER NAME----------->>>'    
$Database = '<<<------DBName------>>'
$SQLCredentialName = '<<<-----CREDENTIAL NAME---->>>'

#---default port
$SqlServerPort = 1433

#---retrieves the credential from Azure Automation Credential Asset
$SqlCredential = Get-AutomationPSCredential -Name $SQLCredentialName
 

 if ($SqlCredential -eq $null)
    {
        throw "Could not retrieve '$SQLCredentialName' credential asset. Check that you created this first in the Automation service."
    }


$SqlUsername = $SqlCredential.UserName 
$SqlPass = $SqlCredential.GetNetworkCredential().Password

#---Display the username and password as plain text - SHOULD BE ONLY USED IN "TEST PANE" RUN
Write-output "SQL User Name $SqlUsername" 
Write-Output "SQL Password $SqlPass"


$connString = "Server=tcp:$SqlServer,$SqlServerPort;Database=$Database;User ID=$SqlUsername;Password=$SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"

#---Display the CONNECTION STRING as plain text - SHOULD BE ONLY USED IN "TEST PANE" RUN
Write-Output $connString


$Conn = New-Object System.Data.SqlClient.SqlConnection($connString)

#--Test connectivity
$conn.Open()
$conn.Close()

Note that, the sample script above should only be used in TEST PANE of Azure automation. The Test Pane only stores the last run of script in Azure Automation. If you run it as a job, the history of output is preserved hence anyone with access to Automation Account would be able to view the credential in plain text.

This script should help you ensure that the username and password being passed to connection string is the same one as required.

Hope this helps.

If the answer did not help, please add more context/follow-up question for it, and we will help you out. Else, if the answer helped, please click Accept answer so that it can help others in the community looking for help on similar topics.