none
Error when configuring SSRS for SQL2017 through WMI

    Question

  • I am experiencing few issues when programmatically configuring SSRS for SQL 2017, through WMI. Any help would be appreciated. Thanks.

    1. The GenerateDatabaseCreationScript WMI method returns a sql script but the script doesn’t execute as no report server Dbs get created. There are no errors on log file either.
    1. To get past Issue # 1, I tried manually executing the script on database, which executes successfully. However, two other WMI methods fail (as part of the our SSRS Configuration process):
      1. BackupEncryptionKey
        • When executing this method with Powershell, the error message returned is:

    {The feature: "The Database Engine instance you selected is not valid for this edition of Reporting Services. The Database Engine does

    report data sources or the report server database." is not supported in this edition of Reporting Services. (rsOperationNotSupported)}

      1. DeleteEncryptedInformation
        • When executing this method with Powershell, the error message returned is:

    {An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk con

    (rsReportServerDatabaseError), The procedure "DeleteEncryptedContent" has no parameter named "@ConfigNamesToDelete".}

        • When investigating this, I noticed this stored procedure in database does not have this parameter.
    Tuesday, February 12, 2019 7:28 PM

Answers

  • Hi Kaushik Bala

    According to your description ,seems your report server host database are created by wmi via coding.

    Seems it has lost something compared to the traditional installation .

    If possible you could try to create a new report server host database via reporting server configuration management .

    See:

    Create a Native Mode Report Server Database

    And then check if the new report server database are works fine or not .

    Hope it can help you.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

    • Marked as answer by Kaushik Bala Wednesday, March 13, 2019 5:23 PM
    Monday, March 11, 2019 3:25 AM

All replies

  • Hi Kaushik Bala

    According to your description ,you could check the information below.

    • For the first issue , the edition is not support, you could check if you have the same edition of ssrs and sql server . Also you should check if both product version are compatible or not . (better use the developer edition or the enterprise version )

    See:

    RS: Database Engine does not meet edition requirements

    SQL Server Reporting Services features supported by its editions

    • For the timeout issue , you would try to modify the  DatabaseQueryTimeout in rsreportserver.config as a large number , And check if it works or not

    See : ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException:

    ps: there exist the @ConfigNamesToDelete in stored procedure .

    You could also offer more detailed information from ssrs log to us for more further research.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.



    • Edited by Eric Liu001 Wednesday, February 13, 2019 2:43 AM
    • Proposed as answer by Eric Liu001 Thursday, February 14, 2019 9:31 AM
    Wednesday, February 13, 2019 2:41 AM
  • Hi Eric,

    Thanks for your response.

    I tried configuring SSRS for SQL 2017 again with the latest version of SSRS (14.0.600.1109). Now, the first issue seems fixed as the report server DBs are created through WMI.

    However, I am still having issue with 'DeleteEncryptedContent' procedure. I still receive error that the procedure has no parameter named '@ConfigNamesToDelete'. When I checked the stored procedure definition on the database, here is what I found:

    USE [ReportServer]
    GO
    /****** Object:  StoredProcedure [dbo].[DeleteEncryptedContent]    Script Date: 3/1/2019 2:03:13 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[DeleteEncryptedContent]
    AS

    -- Remove the encryption keys
    delete from keys where client >= 0

    -- Remove the encrypted content
    update datasource
    set CredentialRetrieval = 1, -- CredentialRetrieval.Prompt
        ConnectionString = null,
        OriginalConnectionString = null,
        UserName = null,
        Password = null

    As you can see, the parameter is missing. Also, below is some related information I found on the SSRS log:

    ReportingServicesWMI!3336!5248!2019/03/01-13:42:33!Administrator!I!Instance SSRS: Entering DeleteEncryptedInformation()
    ReportingServicesWMI!3336!5248!2019/03/01-13:42:34!Administrator!W!DeleteEncryptedInformation() with hr = 0x80131500

    Please have a look and let me know how to resolve this issue.

    Thank you,

    Kaushik


    Friday, March 1, 2019 7:18 PM
  • Hi  Kaushik Bala

    You could try to use the following stored procedure query  to over write dbo.DeleteEncryptedContent , and then test it works fine or not .

    USE [ReportServer]
    GO
    /****** Object:  StoredProcedure [dbo].[DeleteEncryptedContent]    Script Date: 04/03/2019 10:13:02 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[DeleteEncryptedContent]
    @ConfigNamesToDelete AS [dbo].[EncryptedConfigList] READONLY
    AS
    
    -- Remove the encryption keys
    delete from keys where client >= 0
    
    -- Remove the encrypted content
    UPDATE [dbo].[DataSource]
    SET CredentialRetrieval = 1, -- CredentialRetrieval.Prompt
        ConnectionString = null,
        OriginalConnectionString = null,
        UserName = null,
        Password = null;
    
    -- Remove only the OAuth client secret from ConfigurationInfo
    DELETE FROM [dbo].[ConfigurationInfo]
    WHERE [Name] IN (SELECT [ConfigName] FROM @ConfigNamesToDelete)
    
    UPDATE [dbo].[Users]
    SET [ServiceToken] = null
    
    -- Remove KPIs since they are encrypted; Catalog Type=11 is KPI
    UPDATE [dbo].[Catalog]
    SET [Property] = null
    WHERE [Type] = 11
    
    -- Remove encrypted content in DataModelDataSource
    UPDATE [dbo].[DataModelDataSource]
    SET ConnectionString = null,
        Username = null,
        Password = null;
    GO
    

    Hoping for your reply.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

    Monday, March 4, 2019 7:54 AM
  • Hi Eric,

    I tried modifying the stored procedure with above definition but get the following errors:

    Msg 2715, Level 16, State 3, Procedure DeleteEncryptedContent, Line 2 [Batch Start Line 7]
    Column, parameter, or variable #1: Cannot find data type dbo.EncryptedConfigList.
    Parameter or variable '@ConfigNamesToDelete' has an invalid data type.
    Msg 1087, Level 16, State 1, Procedure DeleteEncryptedContent, Line 18 [Batch Start Line 7]
    Must declare the table variable "@ConfigNamesToDelete".

    Regards,

    Kaushik

    Monday, March 4, 2019 2:24 PM
  • Hi Eric,

    We are trying to configure SSRS (for SQL 2017) programmatically in our commercial application, which is used by thousands of our clients. We would appreciate if this can be fixed in future release.

    Thanks,

    Kaushik

    Friday, March 8, 2019 2:29 PM
  • Hi Kaushik Bala

    According to your description ,seems your report server host database are created by wmi via coding.

    Seems it has lost something compared to the traditional installation .

    If possible you could try to create a new report server host database via reporting server configuration management .

    See:

    Create a Native Mode Report Server Database

    And then check if the new report server database are works fine or not .

    Hope it can help you.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

    • Marked as answer by Kaushik Bala Wednesday, March 13, 2019 5:23 PM
    Monday, March 11, 2019 3:25 AM
  • Thanks Eric for the suggestion.

    I tried creating the report server database from reporting server configuration manager, and works fine. I am able to setup SSRS successfully.

    Is there a plan to fix the code for report server database creation by wmi, in a future release?

    Regards,

    Kaushik

    Tuesday, March 12, 2019 4:43 PM
  • Hi Kaushik Bala

    Glad to hear that you have solved your issue , if possible you could mark the appreciated or your solution as answer , so that other member in this forum could get help from It.

    About the wmi code for the report server , you could post your requirement at : : https://feedback.azure.com/forums/908035-sql-server.

    If the requirement mentioned by customers for many times, the product team may consider to add this feature in the next SQL Server version. Your feedback is valuable for us to improve our products and increase the level of service provided.

    I think it would be fix soon.

    Thanks for your support and understanding .

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

    Wednesday, March 13, 2019 5:51 AM