12:44 PM
Q: Windows Scheduled Task executes SQL scripts as Anonymous Logon, despite being associated with a domain account

Matthew HaugenI've built an EXE that I need to run every hour, for the rest of forever. To do this, I'd like to use Windows Task Scheduler. It would be more appropriate to run this on Windows Server, but circumstances stick me with Windows 10 Enterprise (16299). The EXE is simple enough--pertinently running ...

I've posted this here, as compared to Server Fault or Stack Overflow, in case there are nuances about running it on a client version of Windows. But I'm happy to move it over to SF if y'all think that's more appropriate.
@PimpJuiceIT The connection string is super simple: Server=ServerName; Initial Catalog=DatabaseName; Integrated Security=True. And yes, the account I select when it prompts is that cssvi one, which has the correct permissions (proven by how I can run the EXE successfully through File Explorer).
@PimpJuiceIT The failure is wrapped in a try/catch in my code, so it's logged in another SQL Server, which does use SQL Authentication. But it's an exception coming from SQL, and thrown in my own code. I don't have access to the logs to cross-check against them, unfortunately.
To be clear, it's happening on the phase where it tries to open the connection. Unsurprising, but yeah, it's not getting anywhere interesting before that. "The underlying provider failed on Open," with an InnerException that the login failed for that user.
@PimpJuiceIT I don't have any dev tools on that machine, and I'd ideally like to keep it that way if possible. I can install it if you really want, but I believe issues like that are ruled out by it working in File Explorer directly on that machine when signed in. Like, I can double-click it while logged in as that service account, and watch it run the queries and show results appropriately. Do you still want me to try SSMS?
@PimpJuiceIT Weird, right? I appreciate the help! I've been looking at it for a couple hours, but it's just so gosh darn simple. I was wondering whether it was a registry key or group policy entry awry, but everything looks right, to the best of my limited knowledge. It's a clean-ish install of Windows anyway.
@PimpJuiceIT Affirmative, yep. RDP'd in with the service account.
@PimpJuiceIT I wonder whether there'd be value in me writing another EXE whose soul purpose in life was to print its executing username out to a file. Tempting, although I worry there are other nuances in just getting the current user context that I'd mess up and pollute the test. But that's an option, if need be. Removing variables, and all that.
@PimpJuiceIT Sorry for my silence for that--I built the exe that prints out the username, set up a task for it, ran it on-demand, and it prints out the correct REDMOND\cssvi to my file. That's reading from System.Security.Principal.WindowsIdentity.GetCurrent().Name, which I imagine is representative of where SQL would be getting its context from as well. Seems mighty weird.
And not a bad idea, although I'd be tempted to install Windows Server on the machine before going that route, just to see if it resolved it. I just find it weird that this isn't working. I guess I should try it on another machine altogether--that'd be logical.
@PimpJuiceIT Idk what this tells us, but I set up an identical task on my laptop (same OS, same build), and it appears to be working. It, at least, didn't fail immediately, as the ones on that "server" machine seem to. So I'm officially lost. Maybe it is just one of those weird things, and I should just bite the bullet and reformat the machine.
Play with these GPEDIT.MSC GPO objects to start with just in case.... Looking up kerberos now so stay tuned for that..... This is done on the Windows 10 machine ONLY i.imgur.com/XQRif5I.png. Maybe start with adding exception to SQL Server for that setting first, running GPUPDATE /FORCE and then testing. I don't remember if you have to log out and back in or reboot, etc. for GPO settings to become effective but one by one and trial and error may help as a starting point.
I think it's telling us it is not passing the credential across the network when using Task Scheduler so if we could force it to use either kerberos or NTLM and be compatible with Integrated Auth and the SQL Server receiving that across the network so need to make them match to interpret accurately.... I think.... Still looking into forcing kerberos though
It seems that kerberos policies are supposed to be available as per Security policy settings but I cannot find on two different Windows 10 machines; one joined domain and the other not. These should be the option available per the Kerberos Policy