July 29, 2019 at 1:08 pm
Hi there,
I have this SPN thing I cannot wrap my head around correctly to understand where the issue comes from.
So I have an Active Directory Domain with Server A which is hosting MSSQL as a Default Instance (MSSQLSERVER). Due to company policies we should not use the default 1433 port neither dynamic port allocations so we've decided on 31433 as the default port for any MSSQL DB Service.
MSSQL on Server A is running with Service Account Service_A. This account is allowed to read / write it's own SPNs which it is attempting to do so happily, in fact more happily than I would anticipiate.
Quick check through the SQL Side of Settings:
Shared Memory, Named Pipes and TCP/IP are enabled as protocols for both client and server network configuration, on TCP/IP the port is set to 31433.
This is the Output from SQL Server during Service Startup:
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/ServerA.fq.dn:31433 ] for the SQL Server service.
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/ServerA.fq.dn ] for the SQL Server service.
While the first output is what I would expect, the second one is actually causing issues when I try to connect from local SSMS to the instance afterwards.
Initially we did set up SPNs manually, MSSQLSvc/ServerA.fq.dn:31433 and MSSQLSvc/ServerA:31433, since we allow the Service account to register / deregister SPNs by itself a third SPN is being added - MSSQLSvc/ServerA.fq.dn - by the time that happens I'm unable to get in through SSMS anymore.
I've started the Browser Service & made it startup automatically and I even added Client Aliases for ServerA.fq.dn,51433 to no extend I can make MSSQL Server not register that SPN without any port on it. Do I miss something else somewhere? Another machine with just the SPNs with Server:Port works fine while trying to connect.
Yes I could remove the write servicePrincipalName permission from the accounts but is there a better way than going down that route?
Cheers
July 29, 2019 at 1:30 pm
It looks like a bug
As soon as it adds incorrect spn record on the start, you can run a stored proc on startup which does setspn -D
It's not an elegant or better , but nontheless an alternative
July 29, 2019 at 1:41 pm
This behaviour being a bug might aswell just be, unfortunately I have no older versions of SQL Server than 2017 around to test this, I read it might be related to the NP Protocol thus I set those aliases but since he still does force registration of that not useful SPN.
Any other ideas asides a bug?
July 29, 2019 at 10:29 pm
I always have the two spn creations logged - one with the port and one without. Those with the port number are for TCPIP and the others are for other protocols, although port number is suppose to be optional with the new SPN format. I'd probably try the local login by forcing different protocols for the connection in SSMS and see if that makes a difference. You may want to try the Kerberos Configuration Manager, it can pick up some issues (not all) so it's probably worth a shot:
Microsoft Kerberos Configuration Manager for SQL Server
Sue
August 2, 2019 at 4:09 am
It could also be that u have another SPn register for this service account on the domain. If you have permissions or the domain admin can run setspn to find out if you have a duplicate spn register for this service account.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply