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"

$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()

  • When you run it locally, are you running it as the proxy account? – Jonathan Fite May 7 '19 at 19:23
  • @JonathanFite no i am not – DForck42 May 7 '19 at 19:50
  • What account is your agent using? This is the account trying to connect. – Richard Hughes May 20 '19 at 18:25

This doesn't 100% answer the question as to WHY I was getting the answer, but a workaround I figured out was moving the entire job to a different server where it worked.

| improve this answer | |
  • 1
    Possibly this is a Kerberos authentication issue. If you examine the login failures on the remote server, were they coming from NT AUTHORITY\ANONYMOUS LOGON? If so, it indicates that the problem is delegation of the proxy credentials is failing to the remote server. – HandyD May 22 '19 at 0:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.