July 21, 2023 at 4:08 pm
Hello experts,
I'm trying to use Kerberos Configuration Manager on an instance of SQL Server 2022 and get this error when I try to connect:
"Error
Unable to connect to server, please ensure that the server name is correct, SQL Server is installed properly, and the user has administrator permissions. If the problem persists, please contact Microsoft support."
I suspect it is a permissions problem, but could someone let me know what permissions are needed in this case? My AD account is already in a group that is a local admin on the server. And so is the SQL Server service account.
Thanks for any help!
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 21, 2023 at 6:50 pm
I have had zero luck with that tool. I've attempted to use it in a few cases I had a confusing kerberos problems but ended up chasing new errors related to the app.
To configure kerberos, you need to be able to write to the service principal names of the service account, or if you use a GMSA, you can configure the GMSA with permissions to write its own SPN.
In recent version of SQL you may also need to check kerberos encryption types. You should be using one of the AES encryption types. That is configured the msds-supportedencryptiontypes attribute of the service account. It may also be necessary to check the domain that it was not set to only accept RC4.
I 100% prefer to configure it manually, it is much more predictable. I always include the single label name and the DNS name in the SPN. I typically do not use non-standard ports so my SPN list look like this:
MSSQLSvc/Server
MSSQLSvc/Server.domain.tld
MSSQLSvc/aagListener
MSSQLSvc/aagListener.domain.tld
etc
If your account is in the protected accounts group, you won't be able to delegate, so no linked servers unless you are logged into the SQL server.
July 21, 2023 at 7:52 pm
My AD account is already in a group that is a local admin on the server. And so is the SQL Server service account.
Why is the SQL Service account an admin on the server? That is a very poor security practice.
But, to solve your Kerberos issue, make the service account a domain admin. Re-start SQL, and then query the Kerberos entries.
setspn -l <server name> setspn -l <service account>
Copy and paste those entries. Remove the service account as a domain admin, and check the Kerberos entries in the same manner as above. Re-create them as needed.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 21, 2023 at 9:08 pm
Why is the SQL Service account an admin on the server? That is a very poor security practice.
When I was first accidentally becoming a DBA, a lot of senior DBAs would insist on that, stopping the firewall service instead of changing the action block to allow, and disabling UAC on SQL servers - around the 2012 era. Still run across people who think its a good idea creating major attack surface and break the rest of the OS while complaining about the stability of Windows that they broke on purpose
July 24, 2023 at 3:18 pm
Thank you both for the advice. Regarding security, I will bring this up with our senior DBA. I didn't realize that making the SQL service account a local admin was as bad as you say.
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
August 30, 2024 at 9:35 pm
This tool was never updated to work with SQL 2022 that's why it's not working.
Go here and you'll see that information near the top listed as a Note.
You can install 2019 using the same service account and script/create the SPNs, uninstall and re-install 2022. I think that'll work. It's what I'm doing right now, but I don't have authority to create the SPNs so I'll know once they get created but I'm guessing they're the same for either version.
September 11, 2024 at 6:41 am
as per Microsoft , SQLCHECK utility ( https://github.com/microsoft/CSS_SQL_Networking_Tools/releases/download/1.0.1422.0/SQL_CHECK.zip ) replaces Kerberos Configuration Manager from SQLServer 2022 +
Download SQL_CHECK.ZIP and extract SQLCHECK.EXE to a folder of your choice, e.g. C:\TOOLS. It is ready to use. For best results, run this tool on the SQL Server and on the client machine. If using a web-based app, please run on the web server, as well.
Note :
Kerberos Configuration Manager is provided as is and no technical support or updates are offered for it. It hasn't been updated to use SQL Server 2022 and later versions of the WMI provider. To troubleshoot and diagnose SQL Server-related Kerberos configuration issues, see SQLCheck.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply