How do I enable both PowerShell Remoting and SPN for SQL Server Reporting?

Question

I'm struggling with remoting servers with SQL Server Reporting Services running. My Reporting Services are running with individual domain-accounts, and I have set up SPNs for them (HTTP/<Machine> <domain>\<user>). As far as I can see, this effectively disables using PowerShell remoting, since the SPN which WinRM should use points to the domain account used by Reporting Services.

I have no problem running e.g. Get-Service -ComputerName <Machine>, but if I try Get-CimInstance Win32_Service -ComputerName <machine> or Enter-PsSession <machine> I get an error similar to this:

Get-CimInstance : WinRM cannot process the request. The following error with errorcode 0x80090322 occurred while using Kerberos authentication: An unknown security error occurred. Possible causes are:
-The user name or password specified are invalid.
-Kerberos is used when no authentication method and no user name are specified.
-Kerberos accepts domain user names, but not local user names.
-The Service Principal Name (SPN) for the remote computer name and port does not exist.
-The client and remote computers are in different domains and there is no trust between the two domains.

After checking for the above issues, try the following:
-Check the Event Viewer for events related to authentication.
-Change the authentication method; add the destination computer to the WinRM TrustedHosts configuration setting or use HTTPS transport.

Note that computers in the TrustedHosts list might not be authenticated.
-For more information about WinRM configuration, run the following command: winrm help config. At line:1 char:1 + Get-CimInstance win32_service -ComputerName <machine> + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo: AuthenticationError: (root\cimv2:win32_service:String) [Get-CimInstance], CimException + FullyQualifiedErrorId : HRESULT 0x8033809d,Microsoft.Management.Infrastructure.CimCmdlets.GetCimInstanceCommand + PSComputerName : <machine>

If I delete the SPN on one of my servers, then after a few seconds (a bit fast for AD replication?) I can use the above commands, but If I then reset the SPN the commands fail again after a while.

Some of my Reporting Services need to be able to forward credentials, so I hope someone is able to help me solve this dilemma.

Answer 1

I believe we have found the solution. To avoid Reporting Services and WinRM fighting over the HTTP SPN, you can set a port-specific SPN for the WinRM like this:

setspn -S HTTP/<Machine>:<port> <Machine>

It's a good idea to create SPNs for both short machine name and the FQDN.
The default port is 5985 for HTTP and 5986 for HTTPS, but I believe it can be set up to use different ports.

When using WinRM, I just set up a session like this:

$CimSessionOption = New-CimSessionOption -EncodePortInServicePrincipalName
$CimSession = New-CimSession -Name ServiceSession -SessionOption $CimSessionOption -ComputerName <Machine>
Get-CimInstance Win32_Service -CimSession $CimSession

Answer 2

In addition to @carsten-hynne answer, to create a PowerShell session using the Port you use:

$option = New-PSSessionOption -IncludePortInSPN
$pssession = New-PSSession -ComputerName MYMACHINE -SessionOption $option

Note: Make sure you've added the PORT SPN from @carsten-hynne's answer!

Answer 3

Thanks much to @MichelZ and @carsten-hynne for their direction on this. I was struggling with this problem and these answers really helped. Wanted to add a little more information that helped me though.

First, I think the easiest way to accomplish this is by setting the $PSSessionOption environment variable. This works well because if you have existing scripts that use Enter-PSSession or Invoke-Command, you don't have to modify them.

PS C:\> $PSSessionOption = New-PSSessionOption -OpenTimeOut 120000

See Example 5 on New-PSSessionOption

Second, I wondered about changing the SPN for the web site instead of PowerShell Remoting. In my case it was a custom IIS web application, but to the original question if it was SQL Reporting Services. Why not create a SPN : port for the web site. This is definitely possible; however, in my environment I decided not to pursue it, as it would require changes to lots of client machines. Internet Explorer must be configured to include the port in the SPN. I could see in some environment this being the preference. Especially if you wanted to host multiple web sites all on one machine that utilized different service accounts for their Application Pool Identity. And then PowerShell Remoting would work with the default configuration.

Please find the 3 links below that detail this process very well. I really think MS should make this behavior default in both IE and PowerShell. Try the SPN without the port, but if that doesn't work, try it with the port... And yes, you're reading that right, this goes back to IE6...

1. Internet Explorer 6 cannot use the Kerberos authentication protocol to connect to a Web site that uses a non-standard port in Windows XP and in Windows Server 2003
2. Kerberos and non-standard port number
3. Internet Explorer and SPNs

Answer 4

We have SSRS running on the default port 80. In our environment we have 80 servers, 3 of which are SSRS. I first started by registering the WinRM HTTP SPN with the 5985 and 5986 ports.

setspn -s HTTP/ServerName:4985
setspn -s HTTP/ServerName:4986
setspn -s HTTP/ServerName.DomainName:4985
setspn -s HTTP/ServerName.DomainName:4986

I was then able to get the following to work:

Invoke-Command -ComputerName $Servers_WithSpecialSPN -ScriptBlock { Get-Process | Select -First 10 } -SessionOption (New-PSSessionOption -IncludePortInSPN)
Invoke-Command -ComputerName $Servers_WithoutSpecialSPN -ScriptBlock { Get-Process | Select -First 10 }

I didn't like the thought of having to account for this in the future, so we went a different direction that appeared to work, until we realized that we didn't even need the SSRS SPN to begin with and are unsure why it was created. The other direction involved re-registering (dropping and recreating) the SSRS SPNs with the port, instead of the WinRM SPNs with the port:

setspn -s HTTP/ServerName:Port Domain\Account
setspn -s HTTP/ServerName.DomainName:Port Domain\Account

Being we were able to get rid of the SPNs, I guess it is hard to say that it worked, but it appeared to be working. I'm just posting this as I had a hard time finding a desired solution for our environment and hope this might help others.

Answer 5

1. method: You can use IP address instead of machine/host name. You must also use Authentication Negotiate (instead of Default):

Invoke-Command `
   -ComputerName *172.24.253.58* `
   -Command {Get-Service} `
   -Authentication Negotiate

2. method: You can use DCOM instead of WinRM:

$sOpt = New-CimSessionOption -Protocol DCOM

$Cs = New-CimSession -ComputerName *machine* -SessionOption $sOpt

Get-CimInstance `
   -Query "Select * From Win32_OperatingSystem" `
   -CimSession $Cs