Can't log into database from agent

rmunson8

After creating multiple clones for an instance via the sql-clone portal, and then running PS scripts against it, I get the error:

Clone Agent: Couldn't log into the database server. The user account 'sa' doesn't have a login, or the supplied password is wrong. Add the login to the database 

server and try again. (SqlServerConnectionException)

We have multiple instances that work just fine using the same command. I noticed that when I run the Get-SqlCloneSqlServerInstance command for all instances on a machine, that for all that don't have this issue, the Username value is empty, but for the instance that does that have this issue, the Username is set to sa.

I'm not exactly sure how that instance got into that state of a Username getting set.  I haven't found documentation on how to remove this login or how to even specify these credentials when making the call.  I have an sa login on the SQL server, but not sure how that's getting tied into the clone instance.

Any thoughts as to how to troubleshoot this?

Thanks,
Ryan

rmunson8

Hi Jon, I was able to fix the issue by updating the SQLClone_config database and update the dbo.SourceLocations table and updated the Username to NULL from 'sa' and change AuthType from 1 to 2.   So it was just set to SQL auth which I must have incorrectly set it up as, but couldn't find a way to change that post creation (other than at SQL level)

Jon_Kirkwood

Hi @rmunson8

Thanks for contacting Redgate support regarding your SQL Clone issue logging into a database from the agent.

Can you confirm if it's possible that the machine that is failing for you has had a password change for the SQL Server instance user or the Windows User? 

And if so, can you try to create SQL Clone from the UI (as the UI would ask for the credentials to be confirmed, whereas it is possible that Powershell doesn't ask for it).

Go through the start of the Create Image or Create Clone workflows, and when the SQL Clone program offers you the option to update the agent's SQL Server instance credentials, use that UI to update the password to the new one.

If that doesn't work could we suggest that you manually update the password for windows service accounts credentials and restart the clone service to see if that resolves this issue?

Finally, if this issue persists can you please provide a copy of the log files so we can check for any further details to resolve this item with you.

https://documentation.red-gate.com/clone/troubleshooting/log-locations