Can't log into SSMS using a DNS name from the hosts file

Question

In both my server (that hosts SQLS server) and my Pc, I have this entry in the hosts file:

I am using Windows Authentication to log into the SSMS.

Using servername MYSQLSERVER in the SSMS on my Pc, I have no problems logging in, but if I try the same in SSMS (in the server), then I get this error:

TITLE: Connect to Server
------------------------------

Cannot connect to MYSQLSERVER.

------------------------------
ADDITIONAL INFORMATION:

Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. (Microsoft SQL Server, Error: 18452)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-18452-database-engine-error

------------------------------
BUTTONS:

OK
------------------------------

I have tried replacing the ip in the hosts file with 127.0.0.1 MYSQLSERVER and with ::1 MYSQLSERVER but it's the same problem.

I need to be able to log into SSMS on the server using a host name from the hosts file - and not using localhost or the real servername.

Any idea what I am doing wrong?

Answer 1

Windows Authentication (Kerberos) always requires the client-specified server name to match a principal present in the KDC. Although it works differently from TLS, it can be compared to how TLS would require the name to match the certificate (except there is no bypass option, as Kerberos uses the server name to retrieve the correct key).

For example, in your case the client tries to get a ticket for MSSQLSvc/[email protected]. If the KDC cannot associate that with a known service key, it won't be able to issue an auth ticket that the service would find acceptable.

With Active Directory as the KDC, if you wanted to use a custom name you would need to register it as a SPN (service principal name) in the service's account, e.g. using setspn. Microsoft happens to have documentation specifically regarding SQL Server on this topic, although it's mostly the same for any Kerberos-based service.

setspn -S MSSQLSvc/whatever thesqlaccount

If your PC is not a domain member (and hasn't been specifically told to use Kerberos, either) then it will instead use the less-secure NTLM which is mostly unaffected by the SPN requirement.

Suggestions:

• Microsoft's SQL Server client connection docs suggest that it should be possible to set a custom SPN as part of your connection string. That is, if the server is actually called TESTSERV, then you might be able to use ServerSPN=MSSQLSvc/TESTSERV.

• Use different means of blocking access to the test server, such as firewall rules, or limit which SQL Server accounts are allowed to log in (so that your test account can, but real users cannot).