Kerberos Configuration Manager For Sql Server Download
Dibe Naro
We have installed and ran the tool on a remote server (which is not part of theAvailability Group), and it didn't show any issues with our configuration,but we still can't connect to the Availability Group Listener name using Kerberos.
If you have administrator access to your SQL Server-based computer, you can also run the SQL Connectivity Settings Check tool on that computer, and then review the output to check the SPN configuration of your SQL server instance.
Delegation: Use Delegation to identify any issues that affect the service account's configuration for delegation. This is especially useful in troubleshooting linked server issues. For example, if the SPN checkout is fine but you still experience issues that affect linked server queries, this might indicate that the service account is not configured to delegate credentials. For more information, see the Books online topic at Configuring Linked Servers for Delegation.
I strongly recommend against using kerberos configuration manager. It doesn't negate the need to understand how kerberos works or how to configure kerberos, but it does add something (kerberos configuration manager) to the equation that can and will break.
that is significant information. The CIFS share - I assume that is third party? does it support the AES kerberos encryption types? DES and RC4 would have been off by default on server 2012, but it is possible someone may have turned it back on.
I'm in the process of attempting to upgrade from SQL Server 2012 with SSRS to SQL Server 2022 and SSRS 2022.
Our current server is running SQL 2012 and SSRS 2012 on the same box since they used to be part of the same install and it's a small deployment.
However, SSRS 2022 is now separate from SQL Server, and I can't upgrade SQL 2012 until after I have migrated the SSRS content to a new SSRS 2022 server.
I've stood up a new server just to run SSRS 2022, and successfully used ssrs_migration.rss to migrate all content.
However, on the new SSRS 2022 server when I attempt to connect to a data source that that needs to use the "as the user viewing the report" option for authentication, connection to the data source fails with "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."
From what I have read, this is most likely the dreaded SQL Server double-hop kerberos problem, and so far I have been unable to resolve it.
Technical details: the SSRS 2022 service is running with an AD user account as the service account.
The current in the rsreportserver.config is set to "RSWindowsNegotiate".
I've created the SPNs. I think. I'm not ruling out that the problem is that I've make an error or oversite on the needed SPNs.
For both computer accounts (SQLserver and SSRSserver), I've enabled "Trust this computer for delegation to any service (Kerberos only)". I can constrain that later. Right now, while I'm troubleshooting, I just went with unconstrained for both servers to eliminate that as a problem.
Most of the articles & posts I've seen online recommend using the Kerberos Configuration Manager tool from MS to troubleshoot, unfortunately, the current version Kerberos Configuration Manager appears to have a bug that prevents it from being able to be used. Specifically, if I try to run the Kerberos Configuration Manager from the SSRS server (or from any remote computer, I've tested it on multiple machines and received the same failure), the tool hangs on "Getting SQL instances information from SQL WMI". If I check the Kerberos Configuration Manager logs, the tool is actually still running, connecting successfully to the SQL server, reporting no errors, but stuck in a loop where it just keeps repeating the same steps over and over. IE, you can refresh the log file, and it just keeps growing with the following lines repeating until either I kill the process, or after several minutes it crashes with "Connection Time out":
Info: Connect to WMI, \sql2012server\root\cimv2
Info: Successfully connected to SQLWMI \sql2012server\root\Microsoft\SqlServer\ComputerManagement11
Info: Connect to WMI, \sql2012server\root\cimv2
Info: Successfully connected to SQL RS WMI \sql2012server\root\Microsoft\SqlServer\ReportServer\RS_MSSQLSERVER\v11
Info: Opening file \sql2012server\d$\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\RSReportServer.config using credentials for user adminusername.
Info: Closing file \sql2012server\d$\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\RSReportServer.config.
Info: Successfully connected to SQLWMI \sql2012server\root\Microsoft\SqlServer\ComputerManagement11
Info: Connect to WMI, \sql2012server\root\cimv2
Info: Successfully connected to SQLWMI \sql2012server\root\Microsoft\SqlServer\ComputerManagement11
Note: server name and user name of course changed from the real values
I'm not an expert on this, and I've lost a ridiculous number of hours troubleshooting. The inability to run the Kerberos Configuration Manager, which is what practically everyone suggests, is a real problem as well.
Updates!
u/Quadman pointed me to dbatools PowerShell module with the test-spn command that effectively replaces the KCM and allows me to test my SPN configuration. Thank you, Quadman!
u/FloaterFan clued me in that Edge doesn't allow unconstrained kerberos delegation by default, and a GPO needs to be created to enable it. Thank you, FloaterFan!
u/EitanBlumin linked me his PowerShell script to enable Resource-Based Kerberos Constrained Delegation between the two servers. And that finally got kerberos delegation working! My data source now connects successfully using "as the user viewing the report". Thank you, EitanBlumin!
If you have the right SPN configuration and still can't see it, be wary that sometimes DNS changes take time to apply, in my case I cleared the DNS cache and rebooted my domain controller, but my environment is a lab not a production server, so you might have to wait for the changes to be replicated.
To enable single sign-on (SSO), you have to enable Kerberos in both Design Studio and the VirtualDataPort server. This section describes de configuration options for the Design Studio. To enable Kerberosauthentication in the Virtual DataPort server follow the instructions of the section Kerberos Authenticationof the Virtual DataPort Administration Guide.
Use hostname instead of the IP Address. Kerberos is forcing the usage of hostname instead of the IP address. Not being able to resolve an IP address to the hostname indicates an incorrect configuration of your DNS server.
You already probably know, but Domain Controllers in an AD will evolve across time, new DC will appear, some other will disappears, certificates may be refreshed, and every single operation will impact every OneView server configured with AD authentication because of certificates and DC configuration management in OneView.
To configure SQL server for integrated authentication, SQL server should be running on domain joined machines, and the SQL server service must be running as a domain account, and AS must be configured with the keytab corresponding to the SQL client account whose credentials will be used to connect to the SQL database. Using Kerberos with SQL Server
a. As specified in the above document, the service account that SQL server is running as must have the SPN MSSQLSvc/ExactSQLServerNameUsedByAS>:1433 (Not the account of the keytab configured in AS)
b. If the above has been configured correctly, and still the SQL connection fails (from the AS pod logs), you should debug the SQL server integration using a windows client with SQL Management Studio, and connect to the SQL server using the same connection string used by Automation Suite.
i. When doing this debugging, we do a similar debugging as in 2.c above. On the windows machine with SQL Management studio, turn on kerberos logging using Enable Kerberos event logging - Windows Server Microsoft Learn. First set the LogLevel value as specified in the doc, then try connecting to the SQL server.
Trusted for delegation is a Windows Server policy setting. Delegation of authentication is a capability that client and server applications like Nodinite use when they have multiple tiers. For this configuration and to use the Kerberos security protocol, the client and the server must run under accounts that are trusted for delegation.
The details area displays information such as the KDC IP address and port number, KDC vendor, administrative server IP address and port number, Active Directory server and server IP address of the selected Kerberos realm configuration.
08ab062aa8