**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