I recently set up SQL 2022 Express and needed to connect to it over the LAN using sqlcmd. I thought enabling TCP/IP in SQL Server 2022 Configuration Manger and opening the firewall on port 1433 would be enough, but I was still getting these errors when I tried to connect with Windows authentication (sqlcmd –S MYSERVER –E):
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : SQL Server Network Interfaces: No credentials are available in the security package
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Cannot generate SSPI context.
TL;DR Those messages can be caused when the Service Principal Names (SPNs) for SQL are not registered on the server. As I understand it, it used to fall back to NTLM, but now Kerberos is required so the SPNs must exist.
The main Microsoft article for fixing this is here, with references to the SQLCHECK tool on Github here and the setspn command here. My goal here is to winnow that down to what worked for me.
1. Download SQLCHECK and run it on the SQL server. Scroll to the bottom of the output file and look for missing SPNs. I had this:
Suggested SPN Exists Status
------------------------------------- ------ -------------------
MSSQLSvc/MYSERVER.mydomain.local:1433 False SPN does not exist.
MSSQLSvc/MYSERVER:1433 False SPN does not exist.
MSSQLSvc/MYSERVER.mydomain.local False SPN does not exist.
MSSQLSvc/MYSERVER False SPN does not exist.
2. From an administrative command prompt, run setspn –l. Then run these commands to add the missing SPNs:
setspn -s MSSQLSvc/MYSERVER.mydomain.local:1433 MYSERVER setspn -s MSSQLSvc/MYSERVER:1433 MYSERVER setspn -s MSSQLSvc/MYSERVER.mydomain.local MYSERVER setspn -s MSSQLSvc/MYSERVER MYSERVER
3. Run SQLCHECK again to confirm that the SPNs are Okay:
Suggested SPN Exists Status
------------------------------------- ------ ------
MSSQLSvc/MYSERVER.mydomain.local:1433 True Okay
MSSQLSvc/MYSERVER:1433 True Okay
MSSQLSvc/MYSERVER.mydomain.local True Okay
MSSQLSvc/MYSERVER True Okay
4. Try sqlcmd from the LAN workstation now.