SQL Server - Kerberos Constrained Delegation
-
am currently working through replacing a development and production SQL server, moving from server 2012 to server 2022.
These two servers have the SQL Service running as an AD account. Thus, I have gone through and configured the proper SPNs in Active Directory and configured Kerberos Constrained Delegation.
Adding the SPN for the FQDN and hostname, with and without port (1433), on the AD account the SQL Service is running under. Going to the delegation tab for that user account and adding the MSSQLSVC<hostname> and MSSQL<FQDN> with and without port. Going to the AD Computer object of each of the two servers, to the delegation tab and adding the server that I am looking for these two servers to access via a cifs share.
When I login to SSMS from my PC to connect to these servers and run the query:
use master
GO
SELECT COUNT(auth_scheme) as sessions_count, net_transport, auth_scheme
FROM sys.dm_exec_connections
GROUP BY net_transport, auth_schemeI can see that the my TCP connection has an auth_scheme as KERBEROS.
However, when I try to launch the Kerberos Configuration Manager and try to connect to the local host (leaving the server name, use name, and password blank - while on the server as an admin) - I get the following:
"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 am an administrator on the server, ran the application as administrator. I'm not really sure where to look from here. Any help or tips would be appreciated.
Thanks
Steve
- This topic was modified 1 year ago by stephenmbell. Reason: thread has taken turn from original post
-
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.
However, if you are using the utility locally on the SQL server, you will only ever get NTLM authentication. Only NTLM is possible when authenticating locally.
-
Thanks for this response. I'm only looking at it because it opens fine on my older server and not on the new one I am building.
I also have a linked server that links to an Access Database on a CIFS share. It works on my old server but not on my new one. I've reviewed the above information in the OP, but I can't pinpoint why this isn't working.
-
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.
-
Technically, the access database is located on an SMB share on our internal network.
If I am logged on to the new SQL server via RDP or VM console, I can successfully complete a test connection on the linked server.
However, if I access via SSMS from my machine (same LAN, no firewall between), I am not. Also, my SQL Agent job that pulls data from the Access Database to insert into a SQL database is failing.
If by third party you are referring to a vendor or something external, that is not the case.
-
did you configure delegation for the new service account?
-
I believe so. I’m using the same service account as the old server. I’ve used the PowerShell dbatools module to migrate the database, logins, agent jobs and lined servers.
I’ve registered the new hostname, single name and FQDN, with and without the port number as an SPN to the service account. Also delegated the account permissions in AD.
In my notes from when I got 2012 server , I mention that I used wireshark and Procmon while troubleshooting, but I didn’t leave anything more specific. My present self is judging my past self for not thinking of that for my future self.
-
Is delegation constrained?
-
Here's what I've got in AD (image attached). Left side is the service account, right side is the SQL Server.
Entries on the left are the SQL servers that use this service account. Entries on the right is the CIFS delegation.
- stephenmbell wrote:
Thanks for this response. I'm only looking at it because it opens fine on my older server and not on the new one I am building.
I also have a linked server that links to an Access Database on a CIFS share. It works on my old server but not on my new one. I've reviewed the above information in the OP, but I can't pinpoint why this isn't working.
on this one - linked servers, if using the Microsoft Access Runtime are likely going to require you to grant permissions on the TEMP folder of the service account to all other accounts that use that linked server - this may be something you aren't aware of and DBATOOLs would not look for.
-
Looks like this thread has taken a few turns from the title :). I agree - I remember something about that in the past when reading about this. On this server, the service account is a local admin - I verified that it does have permission to $env:temp folder. I
I was able to do some testing tonight. When I run SSMS as an admin on my machine, I am able to do a successful test connection.
However, my SQL Agent jobs (running as the same SVC account as SQL) aren't able to query the Access Databases via linked server. They DO have NTFS permission on the file share - as the test connect works.
Here's what I am seeing in the history.
-
regarding the temp folder you missed the point.
Assume a account named "SQLAccount" - your SQL instance runs under this account
this account will have a folder
c:\users\SQLACOUNT\AppData\Local\Temp
the account itself will obviously have access to it - but YOUR account that is executing the query through the linked Server DOES NOT - and for some operations under the ACE driver it requires MODIFY permissions on that folder - even though it it completely counter intuitive the need for it.
From the error above and from what you said regarding the SQL agent account this is not the issue - but you still need to look into it if other accounts access the linked server.
can you also look at the provider properties - below is for a different provider but ACE would give similar output.
you would want the In process set, and the "disallow ad-hoc" unset (and for this you may need to confirm that the registry entry DOES exist with a value of zero - sometimes it does not property is "DisallowAdHocAccess " under "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\Providers\Microsoft.ACE.OLEDB.12.0"
change the server instance to match your instance version (and name if applicable)
-
Hello @Mr or Mrs. 500,
you wrote "kerberos configuration manager does add something to the equation that can and will break". Can you tell us what is this "something" please?
Thank you,
best regards.
-
`
olivier gg wrote:Hello @Mr or Mrs. 500,
you wrote "kerberos configuration manager does add something to the equation that can and will break". Can you tell us what is this "something" please?
Thank you, best regards.
Its existence. It can have bugs, it can be a legacy version that doesn't support current encryption types, it can fail name resolution, etc.
It doesn't do anything for you to get you to reduce the requirement to understand how kerberos works in order to implement it, but it can fail a test when everything is actually working. It isn't useful to diagnose kerberos problems and its configurator button only occasionally works.
-
Thank you @Mr or Mrs. 500.
Best regards.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply