0

I'm trying to remote connect to my Sql server 2016 instance on my home server running on a workgroup. I need to set an SPN to do this. All the guides out there seem to be related to setting the SPN for domain situations, not workgroups. Here is the SPN that I have tried for server Odin running under NT SERVICE\MSSQLSERVER (the default service account)

C:\Windows\system32>setspn -A MSSQLSvc/Odin:1433 "NT SERVICE\MSSQLSERVER"

Ldap Error(0x51 -- Server Down): ldap_connect
Failed to retrieve DN for domain "" : 0x00000051
Warning: No valid targets specified, reverting to current domain.
FindDomainForAccount: Call to DsGetDcNameWithAccountW failed with return value 0x000004BC
Unable to locate account MSSQLSERVER

I am running the cmd prompt as admin, I'm logged in as admin and now out of ideas. The account MSSQLSERVER must exist otherwise SQL server would not be running (I can connect locally).

Calanus
  • 195
  • 1
  • 3
  • 9
  • 1
    AFAIK, there isn't a way to do what you're looking to do. There is no ticket granting authority here (i.e. Active Directory) to utilize the SPN that you're trying to set and the error message says as much (it can't find the domain because there isn't one). – Ben Thul Sep 22 '16 at 20:36

1 Answers1

1

You're using SQL login, right? Not Windows authentication. SPN is used for Windows authentication (Kerberos) when both the client and server are members of the domain.

I think there's another problem that prevents you from connecting to your SQL server. Might be routing issue,firewall, port configuration for SQL service or antivirus. Try disabling the Windows Firewall on SQL server and connecting again to see if it's related to firewall. Same goes for antivirus.

For port configuration, you can verify it using SQL Server Configuration Manager and running netstat -an | find /i "1433" command to see if port is Listening.

Mer
  • 991
  • 4
  • 9
  • No, I am using windows authentication with the same "Microsoft account" username existing on both laptop and server (who is an administrator of both machines. The firewall is turned off and the database service is running on port 1433. I turned off Windows defender on the server with the same result. – Calanus Sep 22 '16 at 17:13
  • Calanus, that won't work. You need to use a SQL login. – Dylan Knoll Sep 22 '16 at 23:13
  • ok sql login it is then :-( – Calanus Sep 27 '16 at 19:13