3

**Update: I added a much shortened script which generates the same issue. See the bottom of this port:

I have a PowerShell script that runs a SQL query against a MSSQL server (different server). The script runs as expected when run interactively. When run as a scheduled task with the same credentials, I don't get query results. The task runs the script and report success, but no data is retrieved. From what we see in the logs, the connection to the SQL server is made as NT AUTHORITY\ANONYMOUS LOGIN.
I've tried setting delegation for the the machine running the task. There is an authentication issue here for which I just can't find a solution.

Details:

  • Task server Win 2019
  • SQL server Win 2019
  • SQL v.2019 running on an Windows Availability Group (I get the same error if script connects directly to active node)
  • Credentials are an AD account with password stored at task creation
  • Account has local admin rights
  • AD func. level 2016
  • Script uses Get-SQL module for query
  • Connect string uses Integrated Security=true
  • Task is set to run with highest priv.

Pertinent info from a transcript:

**********************
Transcript started, output file is C:\Temp\sessionrecord2.txt
WARNING: Error opening connection to 'Server=svr126AGLa.myco.com;Integrated Security=true;Initial Catalog=mydb;ApplicationIntent=ReadOnly'
PS>TerminatingError(): "System error."
>> $global:?
True
**********************

Script:

# Connects to database and retrieves the first 100k records

$Connect = "Server=svr126AGLa.myco.com;Integrated Security=true;Initial Catalog=mydb;ApplicationIntent=ReadOnly"
$exportDir = "D:\Logs\Events"


function Get-LSEventinfo {
    #Grabs all assets in lansweeper with usernames defined
    #stored in sqlite db
    $recs = $args[0]  #Number of records to request
    $sql = @"


Select Top $recs tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblNtlog.Eventcode,
Case tblNtlog.Eventtype
  When 1 Then 'Error'
  When 2 Then 'Warning'
  When 3 Then 'Information'
  When 4 Then 'Success Audit'
  When 5 Then 'Failure Audit'
End As Eventtype,
tblNtlogFile.Logfile,
tblNtlogMessage.Message,
tblNtlogSource.Sourcename,
tblNtlogUser.Loguser,
tblNtlog.TimeGenerated



From tblAssets
    Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
    Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
    Inner Join tblNtlogFile On tblNtlogFile.LogfileID = tblNtlog.LogfileID
    Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
    Inner Join tblNtlogSource On tblNtlogSource.SourcenameID = tblNtlog.SourcenameID
    Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
    Left Join tblNtlogUser On tblNtlogUser.LoguserID = tblNtlog.LoguserID
    Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Where tblAssets.Domain = 'DOMAIN' And tblNtlogFile.Logfile = 'Security' And
    tblAssetCustom.State = 1 And tblNtlog.Eventtype != 3 And tblComputersystem.Domainrole < 2
  Order By tblNtlog.TimeGenerated Desc
"@


#Connect & query
try { $hld = get-sql -MsSQLserver -connection $connect -Session TT   }
catch { "failed real connect"| set-content c:\temp\errcon2.log -force}


Export


try{  TT $SQL |export-csv "$exportDir\LSEventlog.csv" -notypeinformation; write-host "file exported to $exportdir"} }
Catch {write-output "failed query"}
    TT -close
    $hld = $null
}


main:


Start-Transcript -Path C:\Temp\sessionrecord2.txt
  Get-LSEventinfo 100000
  Stop-Transcript

Simplified script - same error. Note - error is the same whether we conenct to the AvailGrp listener or one of the nodes

$Connect = "Server=tcp:Svr126SQLb.myco.com;Integrated Security=SSPI;Initial Catalog=mydb;ApplicationIntent=ReadOnly"
#$Connect = "Server=tcp:Svr126AGLa.myco.com;Integrated Security=SSPI;Initial Catalog=mydb;ApplicationIntent=ReadOnly"

Start-Transcript -Path C:\Temp\sessionrecord2.txt
$sql = @"
  Select * from tblAssets.Domain
"@


$hld = get-sql -MsSQLserver -connection $connect -Session TT -ForceNew 
 TT -close
 $hld = $null
 Stop-Transcript
uSlackr
  • 6,452

3 Answers3

4

Turns out this is similar to the double hop issue. To resolve it, I ran Enable-WSManCredSSP Client –DelegateComputer <schedTaskHost> on the task server and Enable-WSManCredSSP Server on the SQL servers.

uSlackr
  • 6,452
3

The issue seems to be related to Kerberos delegation. Things that are important: using correct machine name, service accounts, and registered SPNs correctly.
Try the following:

J-M
  • 2,070
  • 1
  • 13
  • 18
1

This looks highly like a Kerberos related problem.

At first I’d check if the task is able to get a ticket from the domain controller. Check all of them for an event in the Security Log with ID 4769. It should include the account name (of the user which runs the task) and the username of the account running the SQL Service. This event should be comparable with a cached ticket for the user (can be listed with klist)

If this is not the case, you should look for Kerberos related errors at run time on all domain controllers.

Without the logs it’s hard to give advice but I can imagine that there is something preventing the task scheduler from reaching your domain controller (windows firewall, policy, antivirus)

Maybe a guide like this can be helpful.

What I also would check (not particularly Kerberos related):

  • Check the Credential Manager for entries regarding the SQL Server to which you user session does have access to but the scheduled task might not have.
  • Does the users UPN, sAMAccountName or CN differ from one another? Has one of these even been changed?
  • Are there network restrictions between those hosts and the domain controller
Manu
  • 994
  • 6
  • 19