1

What I am trying to do:

We have a Task Scheduler that kicks off an EXE, which in the course of its runtime, will connect to SQL Server.

So that would be:

  1. taskServer.myDomain triggers the Task Scheduler action
  2. taskServer.myDomain exe runs locally
  3. taskServer.myDomain initiates a connection to sqlServer.myDomain

The scheduled task is associated with a service account (svc_user) that is set to run with highest privilege, run whether the user is logged in or not, and store credentials for access to non-local resources.

The actual behavior

What we are seeing is the Task Scheduler is indeed running as svc_user. It triggers the EXE as expected, and the EXE is also running as svc_user. When the EXE initiates a connection to SQL Server, it errors on authentication.

Looking at the Event Viewer we can see the failure trying to initialize the connection to SQL

Exception Info: System.Data.SqlClient.SqlException
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(System.Data.ProviderBase.DbConnectionPoolIdentity, System.Data.SqlClient.SqlConnectionString, System.Data.SqlClient.SqlCredential, System.Object, System.String, System.Security.SecureString, Boolean, System.Data.SqlClient.SqlConnectionString, System.Data.SqlClient.SessionData, System.Data.ProviderBase.DbConnectionPool, System.String, Boolean, System.Data.SqlClient.SqlAuthenticationProviderManager)

And then looking at the SQL Server logs we can see the root of the issue

Logon,Unknown,Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided.

The connection initialized by the EXE to SQL Server is trying to authenticate as ANONYMOUS LOGON.

What I have tried

Background

This issue popped up when our IT team started deploying a GPO lockdown in our environments. So in order to get to this point, we first had to add some GPO exceptions to allow the svc_user to:

  • log on locally
  • log on as batch job
Progress?

This is where we started being able to capture the ANONYMOUS LOGON error in SQL Server. From there we tried a handful of other GPO exceptions including

  • Allow Credential Save
  • Enable computer and user accounts to be trusted for delegation
The actual issue?

So it would appear that this is a double hop delegation issue. Which eventually led me here and then via the answer, here and here.

So I tried adding GPO policies to allow delegating fresh credentials using the WSMAN/* protocol + wildcard.

Two issues with this:

  • the Fresh credentials refer to prompted credentials while the EXE is running as a service during off-hours and inheriting the credentials from the TaskScheduler
  • the WSMAN protocol appears to be used for remote PowerShell sessions (via the original question in the serverfault post) and not SQL Service connections.

So, I added the protocol MSSQLSvc/* to the enabled delegation and tried all permutations of Fresh, Saved and Default delegation. (This was all done in Local Computer Policy -> Computer Configuration -> Administrative Templates -> system -> Credentials Delegation)

Where it gets weird

We have another server, otherServer.myDomain, which we setup with the same TaskSchedule. It is setup with the same GPO memberships, but seems to be able to successfully connect to SQL Server. AFAIK, the servers are identical as far as setup and configuration.

The Present

I have done a bit more digging into anywhere I could think that might offer clues as to how I can feed the credentials through or where they might be falling through. Including watching the traffic between the taskServer and the sqlServer as well as otherServer and sqlServer.

I was able to see NTLM challenges coming from the sqlServer to the taskServer/otherServer.

  • In the case of taskServer, the NTLM response only has a workstationString=taskServer
  • On otherServer, the NTLM response has workstationString=otherServer, domainString=myDomain, and userString=svc_user.

Question

What is the disconnect between hop 1 (task scheduler to EXE) and hop 2 (EXE to SQL on sqlServer)? And why does this behavior not match between taskServer and otherServer?

GullySquad02
  • 43
  • 1
  • 6
  • Out of curiosity, why not just use SQL Authentication? – Dai Sep 23 '22 at 01:45
  • Is `svc_user` a Local User or a Domain User account? How is authX / Logins / etc configured on the SQL Server instance? – Dai Sep 23 '22 at 01:46
  • @Dai we have to use domain accounts due to company policy. And yes, svc_user is a domain account. And I believe it is setup to use NTLM if I understand your last question correctly. – GullySquad02 Sep 23 '22 at 02:38
  • Have you checked what `RSOP.msc` says for the Credential Delegation policies? – Dai Sep 23 '22 at 03:05
  • I had not, but it matches what was set from gpedit and what was inherited from global policies. Both `taskServer` and `otherServer` match for Credential Delegation policies, with `taskServer` having an extra permission enabled for the `Allow delegating credentials` mentioned above. – GullySquad02 Sep 23 '22 at 03:22
  • 1
    Welp, time to use your company's MS support contract for an incident ticket - or expense the $500-per-incident fee which means no _mandatory-fun-team-building_ exercises this month... (surely that's a _good_ thing?) – Dai Sep 23 '22 at 03:22
  • Yeah I believe I have already started that process. Hopefully some combination of that and this post lead to an answer. I will make sure to update it whenever that happens. Thanks Dai – GullySquad02 Sep 23 '22 at 03:27
  • Please do post the solution that you come to (assuming the support call is successful, you get your $500 refunded back if it isn't!). I'm personally interested in this myself because _decades ago_ I was trying to get Kerberos delegation working through IIS to SQL Server (so SQL Server would run SQL in the context of the remote user (well, in an AD domain LAN using Internet Explorer...ew...) and I couldn't figure it out, so this will be some nice _white-whale_ closure for me if you can get it working. – Dai Sep 23 '22 at 03:29
  • Yeah that sounds very similar. I will be sure consolidate as much information as I can find to make this post useful to whoever else comes across something similar – GullySquad02 Sep 23 '22 at 03:31
  • _run whether the user is logged in or not_ in connection with Task Scheduler often makes me think of DPAPI issues. Any insights from [Windows 10 2004/20H2 and the broken 'Credentials Manager': Root Cause and Workaround – Part 1](https://borncity.com/win/2020/11/09/windows-10-2004-20h2-und-der-kaputte-credentials-manager-ursache-und-workaround/) or [Windows 10 2004/20H2 and the broken 'Credentials Manager': Cause and Workaround – Part 2](https://borncity.com/win/2020/11/10/windows-10-2004-20h2-und-der-kaputte-credentials-manager-ursache-und-workaround-teil-2/)? – AlwaysLearning Sep 23 '22 at 04:42
  • @AlwaysLearning not sure. Those issues seem to be in the same area, but not sure they are related. If those were the root causes, I would expect that the initial delegation to the EXE would have failed, not just the 2nd hop to SQL server – GullySquad02 Sep 23 '22 at 05:00
  • @dai I finally have a solution, hope it helps or at least that you find the answer interesting. – GullySquad02 Oct 20 '22 at 22:08

1 Answers1

0

So I finally have an update/solution for this post.

The crux of the issue was a missing SPN. The short answer:

  • Add an SPN for sqlServer associated with the service account SQL services are running as (not the svc_user)
    • example: SetSPN -S MSSQLSvc/sqlServer.myDomain myDomain\svc_sql_user
  • Add another SPN like above but w/ the sql service port
    • example: SetSPN -S MSSQLSvc/sqlServer.myDomain:1433 myDomain\svc_sql_user
  • Set the SQL service user account to allow delegation like so
    • enter image description here
GullySquad02
  • 43
  • 1
  • 6
  • Also came across this article today which adds some more context in the case specifically for SQL server. https://www.sqlshack.com/overview-of-service-principal-name-and-kerberos-authentication-sql-server/ – GullySquad02 Oct 21 '22 at 11:36