Sample Header Ad - 728x90

Windows authentication on remote client accessing SQL Server 2022 with SSMS

1 vote
0 answers
82 views
I have installed SQL Server 2022 on Windows Server 2019 Datacenter, and applied the March SQL Server 2022 Cumulative Updates. Also installed on the server is SSMS 18.12.1 and 19.0.2. On a remote machine, I have SSMS 19.0.2. Both machines are on the same domain. I'm using mixed-mode authentication with a custom instance name and custom TCP port. I'm using a self-signed certificate that is installed on both the server and the client machines. I can connect to SQL Server just fine on the server, using both SQL and Windows Authentication methods. From the remote machine, I can connect to this instance using SQL Authentication, but not with Windows Authentication. (I can connect with Windows Authentication to other instances on other machines from the client, although they are SQL Server 2016, not 2022.) I have been round and round over the past couple of weeks with trying out suggestions in other posts. I have enabled "Allow remote connections to this server". I have tried deleting and creating SPNs on the server, although when I start and stop the SQL Server service, it appears to recreate them anyway. I have enabled the custom, static port through SSCM and SQL Server Client Network Utility. (I have enabled TCP and assigned aliases with both utilities since I read that SQL Server 2022 does not use SQL Server Native Client.) The firewall is disabled (although I did go ahead and create an inbound rule for TCP). When I try to connect from the client machine with Windows Authentication and without the "Trust server certificate" box checked, I get: >Cannot connect to xxxxx.xxxxx.xxxxx\xxxx,xxxx. A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The target principal name is incorrect.) (Microsoft SQL Server) The target principal name is incorrect. When I try to connect from the client machine with the "Trust server certificate" box checked, I get: >Cannot connect to xxxxx.xxxxx.xxxxx\xxxx,xxxx. The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server) I have two SPNs registered: MSSQLSvc/servername.xxxxx.xxxxx:portnumber and MSSQLSvc/servername.xxxxx.xxxxx:instancename. I also tried installing the Kerberos Config Manager on the SQL server. It errors with: >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 should also mention that the two SPNs are registered under the AD account that is used to start and run the SQL Server service. I am using the full FQDN servername.xxxxx.xxxxx. The only two SPNs with the "MSSQLSvc/" prefix are MSSQLSvc/servername.xxxxx.xxxxx:portnumber and MSSQLSvc/servername.xxxxx.xxxxx:instancename registered for the domain\dbaccount AD account used to run the SQL Server service, no duplicates.
Asked by K Harrison (11 rep)
Mar 29, 2023, 03:44 PM
Last activity: Apr 5, 2023, 06:59 AM