New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

xSQLServerServiceAccount: Can't change Service account for installed feature #706

Closed
pstork opened this Issue Jul 21, 2017 · 17 comments

Comments

Projects
None yet
4 participants
@pstork

pstork commented Jul 21, 2017

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

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Jul 21, 2017

Collaborator

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?

Collaborator

johlju commented Jul 21, 2017

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

This comment has been minimized.

Show comment
Hide comment
@pstork

pstork Jul 21, 2017

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.

pstork commented Jul 21, 2017

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.

@pstork pstork closed this Jul 21, 2017

@johlju

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Jul 21, 2017

Collaborator

Do you have a link to the xSQL module?

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

Collaborator

johlju commented Jul 21, 2017

Do you have a link to the xSQL module?

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

@johlju johlju reopened this Jul 21, 2017

@pstork

This comment has been minimized.

Show comment
Hide comment
@pstork

pstork Jul 21, 2017

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.

pstork commented Jul 21, 2017

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

This comment has been minimized.

Show comment
Hide comment
@nabrond

nabrond Aug 2, 2017

Contributor

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.

Contributor

nabrond commented Aug 2, 2017

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

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Aug 3, 2017

Collaborator

@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).

Collaborator

johlju commented Aug 3, 2017

@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

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Aug 3, 2017

Collaborator

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

Collaborator

johlju commented Aug 3, 2017

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

@johlju

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Aug 3, 2017

Collaborator

@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.

Collaborator

johlju commented Aug 3, 2017

@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

This comment has been minimized.

Show comment
Hide comment
@nabrond

nabrond Aug 3, 2017

Contributor

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.

Contributor

nabrond commented Aug 3, 2017

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

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Aug 4, 2017

Collaborator

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.

Collaborator

johlju commented Aug 4, 2017

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

This comment has been minimized.

Show comment
Hide comment
@nabrond

nabrond Aug 4, 2017

Contributor

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.

Contributor

nabrond commented Aug 4, 2017

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

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Aug 5, 2017

Collaborator

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.

Collaborator

johlju commented Aug 5, 2017

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 johlju added in progress and removed help wanted labels Aug 5, 2017

@johlju johlju changed the title from xSQLServerSetup: Can't change Service account for installed feature to xSQLServerServiceAccount: Can't change Service account for installed feature Aug 16, 2017

@johlju

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Aug 16, 2017

Collaborator

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

Collaborator

johlju commented Aug 16, 2017

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

@nabrond nabrond referenced this issue Sep 7, 2017

Merged

xSQLServerServiceAccount: New Resource Added #795

5 of 5 tasks complete

@johlju johlju closed this in #795 Sep 16, 2017

johlju added a commit that referenced this issue Sep 16, 2017

xSQLServerServiceAccount: New Resource Added (#795)
- Added new resource xSQLServerAccount (issue #706)
  - Added localization support for all strings
  - Added examples for usage

@johlju johlju removed the in progress label Sep 16, 2017

@flouwers

This comment has been minimized.

Show comment
Hide comment
@flouwers

flouwers Oct 25, 2017

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

flouwers commented Oct 25, 2017

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

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Oct 25, 2017

Collaborator

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

Collaborator

johlju commented Oct 25, 2017

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

@flouwers

This comment has been minimized.

Show comment
Hide comment
@flouwers

flouwers Oct 25, 2017

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

flouwers commented Oct 25, 2017

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

@johlju

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Oct 25, 2017

Collaborator

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

Collaborator

johlju commented Oct 25, 2017

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment