PS-Remoting Kerberos Delegation to SQL-Server

[alatdev]

I'm using Remote-Powershell-Session.
I have configured Kerberos Constrained Delegation, to access fileshares on other servers, which works fine.

But if i want to access a SQL-Server, i'm getting:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

The SPN's are correctly set, because other clients are authenticating with kerberos.

Steps to reproduce

Enter-PSSession -ComputerName xxxx -SessionOption $options -Authentication Kerberos -ConfigurationName "powershell.6.2.0"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=tcp:host,port; Initial Catalog=database; Integrated Security = SSPI"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select * from sys.tables"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$nRecs = $SqlAdapter.Fill($DataSet)

Expected behavior

Kerberos-Authentication to SQL-Server should work.

Actual behavior

Exception calling "Fill" with "1" argument(s): "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."
At line:1 char:1
+ $nRecs = $SqlAdapter.Fill($DataSet)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException

Environment data

Name                           Value
----                           -----
PSVersion                      6.2.0
PSEdition                      Core
GitCommitId                    6.2.0
OS                             Microsoft Windows 6.3.9600
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0.}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

[jonkeda]

Does anyone have an answer to this question?

[lachlann562]

I posted this question on stack overflow: https://stackoverflow.com/questions/57331089/sspi-sql-access-fails-in-remote-powershell-request-due-to-double-hop-failure-co

it was suggested to check for the SPN and authentication scheme. will reply here if i make progress.

Stack Overflow

SSPI sql access fails in remote powershell request due to double-hop failure, constrained delegation

We are trying to run an automated install from serverA on remote serverB which needs to talk to sql serverC using windows authentication.

Invoke-Command -ComputerName serverB -ScriptBlock {

$...</div></blockquote>

[lachlann562]

We finally got this to work:

1. Register the SPN for sql server in active directory using:
   SetSPN –A MSSQLSvc/<sqlserver>.<DomainName>:<port> <sql service AccountName>
2. enable kerberos delagation for server "B" in active directory. Enable the option "trust this computer for delegation to any server (Kerberos only)" in the delegation tab of the computer account.

see https://support.microsoft.com/en-ca/help/909801/how-to-make-sure-that-you-are-using-kerberos-authentication-when-you-c

the following code can be used to test if the connection is possible to be made using kerberose (when not using double hop), look at the auth_scheme column:

  $conn = new-object System.Data.SqlClient.SqlConnection 'Data Source=demodbca02\demo16;Initial Catalog=master;Integrated Security=SSPI'
   $conn.open()
   $cmd = New-Object System.Data.SqlClient.SqlCommand 'select * from sys.dm_exec_connections where session_id=@@spid',$conn
   #get field name/positions
   $SQLReader = $cmd.ExecuteReader()
   $columns = @()
   for ($i = 0 ; $i -lt $SQLReader.FieldCount ; $I++)
   {
   	$columns += $SQLReader.GetName($i)
   }
   #$SQLReader.Read()
   foreach ($row in $SQLReader)
   {
       $info = @{}
   	foreach ($col in $columns)
   	{
   		$info[$col] = $row.Item($col)
   	}
   			
   	[pscustomobject]$info
   			
   }
   $SQLReader.Close()
   $conn.Dispose()

here is minimal code to test double-hop for sql

#run from server a

Invoke-Command -ComputerName serverB -ScriptBlock {
    
    $conn = new-object System.Data.SqlClient.SqlConnection 'Data Source=ServerC;Initial Catalog=master;Integrated Security=SSPI'
    try
    {
        $conn.open()
    } finally {
        $conn | Remove-SQLConnection
    }

} -Credential $cred