SqlRs: Reporting services are not initialized

rdavisunr commented 6 years ago

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

I encountered this issue while continuing on my quest to configure a standalone SSRS server that uses the SQLEngine on a different server.

Two server setup:

Server One running "SQLENGINE" and "SSMS" features (FQDN is DB.mydomain.com)
Server Two running only "RS" feature with Service Account set to NT AUTHORITY\NETWORK SERVICE (FQDN is SSRS.mydomain.com)
xSQLServerRSConfig has the PsDscRunAsCredential set to the credentials of a Domain Admin

The xSQLServerRSConfig resource gets as far as creating the databases on the remote server (DB.mydomain.com) and then fails with a non-informative error message. However, prior to the error, there is a verbose log that says Reporting services DB.mydomain.com\MSSQLSERVER are not initialized. That message seems odd because Reporting services is running on SSRS.mydomain.com

Also worth noting is that once the databases are created, rerunning the config fails due to their presence. There is another issue (#860) logged that also mentions that. Interestingly, the second bullet in that issue also seems relevant (where it mentions that isInitialized is checked and fails, but perhaps needs to wait longer). Perhaps this second bullet is describing the situation I am in as well?

Here is the verbose output from Start-DscConfiguration:

VERBOSE: [SSRS]: LCM:  [ Start  Resource ]  [[xSQLServerRSConfig]DefaultConfiguration]
VERBOSE: [SSRS]: LCM:  [ Start  Test     ]  [[xSQLServerRSConfig]DefaultConfiguration]
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Importing the module MSFT_xSQLServerRSConfig in force mode.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Importing the module MSFT_xSQLServerRSConfig in force mode.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] 2017-12-05_10-13-06: Reporting services DB.mydomain.com\MSSQLSERVER are not initialized.
VERBOSE: [SSRS]: LCM:  [ End    Test     ]  [[xSQLServerRSConfig]DefaultConfiguration]  in 1.4060 seconds.
VERBOSE: [SSRS]: LCM:  [ Start  Set      ]  [[xSQLServerRSConfig]DefaultConfiguration]
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Importing the module MSFT_xSQLServerRSConfig in force mode.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Importing the module MSFT_xSQLServerRSConfig in force mode.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] 2017-12-05_10-13-07: Initializing Reporting Services on DB.mydomain.com\MSSQLSERVER.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Preferred module SqlServer found.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Importing PowerShell module SqlServer.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'ReportServer'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'msdb'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'master'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'ReportServerTempDB'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'ReportServerTempDB'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'ReportServer'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'master'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'msdb'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'ReportServerTempDB'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'ReportServerTempDB'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'ReportServerTempDB'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'ReportServerTempDB'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Caution: Changing any part of an object name could break scripts and stored procedures.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'ReportServerTempDB'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'ReportServer'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'ReportServer'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Warning! The maximum key length is 900 bytes. The index 'IX_Schedule' has maximum length of 1040 bytes. For some combination of large values, the insert/update operation will fail.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'ReportServer'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Caution: Changing any part of an object name could break scripts and stored procedures.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'ReportServer'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'ReportServer'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'ReportServer'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] The module 'CreateRdlChunk' depends on the missing object 'CreateSegmentedChunk'. The module will still be created; however, it cannot run successfully until the object exists.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] The module 'CreateRdlChunk' depends on the missing object 'CreateChunkSegment'. The module will still be created; however, it cannot run successfully until the object exists.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'ReportServerTempDB'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'ReportServer'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'master'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'ReportServer'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'ReportServerTempDB'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'msdb'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'master'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Changed database context to 'ReportServer'.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Getting Reporting Services service information.
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Reporting Services service restarting.
WARNING: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Waiting for service 'SQL Server Reporting Services (MSSQLSERVER) (ReportServer)' to stop...
WARNING: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Waiting for service 'SQL Server Reporting Services (MSSQLSERVER) (ReportServer)' to stop...
WARNING: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Waiting for service 'SQL Server Reporting Services (MSSQLSERVER) (ReportServer)' to start...
WARNING: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] Waiting for service 'SQL Server Reporting Services (MSSQLSERVER) (ReportServer)' to start...
VERBOSE: [SSRS]:                            [[xSQLServerRSConfig]DefaultConfiguration] 2017-12-05_10-13-40: Reporting services DB.mydomain.com\MSSQLSERVER are not initialized.
VERBOSE: [SSRS]: LCM:  [ End    Set      ]  [[xSQLServerRSConfig]DefaultConfiguration]  in 34.2420 seconds. PowerShell DSC resource MSFT_xSQLServerRSConfig  failed to execute Set-TargetResource functionality with error
message: Test-TargetResource returned false after calling set.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : SSRS

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

xSQLServerRSConfig DefaultConfiguration
{
    InstanceName = $SqlData.InstanceName
    RSSQLServer = $sqlServerNodeName
    RSSQLInstanceName = $SqlData.InstanceName
    ReportServerVirtualDirectory = $SqlData.ReportServerVirtualDirectory
    ReportsVirtualDirectory = $SqlData.ReportsVirtualDirectory
    PsDscRunAsCredential = $DomainAdminCreds
    DependsOn = "[xSqlServerSetup]InstallSql", "[File]InstallSqlServerModule"
}

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

Windows Server 2012R2, SQL Server 2014 SP2, PowerShell5.1

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

SqlServer 21.0.17199

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

9.0.0.0

johlju commented 6 years ago

@rdavisunr Would you mind posting you entire config (without any sensetive information) to a public gist and post the link here? I would like to see your config and see if I have time to reproduce the problem.

Could you after the below line add a Start-Sleep -Seconds 45 (or raise it if needed), to try verify if that is the problem. If so maybe we can figure out a better way (than sleeping) to verify if the service is up.

https://github.com/PowerShell/SqlServerDsc/blob/130ea8eeaa091af4d7e7bcaad59fe0342d9b0023/DSCResources/MSFT_SqlRS/MSFT_SqlRS.psm1#L270

rdavisunr commented 6 years ago

@johlju

I checked the Reporting Services Log files and discovered the issue...

As I mentioned before, I had the RSSvcAccount set to NT AUTHORITY\NETWORK SERVICE per the Creating a database on a different server instructions at this link: https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/install-reporting-services

While this allowed the remote ReportSever database(s) to be created successfully, somehow the initialization would fail because the machine account MYDOMAIN/SSRS$ did not have a SQL Login in the remote SQL Server.

The error in the log contained the following: System.Data.SqlClient.SqlException: Login failed for user 'MYDOMAIN\SSRS$'

Interestingly, this issue doesn't occur when using the SQL Server Reporting Services Configuration Manager GUI to create the databases. In that case, the database is created with a User for MYDOMAIN\SSRS$ as illustrated in this screenshot:

machine_account

However, when the databases are created via xSqlServerRsConfig, the user that gets added is the NT AUTHORITY\NETWORK SERVICE account. However, my understanding is that things running as Network Service access remote resources as the machine account (MYDOMAIN\SSRS$ in my case). Here's the screenshot for users added by xSqlServerRsConfig:

network_service

So, it would appear that the Configuration GUI is doing some translation that the Resource is not. I can still provide the gist if you need it, but I don't think you will :)

Thanks

johlju commented 6 years ago

Maybe the GUI creates a SQL Login on the remote server before configuring this. What if you create the Login and the User in the databased (the same as the GUI did) before running the initializing?

When running with a domain account, as the article you linked to also says, that account need to have permission on the remote server, so that will be the same for the Network Service account.

To work around the error, you can change the service account to either Network Service or a domain account. https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/install-reporting-services

Please list the permissions needed to initialize the databases as that should be update in the README.md I think.

johlju commented 6 years ago

@rdavisunr did you need to use Start-Sleep?

rdavisunr commented 6 years ago

@johlju - no, I didn't need to use the Start-Sleep.

It seems like the scripts are almost doing the right thing. However, in the DSC case, the user being added to the DB is NT AUTHORITY\NETWORK SERVICE. That would work fine if SSRS and the DB Engine were on the same server. But, when they are on separate servers, the network service account needs to be translated to be the machine account MYDOMAIN\SSRS$. Somehow, the GUI is doing this translation when it adds users to the remote DB, but the script is not. So, I think you are correct that the GUI is doing something extra here.

It might work if I created the machine account login and user ahead of time. However, the DSC scripts fail if the databases already exists (for different reasons)...

For now, the domain account workaround is fine for us. For permissions, the domain account has the dbcreator role in SQL. I think that is the only role, but I will double check when I return to the office (on Jan. 2nd).

randomnote1 commented 2 years ago

The GenerateDatabaseRightsScript method requires that a UserName be passed to it. By default, the module is using the username the service is running under. Logic would need to be put in place to determine if a local account (e.g. NT AUTHORITY\SYSTEM, NT AUTHORITY\NetworkService, Local Service, or the virtual account is being used to run reporting services.

I'm not particularly fond of allowing computer accounts to connect to database instances due to the security implications. It is far better to us a gMSA when specifying a service account.

dsccommunity / SqlServerDsc