SqlRS: failed to execute Set-TargetResource

[goforebroke]

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

PowerShell DSC resource MSFT_SqlRS failed to execute Set-TargetResource functionality with error message:
Test-TargetResource returned false after calling set.
+ CategoryInfo : InvalidOperation: (:) [], CimException
+ FullyQualifiedErrorId : ProviderOperationExecutionFailure
+ PSComputerName : XXXXXX

Here is a little output from the json file in "C:\Windows\System32\Configuration\ConfigurationStatus"

I don't know its saying reporting services is not initialized.

Services service restarting."},
{"time": "2017-12-17T10:02:20.759-7:00", "type": "warning", "message": "[XXXXXXXX]: [[SqlRS]DefaultConfiguration] Waiting for service 'SQL Server Reporting Services (DS1) (ReportServer$DS1)' to stop..."},
{"time": "2017-12-17T10:02:22.813-7:00", "type": "warning", "message": "[XXXXXXXX]: [[SqlRS]DefaultConfiguration] Waiting for service 'SQL Server Reporting Services (DS1) (ReportServer$DS1)' to stop..."},
{"time": "2017-12-17T10:02:25.907-7:00", "type": "warning", "message": "[XXXXXXXX]: [[SqlRS]DefaultConfiguration] Waiting for service 'SQL Server Reporting Services (DS1) (ReportServer$DS1)' to start..."},
{"time": "2017-12-17T10:02:26.157-7:00", "type": "verbose", "message": "[XXXXXXXX]: [[SqlRS]DefaultConfiguration] 2017-12-17_10-02-26: Reporting services XXXXXXXX\DS1 are not initialized."},
{"time": "2017-12-17T10:02:26.235-7:00", "type": "verbose", "message": "[XXXXXXXX]: LCM: [ End Set ] [[SqlRS]DefaultConfiguration] in 74.8350 seconds."},
{"time": "2017-12-17T10:02:26.235-7:00", "type": "error", "message": "PowerShell DSC resource MSFT_SqlRS failed to execute Set-TargetResource functionality with error message: Test-TargetResource returned false after calling set. "},
{"time": "2017-12-17T10:02:26.407-7:00", "type": "verbose", "message": "[XXXXXXXX]: LCM: [ End Set ] "}

RS gets configured, but the script always fails on Set-TargetResource

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

if($Node.InstanceName)
        {

            SqlRS DefaultConfiguration
            {
                InstanceName         = $Node.InstanceName
                DatabaseServerName   = $Node.DBServerName
                DatabaseInstanceName = $Node.DBInstanceName
                PsDscRunAsCredential = $Node.InstallerAccount
            }
        }   

Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running:

Windows Server 2016, SQL Server 2016 SP1-CU5, PowerShell5.1

What module (SqlServer or SQLPS) and which version of the module the DSC Target Node is running:

SqlServer 21.0.17152

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

10.0.0.0 SqlServerDsc

[johlju]

Please see if the issue #936 is related to yours.

[goforebroke]

Issue resolved.
We run post configuration security scripts after we install SQL Server via [SqlSetup]. One of our security scripts inadvertently denied access to the [TSQL Local Machine] . If SSRS is installed on the same machine as DB Engine, it will try to connect on the [TSQL Local Machine] endpoint when you configure SSRS with [SqlRS]. Once we reapplied the grant and fixed the post configuration scripts, we are now able to configure SSRS via [SqlRS] without issue.

[johlju]

Happy you got it to work. I'm not following what you mean with [TSQL Local Machine], can you explain? Do you mean it wasn't allowed to connect to the DB Engine after your security script ran?

[goforebroke]

If you run the following query in SQL Server "select * from sys.endpoints", you will see all the endpoints that are available to connect to SQL Server. In addition, you will see endpoints that are available for high availability like SQL AlwaysON if you have enabled SQL AlwaysON.

Dedicated Admin Connection
TSQL Local Machine
TSQL Named Pipes
TSQL Default TCP
TSQL Default VIA
Hadr_endpoint

We had accidentally revoked permission to the [TSQL Local Machine] endpoint to the public role. In the SQL Server error log, I noticed login failures to this endpoint at the same time I ran the SqlRS script to configure SSRS. Connection on this endpoint was being made because the SSRS service was co-located on the same server as the DB engine hosting the ReportServer databases. I realized this was a permission issue on that endpoint. Once I granted connect permission back on [TSQL Local Machine] endpoint to the public role, the configuration ran without issue.

If I had been configuring SSRS in a split architecture (DB on one server and SSRS on another), I would not have received this error as the connection to configure SSRS would have been made on the [TSQL Default TCP] endpoint.

[johlju]

@goforebroke Ah, I'm with you now! That's for clarifying that and the detailed explanation! Much appreciated,

Shall we close this issue at this point?

[goforebroke]

Pls close the issue....

BTW I have used all your DSC resources to automatically deploy through our private cloud. We are able to order/provision a new server (default/named instance w/standards) with any of the features (DB, SSAS, SSRS, IS, or Mgmt Studio) in under 30 minutes. We are using these resources and the DBATools toolkit to apply post configs...Thanks for making such a useful resource.

[johlju]

That's pretty awesome! So happy to hear that the resources are being used in real life. and glad that they give you such a high degree of automation! Thanks a lot for sharing that! I'm head deep in my lab environments trying to find bugs and squash bugs, so never get to appreciate the production side of it. Please continue submit issues for questions, bugs and how the resource can improve even more. 😄

If you ever write a blog post about your road to automating you provisioning, please share!

Thanks for using the resources!