2

I recently upgraded SQL Server from Windows 2008 R2 & SQL Server 2008 R2 to Windows 2012 R2 & SQL Server 2014 SP1. One of the sql agent jobs fails to run. The job runs a PowerShell script to script sql agent jobs to disk. The job fails with an error:

SSPI handshake failed with error code 0x8009030c

If I RDP to the server and run the PS script in ISE with the SQL Server Agent service creds, it works.

Any ideas?

Here's the PS script:

param([String]$ServerName)

$OutputFolder = "\\backupfolder\sqlbackups\sqlservername\SQLAgentJobs"

#Load the SQL Server SMO Assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

#Create a new SqlConnection object
$objSQLConnection = New-Object System.Data.SqlClient.SqlConnection

Try
{
    $objSQLConnection.ConnectionString = "Server=$ServerName;Integrated Security=SSPI;"
    $objSQLConnection.Open() | Out-Null
    $objSQLConnection.Close()
}
Catch
{
    $errText =  $Error[0].ToString()
    Write-Host $errText
}

$DoesFolderExist = Test-Path $OutputFolder
$null = if (!$DoesFolderExist){MKDIR "$OutputFolder"}

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerName

foreach($job in $srv.JobServer.Jobs)
{
    $job_name = $job.Name
    $job.Script() | out-file "$OutputFolder\$job_name.sql"
}
  • What kind of account is SQL Server using? A standard domain account? Does the account have the right in Active Directory to create SPNs? Perhaps the information in my question about kerberos will help. – Max Vernon Sep 29 '15 at 15:01
  • Yeah like Max mentioned this does seem to be kerberos related. That being said, it could be group policy. Have you added the SQL Agent account to the 'run as a service' and 'run as a batch job' rights? You can set this via AD or local security policy in a pinch. Also, check out the SPNs for the SQL Agent service with something like "Setspn -l -u domain\user" and post up what you see. You should have an SPN like in Max's link. Note that you'll need to set the AD account for Delegation before registering the SPN. The key question - what is your agent running as? – Peter Sep 29 '15 at 19:19
2

I had a similar problem when running powershell scripts from a sql server job.

If you do not have remote PowerShell enabled on both servers and firewall access between the servers configured properly it will have issues.

Run a PowerShell script on a different server - from inside a SQL Server job

the way I have solved this problem is through a proxy account.

I have created a proxy account in sql server, that had all the relevat permissions to the remote server, folders and files that I needed and run the sql job as that account.

a proxy account in my case was a domain account, and I have granted this account the privileges on the remote server.

this might help with proxy: How to get the AD account associated to a Proxy?

From this link below you can see:

powershell problem when running on a remote server - access is denied

As far as workarounds, there is this answer on SU. you can read the article in detail that the answer points to fully understand, but this is the main command to adjust permissions:

If you decide you want to allow others, what you do is run the command:

Set-PSSessionConfiguration -Name Microsoft.PowerShell -showSecurityDescriptorUI 

Notice that this action could have a serious impact on your system so we ask you to confirm that you really want to do this.

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.