3

We had a power outage last night and when the server came back up our domain users could not access our SQL Server 2000 instance name: MASSQL.

We can access using SQL Authentication.

  1. We tried making sure the user that runs the service has SSPI read and Write
  2. We tried creating a new user
  3. We tried running the service using NETWORK SERVICE account
  4. We tried running as Local System Account
  5. We tried creating a new Domain Admin account and run service as the new Domain Admin account

I also ran the following command against the problem SQL Server instance:

setspn -L xsql2

Results

Registered ServicePrincipalNames for CN=MASSQL,CN=Computers,DC=ABC,DC=com:
        HOST/MASSQL
        HOST/MASSQL.ABC.COM

I ran the following command against a non-problem SQL instance:

setspn -L xensql1

Results

Registered ServicePrincipalNames for CN=XENSQL1,CN=Computers,DC=ABC,DC=com:
        WSMAN/XENSQL1
        WSMAN/XENSQL1.ABC.com
        RestrictedKrbHost/XENSQL1
        HOST/XENSQL1
        RestrictedKrbHost/XENSQL1.ABC.COM
        HOST/XENSQL1.ABC.COM

It seems the results for the non-problem instance have more data. I do not know if this has to do with the fact one is 2000 and the other is 2012.

What likely happened? How do I allow domain users login again?

|improve this question
  • Now's a good time to upgrade :) Did the domain controller lose power too? Anything else having similar issues? That's where I'd start – scsimon Jun 20 '19 at 17:16
  • reboot the server again and verify that windows services all came up... – kevinsky Jun 20 '19 at 17:17
  • What does the SQL Server Error log say about registering Service Principal Names? – Max Vernon Jun 20 '19 at 17:20
4

Use setspn -X to look for duplicate SPNs for the SQL Server in question.

Remove any duplicate SPNs that don't line up the SQL Server Service account in question.

Service Principal Names for SQL Server take the form of:

MSSQLSvc/server.domain:port
MSSQLSvc/server:port

Assuming your SQL Server is using the default TCP port, 1433, I would expect you need the following servers:

MSSQLSvc/MASSQL.abc.com:1433
MSSQLSvc/MASSQL:1433

You can create those SPNs using the following command:

SETSPN -A MSSQLSvc/MASSQL.abc.com:1433 DOMAIN\Account
SETSPN -A MSSQLSvc/MASSQL:1433 DOMAIN\Account

Where DOMAIN\Account is the name of the service account used by SQL Server.

If your SQL Server uses a named instance, the SETSPN commands look like:

SETSPN -A MSSQLSvc/MASSQL.abc.com:1433 DOMAIN\Account
SETSPN -A MSSQLSvc/MASSQL:1433 DOMAIN\Account
SETSPN -A MSSQLSvc/MASSQL.abc.com:INSTANCENAME DOMAIN\Account
SETSPN -A MSSQLSvc/MASSQL:INSTANCENAME DOMAIN\Account
|improve this answer
  • No duplicates found for that server. I think I should have more SPNs for the server than this: HOST/MASSQL HOST/MASSQL.ABC.COM What do you think? – Bill Greer Jun 20 '19 at 18:01
  • 1
    I've added details to my answer showing what you need. – Max Vernon Jun 20 '19 at 18:42
3

The SQL Server SPNs are not set on the computer account per se, but on the account that SQL Server is running under. You will need to create two SPNs for the SQL Server service if the service account does not have permissions to create the SPNs. The following examples assume using the default port of 1433.

SetSPN -s "MSSQLSvc/<FQDN_SERVERNAME>" "<DOMAIN>\<SERVICE_ACCOUNT_NAME>"
SetSPN -s "MSSQLSvc/<FQDN_SERVERNAME>:1433" "<DOMAIN>\<SERVICE_ACCOUNT_NAME>"

See Register a Service Principal Name for Kerberos Connections

To check to see if they are already there: 

SetSPN -L ServiceAccountName

However, it seems that you may have bigger issues in play. If you configured SQL Server to run with a domain admin account, it should have had permissions to create the SPNs.

|improve this answer
  • I will try and report back. – Bill Greer Jun 20 '19 at 18:06
0

Microsoft's Kerberos Configuration Manager for SQL Server may help you quickly diagnose and fix this issue.

Here is a good story to read: http://houseofbrick.com/microsoft-made-an-easy-button-for-spn-and-double-hop-issues/

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.