SqlServiceAccount: works but sql is unusable due to "sql cannot generate sspi context"

[4c74356b41]

Details of the scenario you tried and the problem that is occurring:
Trying to automate AlwaysOn on Azure. I'm using SQLIaaSExtension. That extension changes the SQL service account to "NT SERVICE\MSSQLServer", but AlwaysOn doesn't work with local accounts (or maybe I'm missing something?) so I have to change this to the domain account, after which I'm unable to connect to remote sql instances (only to instances running on localhost). I can work around that by removing server SPN's and restaring SQL and waiting for a bit. For testing purposes I assigned domain admin as the service account for the SQL, yet it still complained that it cannot set the SPN.

The DSC configuration that is using the resource (as detailed as possible):

        xSQLServerServiceAccount "sqlServiceDomainAccount" {
            SQLServer       = $env:COMPUTERNAME
            SQLInstanceName = 'MSSQLSERVER'
            ServiceType     = 'DatabaseEngine'
            ServiceAccount  = $SQLCreds
            RestartService  = $true
        }

Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running:
Microsoft Windows NT 6.3 (14393), SQL - 13.0.4435.0 (2016 SP1 latest on Azure)

Name                           Value
----                           -----
PSVersion                      5.1.14393.1532
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.14393.1532
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

What module (SqlServer or SQLPS) and which version of the module the DSC Target Node is running:
SqlServer 21.0.17178
Version of the DSC module you're using, or 'dev' if you're using current dev branch:
ModuleVersion = '8.1.0.0', but the xSQLServerServiceAccount is from the dev branch.

[johlju]

Setting SPN’s could be delegated to the service account (in AD), but the domain admin have this permission already. So does this problem occur because the SPN’s are already set?
From where did you remove the SPN’s?
Maybe the resource need to remove the SPN’s.

In the original issue for the resource proposal, there was an old resource that had the functionality. I mentioned it in this comment so maybe we need to add code around this.
#706 (comment)

[4c74356b41]

I'm honestly not a SQL person, so I'm not the best person to ask\have an opinion on these things.

I've removed these for each server:

MSSQLSvc/dev-sql-x.test.local:1433
MSSQLSvc/dev-sql-x.test.local

so I cannot use AlwaysOn with local accounts, right? @johlju

[4c74356b41]

I wonder if I can just use Powershell DSC Service resource to set the sql service account? @johlju

[johlju]

From what account did you remove the SPNs?

I honestly don’t know if it’s possible to run AlwaysOn using local accounts. I haven’t tried :)

This resource changes the account thru the SQL SMO object model which isn’t the same as changing the account using Service resource.
You could try changing the service account with the Service resource to see if it makes a difference.

[johlju]

You need to use domain accounts - if using non-domain accounts then certificates must be used.

If you run SQL Server under a non-domain account, you must use certificates. For more information, see Use Certificates for a Database Mirroring Endpoint (Transact-SQL).
https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/set-up-login-accounts-database-mirroring-always-on-availability

[4c74356b41]

From what account did you remove the SPNs? - I used the same domain account, but I did it manually with setspn -d. I removed SPNs from Machine account (so sql servers accounts, not service accounts) @johlju

[johlju]

Is this the steps you took? Do I understand this correctly?

1. The extension changes the SQL service account to "NT SERVICE\MSSQLServer"
2. You change to a domain account. At this time you can't connect remotely and get the error in the issue title.
3. You work around that by removing server SPN's for the domain account in step 2, and restarting SQL and waiting for a bit. Now you can connect remotely without any problem.
4. You added domain admin as a service account - and that can't register SPN's - but you can still connect remotely.

At what other step than 4 did it complain that it cannot set the SPN?

[4c74356b41]

I'm still testing\experimenting, but my current approach is this:

1. Install extension.
2. Start DSC, which contains script step (runs as domain admin) that does this:

setspn -d MSSQLSVC/sql-server:1433 domain\sql-server$         # deletes machine SPN
setspn -S MSSQLSVC/sql-server:1433 domain\sql-service-account # registers service account SPN

3. Next step in DSC configuration (after script step) is xSQLServerServiceAccount, which changes service account to domain account
4. Everything works (but I'm still testing this).

@johlju

[flouwers]

I have the same issue.
For information, if you change the service account in SQL Server Configuration Manager, the SPNs are updated for you.
I am testing if it's a good solution to include setspn commands in the xSQLServerServiceAccount module itself.

[nabrond]

@4c74356b41 / @flouwers I believe you are mistaken here. SQL Configuration Manager is not responsible for registering the SPN when changing service accounts. Rather, the SQL Server Service handles that task itself at start-up. Additionally, removing registered SPNs at service shutdown. Check out the Microsoft documentation for Register a Service Principal Name for Kerberos Connections, specifically the Automatic SPN Registration section for more information.

You can configure your Active Directory to allow SPNs to be automatically registered by the SQL Server Service account. The changes required to allow this can be found in this article, specifically "Step 3" under "More Information". As the article states, be cautious when making the changes in AD as the required tools introduce some risk to other users of the directory such as Exchange.

While SPN configuration and maintenance can be a challenge and struggle, and adding this functionality is not overly difficult, I am hesitant to update this resource for two reasons:

1. This functionality is already provided in SQL Server, natively.
2. It requires additional AD rights and/or privileged credentials to automate.

Just my $0.02 on the topic. But I will leave the decision up to the masses!

[4c74356b41]

well, i mean, i dont really care, its your dsc module, if you want to render it useless, its your choice

[randomnote1]

@4c74356b41

The only thought I have about including a SPN resource is for use with Availability Group listeners or SSRS (not sure about SSAS off the top of my head). Otherwise I agree with @nabrond that SQL Server natively handles SPN registration for the default listener on the instance.

If a SPN resource were proposed to be added to this module, I would suggest that we look at implementing it with the Active Directory resources since that is where the change is actually made. This would shift the focus to AD admins who tend to have the permissions to create the SPNs in AD as opposed to DBAs who tend not to have permissions in AD.

Take a look at Issue dsccommunity/ActiveDirectoryDsc#153 for a request to add SPNs in xActiveDirectory.

[flouwers]

@nabrond @randomnote1
I think both approaches are equivalent:
On one side it's indeed more AD-related since changing SPN requires special permissions.
On the other side, as @4c74356b41 mentions, without that functionality, SQL is not working as desired (at least when using a domain user to run the SQL service). So triggered SPN change from xSQLServer module could also be a valid option.
I leave it up to the experts ;-)

[SphenicPaul]

There doesn't look to be a lot of feedback on this bug/issue since Nov 2017 so I'm not sure how much of a problem/discussion this is any more, but just to add my thoughts...

Having hit SPN-related issues a lot in the past 18 months, the problems and their solutions have typically been in/with Active Directory (AD) and not with SQL Server (or the SqlServerDsc module). Successful, preferred workflows we have typically used have been to prime/ready the AD accounts ready for use with SQL Server, as seperate to the installation of SQL Server (via the SqlServerDsc module).

I'd suggest that in the majority of cases, accounts performing the SQL installations won't (and/or shouldn't) have the relevant access to register and unregister SPNs within AD. As a result, it would seem that making that functionality part of this module would seem inappropriate.

In addition, use of "Always On" functionality is typically, I suspect, not going to be setup using local or network, virtual accounts, and will be, more typically set up with service accounts, or Group Managed Service Accounts (gMSAs).

Latest, SQL Server 2019 documentation on this is here

Is it worth closing this issue?

[johlju]

The ActiveDirectoryDsc now has the resource ADServicePrincipalName so I think managing SPN's should be done with that module. And @nabrond explains further in the #843 (comment).

I'm closing this issue, but happily reopen if there is more to be discussed.