Login error with AD authentication remotely with SSMS
-
Hello all,
I am getting this error on one of our servers and only when logging in remotely with a Windows AD account . Local SQL auth works fine.
All ports are open but something is blocking it somewhere on the server 🙁
please see capture.
"The target principal name is incorrect. Cannot generate SSPI."
We have tried a few things to no avail.
Any other leads are much appreciated !
Thanks
JR
-
First thing to check is whether the password for the account has expired. If not there is a free tool available from Microsoft which will help you diagnose any SPN problems. I've used it a couple of time to fix similar issues. See below link:
Thanks
-
Hello,
You should configure Kerberos in your SQL Instance.
Register a Service Principal Name for Kerberos Connections
How to troubleshoot the "Cannot generate SSPI context" error message
Microsoft offers a tool called Kerberos Configuration Manager.
The tool automatically tells you if your configurations are wrong and need to be fixed or the only thing you need to know is to register the SPN.
If you have multiple named instances to configure kerberos you must add a static port because you must register the SPN for the SQL Service and the SQL Service with port.
This one is from SQLServerCentral without the Configuration Manager
When the SQL Server Instance stops it automatically removes the SPN and when you start it should add it automatically. but for these conditions to be met the user needs the next permissions in the AD.
- Read servicePrincipalName
- Write servicePrincipalName
If im missing something i'll come back later.
Greetings.
-
Thank you very much for your input will check with Kerberos and report back
-
Since Kerberos is not being used this is the message we are getting. The domain user has all the necessary grants as mentioned read/write
SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid
Shared memory
NTLM
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/xxxxxxxxxx:1433 ] for the SQL Server service. Windows return code: 0x21c7, state: 15. 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.
-
I think the first time you have to manually set it, after you set it, verify.
SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid
If it's working try restarting the instance, it should automatically set the SPN.
You can query if your service account is being used for SPN, open CMD/Powershell and run:
setspn -L "Domain\Account"
It will print all the services that use the domain account.
Microsoft tool can help you alot with troubleshooting like NorthernSoul mentioned.
Shared Memory will never change, if you are inside the server accesing through SSMS inside the server, it will always use Shared Memory protocol.
Greetings.
-
I'm at a lost as to making it work the we cannot afford any downtime on this installation 🙁
This is what I get when I query the SPN
C:\Windows\system32>setspn -L "ourdomain\s-cpo-sqlgui-d"
Registered ServicePrincipalNames for CN=s-cpo-sqlgui-d,OU=Services Accounts,OU=1-IT,OU=1-ourdomain,DC=ourdomain,DC=com:
MSSQLSvc/serverhere.domain.com:64236
MSSQLSvc/serverhere.domain.com:instancename
I cant afford to start reconfiguring the environment with Kerberos , Any other things I can try ?
Would changing to a local admin account be a workaround ?
Much appreciated
JR
-
Just to clarify ... we have 2 instances on this server and only the second appears in the results above
Can I just run the command to add the default instance along with the port ?
setspn -A MSSQLSvc/SQLServerName:1433 Domain\Account
-
Attached is a printscreen from the Kerberos tool
do I need to remove the setting before adding again as we have 2 instances ?
-
Hey!,
johnnyrmtl wrote:Just to clarify ... we have 2 instances on this server and only the second appears in the results above
Can I just run the command to add the default instance along with the port ?
setspn -A MSSQLSvc/SQLServerName:1433 Domain\Account
You need to register 2 SPNS per Instance, one for the Service and one for the Port.
Where it says Status: Dynamic port, Kerberos doesn't let you add a SPN to a dynamic SQL Port since it would change after the restart and kerberos wouldn't work, you need to add a static port and need to restart the SQL Instance for it to use it.
Im really sorry for not being able to reply sooner.
Regards,
-
Thanks!
So just to be clear I would need to delete what's there with setspn - D and put a static port to replace the dynamic one and then add the SPN service account to both instances with setspn - s
-
Yes, clean everything before adding another SPN, since it may confuse the services.
-
Do I need to restart sql services?
Or can do on the fly
-
No, you don't need to restart the services, most of kerberos commands if not all can be done on the fly, services won't be interrupted.
When you change from dynamic port to static you will need to restart the service.
-
Thank you very much for your help and tips... Much appreciated
Will report back if all works
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply