I have a powershell script that queries from the local server for a list of servers and then iterates through that list of servers, connects to them, queries some data, and then connects and inserts that data locally.
The powershell works perfectly fine if I run it in Powershell ISE, but when I put it into a powershell step in a SQL Agent job it fails. I have the job using a proxy sql agent account that I set up that uses a windows account, let's call it domain\PowershellAgent. I have granted the account PowershellAgent sufficient permissions on the server as far as I can tell, but I still get the error.
The full error is:
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
What can I do to get this working?
Example of a connection:
$sqlConnection = new-object System.Data.SqlClient.SqlConnection $sqlConnection.ConnectionString = "Server='$server'; Database=DBADatabase;Trusted_Connection=True;Connect Timeout=5; MultiSubnetFailover=Yes" $sqlConnection.Open() $sqlCommand = new-object System.Data.SqlClient.SqlCommand $sqlCommand.Connection = $sqlConnection $sqlCommand.CommandText = $Query $sqlCommand.CommandType = [System.Data.CommandType]::Text $sqlCommand.CommandTimeout = 60 $sqlCommand.Parameters.Add("@id", [System.Data.SqlDbType]::Int).value = $id $DataReader = $sqlCommand.ExecuteReader() $Datatable.Load($DataReader)