xSQLServerServiceAccount: Can't change Service account for installed feature

[pstork]

When using the SQL Server image available in Azure, SQL is already installed, but its installed on a standalone workgroup server. In the past I've used xSQLServer to change the service account to a domain account after the server is joined to the domain. That functionality no longer seems to be present. I've tried using xSQLServerSetup, but that simply bypasses any settings changes since the DatabaseEngine Feature is already installed. I can't find any other resource that could be used to modify the service accounts for running services. Was the copy of xSQLServer I used in the past a custom version for Azure? Is there any resource I can use to change the service account for a pre-installed SQL Instance?

[johlju]

I can't see how that have worked in the past. Test-TargetResource have, as far as I know, always checked for presence of the Database Engine service and then reported $true.

Very curious. Which version of xSQLServer and what configuration did you use when that worked?

I don't know if any custom versions exist for Azure. But if there is I'm interested in knowing of them.

Unfortunately, currently there is no resource able to change the service account.
Maybe as a workaround you could use the DSC built-in Service resource?

[pstork]

I am trying the the built-in Service resource as a workaround. I think that should work for my purposes.

The version I was using originally was reverse engineered about 9 months ago from the SharePoint HA offering in the Azure Marketplace. The documentation in the module says "The xSQLServer module is a part of the Windows PowerShell Desired State Configuration (DSC) Resource Kit,..."

But I just went back and started digging deeper. The actual resource I used appears to come from a module called xSQL. The resource just has the same name as your module. And based on the naming in the module it appears to be Azure specific, since the resource directory is labeled "MicrosoftAzure_xSqlServer". The version is 1.0.0.

Sorry for the confusion. Seems to be a naming issue related to using a Module and a Resource with the same FriendlyName in a DSC script.

[johlju]

Do you have a link to the xSQL module?

I reopening this issue so we can track adding this missing functionality.

[pstork]

There isn't really a link directly to the module. But I can explain how to find it. On Github there is a project for the azure-quickstart-templates. This module is in one of the DSC resources for the Sharepoint-server-farm-ha project. You can find the master branch for that here:

[https://github.com/Azure/azure-quickstart-templates/tree/master/sharepoint-server-farm-ha]

In the dsc folder is a pre-zipped DSC script called PrepareAlwaysOnSqlServer.ps1.zip. If you download and unzip that you will see the modules that were in the Zip, including xSQLServer and xSQL. The XSQLServer resource is actually in the xSQL Module folder.

That's now I got it originally and that's the only link I can find to it. The module doesn't appear to be published separately on github. Hope, that helps.

One last note. The other piece of functionality that I am using from that module is the ability to change the default Database and Log directories. Right now it appears that your module only allows that during Installation. It would be nice to be able to change the default Backup directory also, but the MSFTAzure xSQL module doesn't appear to do that either.

[nabrond]

Thoughts on making this its own resource rather than trying to jam more functionality into xSQLServerSetup? I envision something like...

xSQLServerServiceAccount [String] #ResourceName
{
    ServiceName = [string]
    ServiceAccount = [PSCredential]
    [Force = [bool]]
    [RestartService = [bool]]
    [RestartTimeout = [Int32]]
}

If there's no concerns, I can take on writing this resource.

[johlju]

@pstork I see that MicrosoftAzure_xSqlServer module (friendly name xSqlServer) in xSQL has a helper function Set-ServiceAccount that handles this. It also seems handle recreating the SPN's.

@nabrond I think I'm positive to this.

On one hand I don't like the idea since that would make it possible the service accounts (SQLSvcAccount, AgtSvcAccount, FTSvcAccount, RSSvcAccount, ASSvcAccount and ISSvcAccount) be different for for xSQLServerSetup and xSQLServerServiceAccount, where the account in xSQLServerSetup would no longer be accurate. And a resource should be able to enforce consistency (issue #37).

On the other hand the setup.exe cannot change the service accounts. And xSQLServerSetup should reflect what setup.exe can do, install, uninstall, etc. I think we must see it that way, otherwise xSQLServerSetup would be extremely big if we should enforce consistency with all properties it touches. Also if the service account need to be changed after installation, most likely it wasn't installed using xSQLServerSetup, or a rare scenario occured, so there would not be tow resource with different service accounts (xSQLServerSetup and xSQLServerServiceAccount).

If we agree that we saying that xSQLServerSetup should only enforce consistency on what setup.exe can do, then I all for creating a separate resource for this (xSQLServerServiceAccount).

[johlju]

@pstork Regarding default database file and log directories, can ju post a separate issue for that?

[johlju]

@nabrond Could you post a suggested schema.mof so we can see type qualifiers (Key, Write, etc) and descriptions? I'm curious what Force does.

[nabrond]

I vote for xSQLServerSetup to be limited to actions that would normally be performed by setup.exe. In normal, day-to-day operations, an administrator would not rerun setup to change the service account or default data directory. Rather, they would use a SQL Configuration Manager, SSMS/Registry editor to update these values.

@johlju, for the proposed resource, this is what I am thinking for the schema.mof...

[ClassVersion("1.0.0.0"), FriendlyName("xSQLServerServiceAccount")]
class MSFT_xSQLServerServiceAccount : OMI_BaseResource
{
    [Key, Description("The name of the SQL Server service to change the service account.")] String ServiceName;
    [Required, EmbeddedInstance("MSFT_Credential"), Description("The service account that should be used when running the service.")] String ServiceAccount;
    [Write, Description("Determines whether the service is automatically restarted.")] Boolean RestartService;
    [Write, Description("Forces the service account to be updated. Useful for password changes.")] Boolean Force;
}

The purpose of Force is to handle situations where the password for the current account is changing. I think this is a common-enough task to include support.

[johlju]

We use instance name on all other resources. Should we do that here for xSQLServerServiceAccount as well?

Having SQLInstanceName also as a Key, and change ServiceName to be a ValidateSet with only those services we support changing account for. From those two parameters we find the correct service. That would seem better to me, otherwise the user must first look what the actual service name is.
What do you think?

Setting Force to $true means that Test-TargetResource will always return $false? That means the account (password) will be update each time it checks if state is in desired state, default every 15 minutes. I just noting it here so we get that documented.

[nabrond]

That is probably a better route. As I started to work through things, I was struggling to find a good way to work backwards to the Server and InstanceName from the service. I like maintaining parity with the common parameters in other resources. I will update the MOF to reflect: SQLServer, SQLInstanceName, ServiceType (Enum), ServiceAccount, RestartService, and Force as the parameters. I think using SQLServer, SQLInstanceName, and ServiceType as the key would be the best route as you can have several services of different types with the same name; also, I would like to be able to support clusters.

You are correct on the Force parameter. It seems weird, but it was the best method I could come up with to allow the password to be updated. I can dig around to see if there's a way to check whether a password hash for a service matches the new password.

[johlju]

Sounds good to me @nabrond! Label this as in progress. Looking forward to the PR.
Is it possible for you to fix localization (en-US) at once? See xSQLServerSetup as an example.

[johlju]

Changed the title of this issue to reflect the new resource name instead.

[flouwers]

I faced the same issue in the same context (SQL cluster in Azure).
When changing the service account in SQL Server Configuration Manager, the SPNs are updated for you.
It is not the case in DSC module

[johlju]

@flouwers since this issue is closed, could you please open a new issue regarding SPN's not being chnaged?

[flouwers]

@johlju sorry, I replied on the wrong issue. It was for #843

[johlju]

@flouwers No worries. 😄 Then please submit your comment on the issue #843 as well 😄