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

1

I have tried this question on Stack Exchange - Database Administrators (https://dba.stackexchange.com/q/129286/51925), but I don't get any responses, so I'll try here.

I'm struggling with remoting servers with SQL Server Reporting Services. 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.

A good day to all.

Hynne

powershell
sql-server
ssrs
asked on Super User Feb 16, 2016 by Carsten Hynne • edited Jan 28, 2019 by fixer1234

2 Answers

2

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

Have a good day

Hynne

answered on Super User Feb 18, 2016 by Carsten Hynne
1

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!

answered on Super User Oct 24, 2017 by MichelZ

User contributions licensed under CC BY-SA 3.0