SQL Server Reporting Services – The value for UrlRoot in RSReportServer.config is not valid. The default value will be used instead.

-> On one of my SQL Server Reporting Services server, I could see below warnings in Event Viewer.

Blog76_1.png

Blog76_2.png

Log Name: Application
Source: Report Server Windows Service
Date: 18/09/2019 9:44:16 AM
Event ID: 110
Task Category: Management
Level: Warning
Keywords: Classic
User: N/A
Computer:
Description:
The value for UrlRoot in RSReportServer.config is not valid. The default value will be used instead.
Event Xml:
<Event xmlns=”http://schemas.microsoft.com/win/2004/08/events/event”&gt;
<System>
<Provider Name=”Report Server Windows Service” />
<EventID Qualifiers=”0″>110</EventID>
<Level>3</Level>
<Task>5</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime=”2019-09-17T23:44:16.691430500Z” />
<EventRecordID>6489</EventRecordID>
<Channel>Application</Channel>
<Computer></Computer>
<Security />
</System>
<EventData>
<Data>UrlRoot</Data>
<Data>RSReportServer.config</Data>
</EventData>
</Event>

-> Edit file RSReportServer.config from location D:\Program Files\Microsoft SQL Server\MSRS<Version>.<Instance>\Reporting Services\ReportServer folder.

-> Search for <UrlRoot> in that file. In my case, it was blank,

Blog76_3.png

-> Open Reporting Services Configuration Manager. Navigate to “Web Portal URL” or “Report Manager URL”. Copy the entry in “URLs:” and paste it in RSReportServer.config file between <UrlRoot> </UrlRoot> as shown below,

Blog76_4.png

-> Restarting reporting service after the above change stopped the warning.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Advertisements

SQL Server Reporting Services Encryption Key Backup

-> Backup of SQL Server Reporting Services Encryption keys plays an important role in Disaster Recovery involving SQL Server Reporting Services.

-> I have seen scenarios where there were no backups of SSRS encryption keys during restore, DBA’s had to delete all encrypted contents and had to create connection string, login, password etc manually.

-> SSRS encryption key backups can be performed following below article,

https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/ssrs-encryption-keys-back-up-and-restore-encryption-keys?view=sql-server-2017

https://www.powershellgallery.com/packages/ReportingServicesTools/0.0.0.2/Content/Functions%5CAdmin%5CBackup-RsEncryptionKey.ps1

-> In one of my recent Project, I used the second link and tried to perform the SSRS encryption key backup using powershell and got below error,

Blog75_1

PS C:\> Backup-RSEncryptionKey -SqlServerInstance ‘SQL16S_TOPR_GIS’ -SqlServerVersion ’13’ -Password ‘Test1234$3$’ -KeyPath ‘C:\temp\PlayingGround\Default.snk’
Get-WmiObject : Invalid namespace “root\Microsoft\SqlServer\ReportServer\RS_SQL16S_TOPR_GIS\v13\Admin”
At C:\Users\vjanakiraman\Documents\WindowsPowerShell\Modules\ReportingServicesTools\ReportingServicesTools\Functions\Utilities\New-RsConfigurationSettingObject.ps1:100 char:19
+     $wmiObjects = Get-WmiObject @getWmiObjectParameters
+                   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidArgument: (:) [Get-WmiObject], ManagementException    + FullyQualifiedErrorId : GetWMIManagementException,Microsoft.PowerShell.Commands.GetWmiObjectCommand

-> I tries below command to list the encoded instance names of my report server instance in the WMI namespace path,

Get-WmiObject -namespace root\Microsoft\SqlServer\ReportServer -class __Namespace -ComputerName . | select Name

Blog75_2.png

-> This explains why we are experiencing the error previously. Compare the name in above screenshot and instance name. Could see extra 5f added in
encoded instance name of report server instance in the WMI namespace path.

-> Lets check the WMI namespace using wmimgmt.msc.

Blog75_3.png

-> Right Click “WMI Control (Local)” and click properties,

Blog75_4.png

Blog75_5

Blog75_6.png

-> The powershell script expects “root\Microsoft\SqlServer\ReportServer\RS_SQL16S_TOPR_GIS\v13\Admin”. But the server has entries, root\Microsoft\SqlServer\ReportServer\RS_5fSQL16S_5fTOPR_GIS\v13\Admin and this is causing issue.

-> I tried rerunning the powershell command to backup SSRS encryption keys by changing the instancename to what we see in WMI namespace,

Blog75_7.png

PS C:\> Backup-RSEncryptionKey -SqlServerInstance ‘SQL16S_5fTOPR_5fGIS’ -SqlServerVersion ’13’ -Password ‘Test1234$3$’ -KeyPath ‘C:\temp\PlayingGround\Default.snk’
You cannot call a method on a null-valued expression.
At C:\Users\vjanakiraman\Documents\WindowsPowerShell\Modules\ReportingServicesTools\ReportingServicesTools\Functions\Admin\Backup-RsEncryptionKey.ps1:86 char:9
+         $encryptionKeyResult = $rsWmiObject.BackupEncryptionKey($Pass …
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
Failed to create backup of the encryption key. Errors:
At C:\Users\vjanakiraman\Documents\WindowsPowerShell\Modules\ReportingServicesTools\ReportingServicesTools\Functions\Admin\Backup-RsEncryptionKey.ps1:94 char:13
+             throw “Failed to create backup of the encryption key. Err …
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : OperationStopped: (Failed to creat…n key. Errors: :String) [], RuntimeException
+ FullyQualifiedErrorId : Failed to create backup of the encryption key. Errors:

-> As per article https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/ssrs-encryption-keys-back-up-and-restore-encryption-keys?view=sql-server-2017 we need to backup the keys only once. Please find the details from that article below,

“You only need to back up one copy of the symmetric key. There is a one-to-one correspondence between a report server database and a symmetric key. Although you only need to back up one copy, you might need to restore the key multiple times if you are running multiple report servers in a scale-out deployment model. Each report server instance will need its copy of the symmetric key to lock and unlock data in the report server database.”

-> I have developed a solution for backing up the SSRS encryption key every day and maintain the backups for last last 10 days using below scripts.

-> Below scripts are for creating credential and Proxy to allow the powershell script to be executed in SQL Server Agent,


CREATE CREDENTIAL SQLAgentPowershellCredential
WITH IDENTITY = 'JBS\SQLAgentProxy'
    , SECRET = 'Passw0rd@#' 

USE msdb;
EXEC dbo.sp_add_proxy @proxy_name = N'SQLAgentPowershellProxy'
    , @enabled=1
    , @description = N'Proxy used to run PowerShell as an administrator'
    , @credential_name = N'SQLAgentPowershellCredential';

USE msdb;
EXEC dbo.sp_grant_proxy_to_subsystem @proxy_name = N'SQLAgentPowershellProxy'
     , @subsystem_name = N'PowerShell'

-> Save below file as c:\temp\Backup_SSRS_Key.ps1


$ComputerName = "JBSERVER1"
$InstanceName = "IN01"
$KeyFolder = "\\JBUNC1\Prod_Backup\SSRS_Encryption_Key_Backup\"
$KeyPassword = 'Passw0rd@#'
$TimeStamp = Get-Date -Format "-yyyyMMdd-HHmmss"

Get-WmiObject -Namespace "Root\Microsoft\SqlServer\ReportServer" -Class "__Namespace" -ComputerName $ComputerName|
Select-Object -ExpandProperty Name |
    % {
        $NameSpaceRS = $_
        $KeyFileName = Join-Path -Path $KeyFolder -ChildPath ($ComputerName + "_" + $InstanceName + $Timestamp + ".snk")
        "Found Reporting Services in instance '$($InstanceName)' on $($ComputerName); will save key to '$($KeyFileName)' ..."
        $SQLVersion = (Get-WmiObject -Namespace "Root\Microsoft\SqlServer\ReportServer\$($NameSpaceRS)" -Class "__Namespace" -ComputerName $ComputerName).Name
        $SSRSClass = Get-WmiObject -Namespace "Root\Microsoft\SqlServer\ReportServer\$($NameSpaceRS)\$($SQLVersion)\Admin" -Query "SELECT * FROM MSReportServer_ConfigurationSetting WHERE InstanceName='$($InstanceName)'" -ComputerName $ComputerName
        $Key = $SSRSClass.BackupEncryptionKey($KeyPassword)
        If ($Key.HRESULT -ne 0) {
            $Key.ExtendedErrors -join "`r`n" | Write-Errorts
        } Else {
            $Stream = [System.IO.File]::Create($KeyFileName, $Key.KeyFile.Length)
            $Stream.Write($Key.KeyFile, 0, $Key.KeyFile.Length)
            $Stream.Close()
        }
    }

-> Save below file as c:\temp\Delete_SSRS_Key_10days_Old.ps1


New-PSDrive -Name "P" -PSProvider FileSystem -Root "\\JBUNC1\Prod_Backup\"
Get-ChildItem –Path "P:\SSRS_Encryption_Key_Backup\" -Recurse | Where-Object {($_.LastWriteTime -lt (Get-Date).AddDays(-10))} | Remove-Item
Remove-PSDrive -Name "P"

-> Create a SQL Server agent job to execute the created powershell scripts,


USE [msdb]
GO

/****** Object:  Job [SSRS - EncryptionKey - Backup]    Script Date: 17/09/2019 5:52:38 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]    Script Date: 17/09/2019 5:52:38 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR != 0 OR @ReturnCode != 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'SSRS - EncryptionKey - Backup', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'Database Maintenance', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR != 0 OR @ReturnCode != 0) GOTO QuitWithRollback
/****** Object:  Step [Backup_SSRS_Encryption_key]    Script Date: 17/09/2019 5:52:38 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup_SSRS_Encryption_key', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'C:\temp\Backup_SSRS_Key.ps1
', 
		@database_name=N'master', 
		@flags=0, 
		@proxy_name=N'SQLAgentPowershellProxy'
IF (@@ERROR != 0 OR @ReturnCode != 0) GOTO QuitWithRollback
/****** Object:  Step [Delete_Files_Older_Than_10days]    Script Date: 17/09/2019 5:52:38 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delete_Files_Older_Than_10days', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'C:\temp\Delete_SSRS_Key_10days_Old.ps1', 
		@database_name=N'master', 
		@flags=0, 
		@proxy_name=N'SQLAgentPowershellProxy'
IF (@@ERROR != 0 OR @ReturnCode != 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR != 0 OR @ReturnCode != 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'1', 
		@enabled=0, 
		@freq_type=1, 
		@freq_interval=0, 
		@freq_subday_type=0, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20190917, 
		@active_end_date=99991231, 
		@active_start_time=164800, 
		@active_end_time=235959, 
		@schedule_uid=N'e45a59ec-7444-4838-ba15-a563f161c46b'
IF (@@ERROR != 0 OR @ReturnCode != 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR != 0 OR @ReturnCode != 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO


Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Error: 18456, Severity: 14, State: 5. Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. Reason: Could not find a login matching the name provided.

Problem

-> One of the SSIS package was failing with below error when validated from SSMS,

Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. Reason: Could not find a login matching the name provided.

-> I tried validating the package and it succeeded. Spoke to the developer who was experiencing this issue and understood below details.

-> SSIS package is stored in “Integration Services Catalog” on SQL Server instance JBSERVER1\SSIS.

-> The SSIS package has a “Data Flow” Task that connects to SQL Server instance JBAG1.

-> Developer has a RDP session to Database Server JBSERVER3. Opens up a SSMS in database server JBSERVER3 and connects to SQL Server Instance JBSERVER1\SSIS and is validating the SSIS package and encounters this error.

-> When the developer validates the package connected from Database Server JBSERVER1, there are no issues.

-> This error is due to double-hop authentication issue. Please refer articles “Understanding Kerberos Double Hop” and “Double-hop authentication: Why NTLM fails and Kerberos works” for more details on Double-hop authentication.

Analysis

-> Connected to SQL Server instance JBAG1, queried SQL Server errorlog and found below errors, which is same when SSIS package validation fails,

2019-07-19 12:15:16.500 Logon        Error: 18456, Severity: 14, State: 5.
2019-07-19 12:15:16.500 Logon        Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. Reason: Could not find a login matching the name provided. [CLIENT: 192.168.15.12]

2019-07-19 13:07:27.200 Logon        Error: 18456, Severity: 14, State: 5.
2019-07-19 13:07:27.200 Logon        Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. Reason: Could not find a login matching the name provided. [CLIENT: 192.168.15.12]

2019-07-19 13:17:08.790 Logon        Error: 18456, Severity: 14, State: 5.
2019-07-19 13:17:08.790 Logon        Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. Reason: Could not find a login matching the name provided. [CLIENT: 192.168.15.12]

-> “ping -a 192.168.15.12” resolves to SSIS server JBSERVER1.

-> Connected to SQL Server instance JBAG1 again, queried SQL Server errorlog and searched for keyword “Service Principal Name (SPN)” and found below details,

2019-07-18 14:49:03.810 Server SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.

2019-07-18 14:49:03.810 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/JBAG1.JBS.COM ] for the SQL Server service. Windows return code: 0xffffffff, state: 63. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

2019-07-18 14:49:03.810 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/JBAG1.JBS.COM:1433] for the SQL Server service. Windows return code: 0xffffffff, state: 63. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

-> SPN is not properly registered. Execute below code on server JBAG1 remotely (RDP to a different server other than JBAG1 and then use SSMS to connect to JBAG1. This is just to use TCP) and look for “net_transport” and “auth_scheme” in your output,

select @@servername as ServerName, session_id,net_transport, auth_scheme from sys.dm_exec_connections where session_id= @@spid

3.Ketool13.PNG

-> Ideally, you should see net_transport as TCP and auth_scheme as KERBEROS. Since SPN is not configured properly in SQL Server instance JBAG1, auth_scheme is NTLM and this is the reason for the error message. Configuring proper SPN should solve this problem.

SPN

-> SPNs are objects registered within AD which allow Kerberos to work. On startup SQL Server attempts to register 2 SPNs against the service account. On shutdown, it attempts to re-register these.

-> These operations can fail, so SPNs are either not registered, or deleted. Both of these can cause problems.

Checking SPNs exist against the service account

-> There should be 2 SPNs configured per instance. One for instance name and one for port number. These need to be against the servername listed in ‘select @@servername’.

-> You can query the SQL Server port using below query,

SELECT MAX(CONVERT(VARCHAR(15),value_data)) as Port FROM sys.dm_server_registry WHERE registry_key LIKE '%MSSQLServer\SuperSocketNetLib\Tcp\%' AND value_name LIKE N'%TcpPort%' AND CONVERT(float,value_data) &amp;amp;amp;gt; 0;

2.Port.PNG

Add SPN manually

-> To query the SPNs registered against a service account run the following from an administrative command prompt:

setspn -L JBS\JBSQLServer

-> You will see output similar to below,

Registered ServicePrincipalNames for CN=JBSQLServer,OU=Service,OU=Generic Accounts,OU=User Environment,DC=JBS,DC=com:

    MSSQLSvc/JB1.JBS.COM:15734
MSSQLSvc/JB1.JBS.COM:IN01
    MSSQLSvc/JBAG1:15734
    MSSQLSvc/JBAG1
    MSSQLSvc/JBSERVER1.JBS.COM:15734
    MSSQLSvc/JBSERVER1.JBS.COM:SSIS
    MSSQLSvc/JBSERVER2.JBS.COM:15734
    MSSQLSvc/JBSERVER3.JBS.COM:IN01

-> SPN entries for database server JBAG1 is not created using FQDN (Fully qualified domain name) and has a wrong port number.

-> Correct SPN can be added using below command from administrative command prompt,

setspn -A "MSSQLSvc/JBAG1.JBS.COM:1433" JBS\JBSQLServer
setspn -A "MSSQLSvc/JBAG1.JBS.COM" JBS\JBSQLServer

-> If it is a named instance, Example : JBSERVER123\IN2017 with port 15223. Below command can be used,

setspn -A "MSSQLSvc/JBSERVER123.JBS.COM:15223" JBS\JBSQLServer
setspn -A "MSSQLSvc/JBSERVER123.JBS.COM:IN2017" JBS\JBSQLServer

-> Delete the bad SPNs using below command,

setspn -D "MSSQLSvc/JBAG1:15734" JBS\JBSQLServer
setspn -D "MSSQLSvc/JBAG1" JBS\JBSQLServer

-> It can take some time for the changes to take effect, but you should be able to reconnect to SQL Server, and get a KERBEROS connection.

-> Execute below code on server JBAG1 remotely (RDP to a different server other than JBS and then use SSMS to connect to JBS\IN2017. This is just to use TCP) and look for “net_transport” and “auth_scheme” in your output. You should see net_transport as TCP and auth_scheme as KERBEROS.

select @@servername as ServerName, session_id,net_transport, auth_scheme from sys.dm_exec_connections where session_id= @@spid

Add SPN using “Microsoft® Kerberos Configuration Manager for SQL Server®”

-> Download the tool and install it.

3.Ketool1.PNG

3.Ketool2.PNG

3.Ketool3.PNG

3.Ketool4.PNG

3.Ketool5.PNG

-> Double click “C:\Program Files\Microsoft\Kerberos Configuration Manager for SQL Server\KerberosConfigMgr.exe”

3.Ketool6.PNG

-> To connect to a local server, leave all fields blank and click connect.

-> To connect to a remote server, enter the Server name, username and password as below,

3.Ketool7.PNG

3.Ketool8.PNG

-> After connecting to the server, you will see all SPNs. Scroll to right and check the status of the SPN. In our case it is Missing.

3.Ketool9.PNG

3.Ketool10.PNG

-> Click on “Fix All” button, and confirm YES.

3.Ketool11.PNG

-> Once created, the status should change to Good.

3.Ketool12.PNG

-> Testing SSIS package should be fine now and you shouldn’t be seeing the issue any more. In my case the issue got resolved after I have added the SPNs.

-> In case the issue is still not resolved after adding SPNs. Check below things,

Checking for multiple SPNs for an instance

-> Sometimes the same SPN is registered against multiple Service Accounts. This will break KERBEROS, as it expects the SPN to be unique.

-> To check for multiple SPNs, run the following,

setspn -Q "MSSQLSvc/JBAG1.JBS.COM:1433"
setspn -Q "MSSQLSvc/JBAG1.JBS.COM"

-> Output,

Checking domain DC=JBS,DC=com

CN=JBSQLServer,OU=Service,OU=Generic Accounts,OU=User Environment,DC=JBS,DC=com

MSSQLSvc/JBS1.JBS.COM:15734
MSSQLSvc/JBAG1.JBS.COM:1433
MSSQLSvc/JBAG1.JBS.COM

CN=svc-Prod-SQLServer,OU=Service,OU=Generic Accounts,OU=User Environment,DC=JBS,DC=com

MSSQLSvc/JBAG1.JBS.COM:1433      
MSSQLSvc/JBAG1.JBS.COM
MSSQLSvc/JBS09903.JBS.COM:15734
MSSQLSvc/JBS09900.JBS.COM:15734
MSSQLSvc/JBS09909.JBS.COM:15734
MSSQLSvc/JBS09909.JBS.COM:INSQL01
MSSQLSvc/JBS09911.JBS.COM:INSQL01

CN=svc-Prod-build,OU=Service,OU=Generic Accounts,OU=User Environment,DC=JBS,DC=com

MSSQLSvc/JBS09982.JBS.COM:15734
MSSQLSvc/JBS09982.JBS.COM:PMGBVSQL01
MSSQLSvc/JBS19609.JBS.COM:DMGBVSQL01
MSSQLSvc/JBS09612.JBS.COM.net:64363
MSSQLSvc/JBAG1.JBS.COM:1433       
MSSQLSvc/JBAG1.JBS.COM

Existing SPN found!

-> In this case I am able to see SPNs created also for service account JB\svc-Prod-SQLServer and JB\svc-Prod-build. May e the service account was changed multiple times. Currently Service account for SQL Server instance JBS is JB\svc-JBS-SQLServer. So in this case we will delete the SPN’s created for servcie account JB\svc-Prod-SQLServer and JB\svc-Prod-build using below command,

setspn -D "MSSQLSvc/JBAG1.JBS.COM:1433" JB\svc-Prod-SQLServer
setspn -D "MSSQLSvc/JBAG1.JBS.COM" JB\svc-Prod-SQLServer
setspn -D "MSSQLSvc/JBAG1.JBS.COM:1433" JB\svc-Prod-build
setspn -D "MSSQLSvc/JBAG1.JBS.COM" JB\svc-Prod-build

Delegation

If instances shows KERBEROS connections, and it still doesn’t work, check that the service account is set to allow delegation.

Similar Issues

-> Linked Servers configured may also fail with this error, since Linked Server needs a ‘double-hop’ which requires you to be connected to the instance using Kerberos. For example, On SQL Server instance SERVER1\IN01, you have configured a linked server to SERVER2\IN02. Everything works fine when connected to Server1. But connection fails with message “Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’ ” when You have a RDP session to SERVER3 (Any other server other than SERVER1, SERVER2) and connected to SERVER1\IN01 using SSMS from SERVER3 and test a connection for the configured linked server which points to SERVER2\IN02. You can solve this by configuring proper SPN as mentioned in this post.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.