SqlServiceAccount: Not working for IntegrationServices, SQLServerBrowser and Fulltext

[Francois-Rousseau]

Details of the scenario you tried and the problem that is occurring

I tried to work with SqlServiceAccount but it is not working as I expected because I can't set service account for IntegrationServices, SQLServerBrowser and Fulltext service.

The error : PowerShell DSC resource MSFT_SqlServiceAccount failed to execute Test-TargetResource functionality with error message: System.Exception: Service type 'IntegrationServices' is not instance aware.

Same error happens with IntegrationServices, SQLServerBrowser and Fulltext service. I don't understand why the property is giving me the choice of those services if I can't use the resource with them.

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

                SqlServiceAccount integration 
                {
                    ServerName           = $NodeName
                    InstanceName         = $Node.InstanceName
                    ServiceType          = "IntegrationServices"
                    ServiceAccount       = $Node.SqlAgentServiceCredential
                 #   RestartService       = $true
                 #   Force                = $true   
                    DependsOn   = $InstallTask

                }

Version of the operating system and PowerShell the target node is running

Windows 2016

SQL Server edition and version the target node is running

Edition : Standard Edition Version : 11.4.7001.0 Language : 1033

What SQL Server PowerShell modules, and which version, are present on the target node.

xSqlServer 9.1.0.0 SQLASCMDLETS 1.0 SQLPS 1.0

Version of the DSC module you're using, or write 'dev' if you're using current dev branch

SqlServerDsc 11.3.0.0

[johlju]

Hmm... This code throws this error. But I don't know why this check is even in here, since the resource does not do anything cluster specific. 🤔

https://github.com/PowerShell/SqlServerDsc/blob/9b6f76804acf1cf241afb8161f85e8a113ecf280/DSCResources/MSFT_SqlServiceAccount/MSFT_SqlServiceAccount.psm1#L434-L438

I suggest we just remove these rows, unless someone can explain what the purpose is. 🙂

[johlju]

Labeled it as bug and help wanted so that anyone in the community can resolve this.

[johlju]

@Francois-Rousseau If you remove the above code lines and run your configuration again, does it work?

[Francois-Rousseau]

@johlju : It doesn't work : PowerShell DSC resource MSFT_SqlServiceAccount failed to execute Test-TargetResource functionality with error message: System.Exception: The IntegrationServices service on DSC\SQL could not be found. It needs more investigation :)

[Francois-Rousseau]

@johlju : I'm investigating on that, it looks like the information on the services that comes from the registry is filtered for the "managed" services only. I'm new to DSC. Is there a way to run a DSC configuration and look in the variables line per line? Like a debug mode? It would help me a lot if I would be able to look in the variables.

[johlju]

I was just about to ask you if you could debug this as I don't have the bandwidth to do it right now. 😃

You could use Enable-DscDebug -BreakAll, see Debugging DSC resources, but I rarely use that. Normally I just open up the resource script file on the target node in ISE or in the PowerShell console an run it line by line either manually (F8) or save the script file as a .ps1 and modify it so I can run it more than once (F5). That way I can test different things, and change the code as I debug too. 🙂

[Francois-Rousseau]

Ok thanks for this! I'm facing a little issue while debugging on the target node... when I hit this line : return $serviceTypeValue -as [Microsoft.SqlServer.Management.Smo.Wmi.ManagedServiceType]

I got this error: Unable to find type [Microsoft.SqlServer.Management.Smo.Wmi.ManagedServiceType].

It seems that something is missing to continue running the script, but... I'm stuck now, any idea?

[johlju]

You need to Import-Module -Name SqlServer or Import-Module -Name SQLPS, depending which you use. The former one is SqlSever (not SqlServerDsc).

[Francois-Rousseau]

I found the bug. I would like to explain it clearly like you did before with the piece of code and the lines referenced but I don't know how to do that, help please :)

I'm testing what I think might be the change to do right now

[Francois-Rousseau]

The problem is that the code looks in HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Services to get all the services and their names. It looks like that for each service:

The problem is this piece of code:

    # The value grabbed varies for a named vs default instance   
    if ($InstanceName -eq 'MSSQLSERVER')
    {
        $propertyName = 'Name'
        $returnValue = '{0}'
    }
    else
    {
        $propertyName = 'LName'
        $returnValue = '{0}{1}'
    }

The value grabbed is not always LName if the instanceName is not MSSQLSERVER. This is true only for the services that are instance aware, i.e. those that have ($) in their name.

So the condition has to be modified to reflect the reality:

    # The value grabbed varies for a named vs default instance   
    if (($InstanceName -eq 'MSSQLSERVER') -or ($ServiceType -eq 'SQLServerBrowser')) #other services as to be added here, i'm working on it, don't want to put 3-4 -or))
    {
        $propertyName = 'Name'
        $returnValue = '{0}'
    }
    else
    {
        $propertyName = 'LName'
        $returnValue = '{0}{1}'
    }

Tested if for SQLBrowser and it works! I need to add other services. I also want to add the possibility for the full text service. I also noted that all of the services of the instance are restarted after a change to any service, it related in another issue, I might check that later too.

[johlju]

did before with the piece of code and the lines referenced but I don't know how to do that

When you look at the code files on GitHub you can shift-click on the line numbers to select a range of lines, then you click on the '...' and choose to copy the permalink. When you paste in the permalink, GitHUb will automatically return the lines that the permalink reference (you can see it working when you choose preview in the comment).

[johlju]

You can also make code blocks by using three backticks on a seperate row, before and after the code. I updated your comment above. 🙂

[johlju]

Is it thru that all value name that has '$' in the value data (in the registry) uses the LName value name? If so maybe we can use 'SqlAgent$' -match '\$', which should match any string with a '$' in it. Are there any exceptions to the rule? 🙂

[johlju]

Btw. That explains why there was code that throw on instance ware - I mistakenly interpreted that as a cluster-thing. Still doesn't explain why that limitation was added. 🤔

Thank you for looking into this so a solution can be found! 🙇 👍

[Francois-Rousseau]

My code works for now, I'm installing all the features to be sure to get the right condition for all the services. The instance-aware thing is not related to a cluster thing, like you said. It's related to the SQL instance. If the instance is installed with default name, all the services won't have any $ in their name. If the instance is installed with a name specified, SOME of the services will have a $ in their name + the instance name. The trick here is that SOME of the service don't respect this and this is why the code wasn't working. Thanks for your help on debugging and with github. I'll post the solution when I'll have tested for all services, and we'll see how to include it in the next release?

[Francois-Rousseau]

Those services are currently not treated by the resource : SQL Server Distributed Replay Client SQL Server Distributed Replay Controller SQL Server VSS Writer SQL Full-text Filter Daemon Launcher (SQL) I will have a look on that, to see if it is possible to add them, so the resource could treat all the SQL services installed by all the possible sql features

[Francois-Rousseau]

Ok so here's the solution for the first bug, so now it will work for 2 more services : replace if (($InstanceName -eq 'MSSQLSERVER') by if (($InstanceName -eq 'MSSQLSERVER') -or ('IntegrationServices','SQLServerBrowser' -contains $ServiceType)) Maybe it's not the best powershell way to do it but it works.

The line is line 395

[Francois-Rousseau]

I found another issue with IntegrationServices, It's related but not sure if I need to open a new issue. There is a particularity with the IntegrationServices:

The name in the registry is MsDtsServer but the real name of the service is MsDtsServer110. 110 means 11.0 which is the version of the integration services.

To resolve this bug, we will have to read the version somewhere in the registry and add it to MsDtsServer to get MsDtsServer110 dynamically.

I'm with SQL 2012, it would be nice to have feedback on what the values are for other versions of SQL.

I'm looking for a solution right now as for me it's the same problem that I want resolved.

[Francois-Rousseau]

Maybe I need someone better than me with SQL for this one, is this service always begin with MsDtsServer, I don't know Here's what I found : Looking at the path of the "SQL Server Integration Services 11.0" service : D:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.exe Searching the registry for that .exe gives me :

Here's the code in problem:

    $serviceObject = $managedComputer.Services | Where-Object -FilterScript {
        $_.Name -eq $serviceNameFilter

The problem is the -eq which is not equal for the integration services with the missing 110. How do you suggest to modify the code to resolve this issue? If there are other places in the ressource that utilize this piece of code to find services names, that should be great to correct them all. So I think we should modify the Get-SqlServiceName function to return MsDtsServer110 instead of MsDtsServer. I'm just not sure how to get the 110. Any idea?

[Francois-Rousseau]

Looking for a service that begins with MsDtsServer ? based on that article that might be the trick : https://support.microsoft.com/en-in/help/942177/how-to-determine-the-version-of-sql-server-2005-integration-services

We can see in this article, which was for SQL 2005, that the 110 was 90... so we can not check the .exe fil because it is in a subfolder that contains the number that we are searching for...

But, looking for services that begin with MsDtsServer should do the trick, I think.

[Francois-Rousseau]

I hardcoded it just for fun and it works :

    $serviceNameFilter = Get-SqlServiceName -InstanceName $InstanceName -ServiceType $ServiceType
    if("MsDtsServer" -match $serviceNameFilter){$serviceNameFilter += "110"} #HERE

    # Get the Service object for the specified instance/type
    $serviceObject = $managedComputer.Services | Where-Object -FilterScript {
        $_.Name -eq $serviceNameFilter

The only thing missing is to get the 110 dynamically

[johlju]

in this https://github.com/PowerShell/SqlServerDsc/issues/1173#issuecomment-403062441, can you give me the context (which line) should be changed in that code you proposing? It's easier to follow you then 🙂

In regards to getting the major version if the current SQL Server instance there is a helper function that could help with that maybe.

https://github.com/PowerShell/SqlServerDsc/blob/4997ae4726171110bdac57122114487cab36ab55/SqlServerDscHelper.psm1#L192-L201

In the regard to new issues. If you see it easier to split an issue to one or more, then please do - easier for the community to follow if there is a issue being discussed is specific to the topic, but leave it to you to decide when it's suitable to create a new issues around this - so far it feels like we are withing topic? 🙂

[johlju]

I think Get-SqlInstanceMajorVersion returns 11, so you have to add a 0 to it. Think the below could work.

if ('MsDtsServer' -match $serviceNameFilter)
{
    $majorVersion = Get-SqlInstanceMajorVersion -SQLInstanceName $InstanceName
    $serviceNameFilter += "$($majorVersion)0" #HERE
} 

[Francois-Rousseau]

It's better than I expected :) Do you think the 0 shoud be dynamic also ? Based on that table, it will not work for sql 2008 r2 : https://sqlserverbuilds.blogspot.com/

Is there another function that return the "minor" version I guess?

[Francois-Rousseau]

The line you asked is line 395

[johlju]

Actually I think SQL Server 2008 R2 is 110 too. Don't have an instance up and running so I could check, if someone knows please correct me - for now I think we can assume it is 110. 🙂

[johlju]

Looked at line 395, I'm following you now - looks good to me so far. Great work on this! 😃

[Francois-Rousseau]

thanks! :) I'll test what you propose for the 110 now

[Francois-Rousseau]

Works like a charm! Adding the code you proposed before but in the right function, this should be :

      if ('MsDtsServer' -match $serviceNamingScheme)
        {
                $majorVersion = Get-SqlInstanceMajorVersion -SQLInstanceName $InstanceName
                $serviceNamingScheme += "$($majorVersion)0" #HERE
        } 

at line 428 This works. So with these 2 changes, this issue should be resolved. How it works for proposing it "officially" for a new version of the module? Thanks a lot!

[Francois-Rousseau]

I forgot the fulltext and others that doesn't work, maybe I'll open a new issue on this.

[johlju]

You need to send in a pull request (PR) with the changes. You need git on a machine, and preferably Visual Studio Code. You can see how to get up and running here; https://github.com/PowerShell/DscResources/blob/master/GettingStartedWithGitHub.md, after that you can read https://github.com/PowerShell/DscResources/blob/master/CONTRIBUTING.md#fixing-an-issue and the testing guidelines https://github.com/PowerShell/DscResources/blob/master/TestsGuidelines.md

Top off my head it's these steps (I'm writing these down here so I can do a proper quick-start documentation). If you get stuck, let me know and I will give you pointers. 🙂

1. Install Git for Windows (default settings works perfectly)
2. Configure git with username, e-mail address and autocrlf (here; https://github.com/PowerShell/DscResources/blob/master/GettingStartedWithGitHub.md)
3. Install Visual Studio Code, once Visual Studio Code is started, install the PowerShell extension and markdownlint extension.
4. Install Pester (to run tests before sending in a PR)

   Install-Module -Name Pester -SkipPublisherCheck -Force

5. Fork this repo (if you haven't already). Click on fork, top right on this page.
6. On you client, run these in PowerShell

   mkdir c:\source # or any folder
   cd c:\source
   git clone https://github.com/PowerShell/SqlServerDsc
   cd c:\source\SqlServerDsc
   git remote add my https://github.com/Francois-Rousseau/SqlServerDsc # Adds a remote to your fork
   git checkout -b fix-issue#1173 # creates a new working branch
   # starts code for the current folder
   code . 
   # You should now see the the working branch name down to the left in Code.
   # Copy in the files into  c:\source\SqlServerDsc overwriting the existing ones.
   # Update change log and update/add unit tests, and verify by running the tests.
   Invoke-Pester .\Tests\Unit\MSFT_SqlServerNetwork.Tests.ps1 -CodeCoverage .\DSCResources\MSFT_SqlServerNetwork\MSFT_SqlServerNetwork.psm1
   # If you look in Code, you should see changed files under "Source Control" tab
   # Add a commit message and commit the files under "Source Control" tab.
   # Push the changes to your fork
   git push my fix-issue#1173
   # Go to your fork in GitHub and create a new pull request (GitHub will suggest that) and follow the instructions.

[Francois-Rousseau]

Hi @johlju

I tried Pester without modifying anything on the SqlSetup resource and I have those results :

Am I supposed to get a 100% ? Thanks

Code coverage report: Covered 97,65 % of 724 analyzed Commands in 1 File. Missed commands:

File Function Line Command

---

MSFT_SqlSetup.psm1 Get-TargetResource 179 Write-Verbose -Message $script:localizedData.DataQualityServicesFeatureNotFound MSFT_SqlSetup.psm1 Get-TargetResource 203 $securityMode = 'SQL' MSFT_SqlSetup.psm1 Set-TargetResource 1216 $setupArguments += @{ SAPwd = $SAPwd.GetNetworkCredential().Password } MSFT_SqlSetup.psm1 Set-TargetResource 1216 SAPwd = $SAPwd.GetNetworkCredential().Password MSFT_SqlSetup.psm1 Set-TargetResource 1259 $setupArguments += (Get-ServiceAccountParameters -ServiceAccount $FTSvcAccount -ServiceType 'FT') MSFT_SqlSetup.psm1 Set-TargetResource 1259 Get-ServiceAccountParameters -ServiceAccount $FTSvcAccount -ServiceType 'FT' MSFT_SqlSetup.psm1 Set-TargetResource 1267 $setupArguments += (Get-ServiceAccountParameters -ServiceAccount $RSSvcAccount -ServiceType 'RS') MSFT_SqlSetup.psm1 Set-TargetResource 1267 Get-ServiceAccountParameters -ServiceAccount $RSSvcAccount -ServiceType 'RS' MSFT_SqlSetup.psm1 Set-TargetResource 1316 $setupArguments += (Get-ServiceAccountParameters -ServiceAccount $ISSvcAccount -ServiceType 'IS') MSFT_SqlSetup.psm1 Set-TargetResource 1316 Get-ServiceAccountParameters -ServiceAccount $ISSvcAccount -ServiceType 'IS' MSFT_SqlSetup.psm1 Set-TargetResource 1376 $log = $log.Replace($SAPwd.GetNetworkCredential().Password,"****") MSFT_SqlSetup.psm1 Set-TargetResource 1472 $errorMessage = $script:localizedData.TestFailedAfterSet MSFT_SqlSetup.psm1 Set-TargetResource 1473 New-InvalidResultException -Message $errorMessage MSFTSqlSetup.psm1 Set-TargetResource 1478 throw $ MSFT_SqlSetup.psm1 Test-TargetResource 1857 $result = $false MSFT_SqlSetup.psm1 Get-SqlMajorVersion 1895 (Get-Item -Path $Path).VersionInfo.ProductVersion.Split('.')[0] MSFT_SqlSetup.psm1 Get-SqlMajorVersion 1895 Get-Item -Path $Path

[Francois-Rousseau]

I tried it for \MSFT_SqlServiceAccount and it's 100%

[Francois-Rousseau]

After the 2 modifications discussed below, I got this with Pester : [+] Should throw the correct exception if SetServiceAccount call fails 45ms Tests completed in 2.4s Tests Passed: 43, Failed: 3, Skipped: 0, Pending: 0, Inconclusive: 0

Code coverage report: Covered 95,51 % of 89 analyzed Commands in 1 File. Missed commands:

File Function Line Command

---

MSFT_SqlServiceAccount.psm1 Get-SqlServiceName 432 $serviceNamingScheme += "$($majorVersion)0" MSFT_SqlServiceAccount.psm1 Get-SqlServiceName 432 $majorVersion MSFT_SqlServiceAccount.psm1 Get-SqlServiceName 443 $errorMessage = $script:localizedData.NotInstanceAware -f $ServiceType MSFT_SqlServiceAccount.psm1 Get-SqlServiceName 444 New-InvalidResultException -Message $errorMessage

How do I ... correct that? Any help ? Thanks

[Francois-Rousseau]

Here's one error message:

et-ItemProperty : Cannot find path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Setup' because it does not exist.
At C:\source\SqlServerDsc\SqlServerDscHelper.psm1:204 char:20
+ ... lVersion = (Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsof ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (HKLM:\SOFTWARE\...QL Server\Setup:String) [Get-ItemProperty], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetItemPropertyCommand

      [-] Should throw an error for 'IntegrationServices' which is not instance-aware 74ms
        Expected an exception, with message 'Service type 'IntegrationServices' is not instance aware.' to be thrown, but the message was 'System.Exception: Could not get the SQL version for the instance 'TestInstance'.'. from C:\source\SqlServerDsc\DscResources\CommonResourceHelper.psm1:193 char:5
            +     throw $errorRecordToThrow
            +     ~~~~~~~~~~~~~~~~~~~~~~~~~
        545:                     { Get-SqlServiceName -InstanceName $mockNamedInstance -ServiceType $ServiceType } | Should -Throw $testErrorMessage
        at <ScriptBlock>, C:\source\SqlServerDsc\Tests\Unit\MSFT_SqlServiceAccount.Tests.ps1: line 545
      [-] Should throw an error for 'SQLServerBrowser' which is not instance-aware 47ms
        Expected an exception, with message 'Service type 'SQLServerBrowser' is not instance aware.' to be thrown, but no exception was thrown.
        545:                     { Get-SqlServiceName -InstanceName $mockNamedInstance -ServiceType $ServiceType } | Should -Throw $testErrorMessage
        at <ScriptBlock>, C:\source\SqlServerDsc\Tests\Unit\MSFT_SqlServiceAccount.Tests.ps1: line 545

What I understand is that the test is expecting the behavior that was coded before, but I corrected this bug and now the test is failing (of course...)

What I'm I supposed to do with that? Thank you for helping anyone

[johlju]

@Francois-Rousseau Sorry for not responding for this for a while, I have been away for a few days.

You don't need 100% coverage, just make sure the code you are changing is tested. Any tests that are failing you need to make sure the test works - the existing tests can be changed if it is necessary for your code change, but it can also mean a test is failing because the code is now doing something it should not.

For example a test in your previous comment says

Expected an exception, with message 'Service type 'IntegrationServices' is not instance aware.' to be thrown, but the message was 'System.Exception: Could not get the SQL version for the instance 'TestInstance'.'

It expected an the error message Service type 'IntegrationServices' is not instance aware. to be thrown, but instead the changed code throw the error message System.Exception: Could not get the SQL version for the instance 'TestInstance'.. To solve this, you need to figure out if the code you changed made the tests fail, you got an error before this tests, so that can cause this test to fail as well. So first, resolve any errors in the code, and then look at the tests if they either need to be changed, for example if you need to add new mocks, or change existing mocks.

[johlju]

You might need to add new test as well, for example if a new functionality is added, then there are new scenarios that need to be tested, that are not covered by present tests.

[ghost]

any updates on this?

[sql-aus-hh]

Another month without any progress? I was able to change service user of DatabaseEngine but not able to change SQLServerAgent- Account...

`VERBOSE: [SQLVM02]: LCM: [ Start Resource ] [[xSQLServerServiceAccount]SetServiceAccount_User] VERBOSE: [SQLVM02]: LCM: [ Start Test ] [[xSQLServerServiceAccount]SetServiceAccount_User] VERBOSE: [SQLVM02]: [[xSQLServerServiceAccount]SetServiceAccount_User] Preferred module SqlServer found. VERBOSE: [SQLVM02]: [[xSQLServerServiceAccount]SetServiceAccount_User] Importing PowerShell module SqlServer. VERBOSE: [SQLVM02]: [[xSQLServerServiceAccount]SetServiceAccount_User] 2019-01-07_13-30-53: Connecting to WMI on 'sqlvm02'. VERBOSE: [SQLVM02]: [[xSQLServerServiceAccount]SetServiceAccount_User] 2019-01-07_13-30-53: Current service account is 'NT Service\MSSQLSERVER' for sqlvm02MSSQLSERVER. VERBOSE: [SQLVM02]: LCM: [ End Test ] [[xSQLServerServiceAccount]SetServiceAccount_User] in 0.2180 seconds. VERBOSE: [SQLVM02]: LCM: [ Start Set ] [[xSQLServerServiceAccount]SetServiceAccount_User] VERBOSE: [SQLVM02]: [[xSQLServerServiceAccount]SetServiceAccount_User] Preferred module SqlServer found. VERBOSE: [SQLVM02]: [[xSQLServerServiceAccount]SetServiceAccount_User] Importing PowerShell module SqlServer. VERBOSE: [SQLVM02]: [[xSQLServerServiceAccount]SetServiceAccount_User] 2019-01-07_13-30-53: Connecting to WMI on 'sqlvm02'. VERBOSE: [SQLVM02]: [[xSQLServerServiceAccount]SetServiceAccount_User] 2019-01-07_13-30-53: Setting service account to 'sqlscripting\sqlserv' for service MSSQLSERVER. VERBOSE: [SQLVM02]: LCM: [ End Set ] [[xSQLServerServiceAccount]SetServiceAccount_User] in 17.5470 seconds. VERBOSE: [SQLVM02]: LCM: [ End Resource ] [[xSQLServerServiceAccount]SetServiceAccount_User] VERBOSE: [SQLVM02]: LCM: [ Start Resource ] [[xSQLServerServiceAccount]SetAgentAccount_User] VERBOSE: [SQLVM02]: LCM: [ Start Test ] [[xSQLServerServiceAccount]SetAgentAccount_User] VERBOSE: [SQLVM02]: [[xSQLServerServiceAccount]SetAgentAccount_User] Preferred module SqlServer found. VERBOSE: [SQLVM02]: [[xSQLServerServiceAccount]SetAgentAccount_User] Importing PowerShell module SqlServer. VERBOSE: [SQLVM02]: [[xSQLServerServiceAccount]SetAgentAccount_User] 2019-01-07_13-31-11: Connecting to WMI on 'sqlvm02'. VERBOSE: [SQLVM02]: LCM: [ End Test ] [[xSQLServerServiceAccount]SetAgentAccount_User] in 0.4220 seconds. PowerShell DSC resource MSFT_xSQLServerServiceAccount failed to execute Test-TargetResource functionality with error message: System.Exception: The SQLServerAgent service on sqlvm02\MSSQLSERVER could not be found.

• CategoryInfo : InvalidOperation: (:) [], CimException
• FullyQualifiedErrorId : ProviderOperationExecutionFailure
• PSComputerName : sqlvm02

VERBOSE: [SQLVM02]: LCM: [ End Set ] The SendConfigurationApply function did not succeed.

• CategoryInfo : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
• FullyQualifiedErrorId : MI RESULT 1
• PSComputerName : sqlvm02

VERBOSE: Operation 'Invoke CimMethod' complete. VERBOSE: Time taken for configuration job to complete is 39.98 seconds`

[Francois-Rousseau]

Hi,

Sorry but I don't have any time to work on this...

But, I can see in your log that the service could not be found : The SQLServerAgent service on sqlvm02\MSSQLSERVER could not be found.

I don't think the issue is the one that I open #1173 for.

The Test is not able to find the service, is the service exists on the target?

---

De : SQL_aus_HH notifications@github.com Envoyé : 7 janvier 2019 14:03 À : PowerShell/SqlServerDsc Cc : Francois-Rousseau; Mention Objet : Re: [PowerShell/SqlServerDsc] SqlServiceAccount not working for IntegrationServices, SQLServerBrowser and Fulltext (#1173)

Another month without any progress? I was able to change service user of DatabaseEngine but not able to change SQLServerAgent- Account...

`VERBOSE: [SQLVM02]: LCM: [ Start Resource ] [[xSQLServerServiceAccount]SetServiceAccount_User] VERBOSE: [SQLVM02]: LCM: [ Start Test ] [[xSQLServerServiceAccount]SetServiceAccount_User] VERBOSE: [SQLVM02]: [[xSQLServerServiceAccount]SetServiceAccount_User] Preferred module SqlServer found. VERBOSE: [SQLVM02]: [[xSQLServerServiceAccount]SetServiceAccount_User] Importing PowerShell module SqlServer. VERBOSE: [SQLVM02]: [[xSQLServerServiceAccount]SetServiceAccount_User] 2019-01-07_13-30-53: Connecting to WMI on 'sqlvm02'. VERBOSE: [SQLVM02]: [[xSQLServerServiceAccount]SetServiceAccount_User] 2019-01-07_13-30-53: Current service account is 'NT Service\MSSQLSERVER' for sqlvm02MSSQLSERVER. VERBOSE: [SQLVM02]: LCM: [ End Test ] [[xSQLServerServiceAccount]SetServiceAccount_User] in 0.2180 seconds. VERBOSE: [SQLVM02]: LCM: [ Start Set ] [[xSQLServerServiceAccount]SetServiceAccount_User] VERBOSE: [SQLVM02]: [[xSQLServerServiceAccount]SetServiceAccount_User] Preferred module SqlServer found. VERBOSE: [SQLVM02]: [[xSQLServerServiceAccount]SetServiceAccount_User] Importing PowerShell module SqlServer. VERBOSE: [SQLVM02]: [[xSQLServerServiceAccount]SetServiceAccount_User] 2019-01-07_13-30-53: Connecting to WMI on 'sqlvm02'. VERBOSE: [SQLVM02]: [[xSQLServerServiceAccount]SetServiceAccount_User] 2019-01-07_13-30-53: Setting service account to 'sqlscripting\sqlserv' for service MSSQLSERVER. VERBOSE: [SQLVM02]: LCM: [ End Set ] [[xSQLServerServiceAccount]SetServiceAccount_User] in 17.5470 seconds. VERBOSE: [SQLVM02]: LCM: [ End Resource ] [[xSQLServerServiceAccount]SetServiceAccount_User] VERBOSE: [SQLVM02]: LCM: [ Start Resource ] [[xSQLServerServiceAccount]SetAgentAccount_User] VERBOSE: [SQLVM02]: LCM: [ Start Test ] [[xSQLServerServiceAccount]SetAgentAccount_User] VERBOSE: [SQLVM02]: [[xSQLServerServiceAccount]SetAgentAccount_User] Preferred module SqlServer found. VERBOSE: [SQLVM02]: [[xSQLServerServiceAccount]SetAgentAccount_User] Importing PowerShell module SqlServer. VERBOSE: [SQLVM02]: [[xSQLServerServiceAccount]SetAgentAccount_User] 2019-01-07_13-31-11: Connecting to WMI on 'sqlvm02'. VERBOSE: [SQLVM02]: LCM: [ End Test ] [[xSQLServerServiceAccount]SetAgentAccount_User] in 0.4220 seconds. PowerShell DSC resource MSFT_xSQLServerServiceAccount failed to execute Test-TargetResource functionality with error message: System.Exception: The SQLServerAgent service on sqlvm02\MSSQLSERVER could not be found.

• CategoryInfo : InvalidOperation: (:) [], CimException
• FullyQualifiedErrorId : ProviderOperationExecutionFailure
• PSComputerName : sqlvm02

VERBOSE: [SQLVM02]: LCM: [ End Set ] The SendConfigurationApply function did not succeed.

• CategoryInfo : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
• FullyQualifiedErrorId : MI RESULT 1
• PSComputerName : sqlvm02

VERBOSE: Operation 'Invoke CimMethod' complete. VERBOSE: Time taken for configuration job to complete is 39.98 seconds`

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/PowerShell/SqlServerDsc/issues/1173#issuecomment-451944247, or mute the threadhttps://github.com/notifications/unsubscribe-auth/Am8d-bt3p4S-JGpjJYtJz7Vnsxg1cW97ks5vA1PHgaJpZM4VEUAP.

[jeremiahmoree-adot]

Was this issue opened before the addition of these SQLSetup options?

• ISSvcAccount
• ISSvcAccountUsername

I'm unable to find a way to get SSIS installed with SQLSERVERDSC and wonder if it works at all? This issue suggests no but the fact that those options exists suggest it should work.