locked
Which user id is used in each data source? RRS feed

  • Question

  • Using SQ: SSRS 2008 R2 - Beginner - Is there a report or query that provides which user ID is associated with the data source of each report? I need to be able to list out all the data sources being used and which user id is being used in the data source configuration instead of going to each report and using manage to review this. Any help is greatly appreciated
    Tuesday, March 10, 2015 3:40 PM

Answers

  • Hi RudyCat,

    As PrajapatiNeha mentioned that we can find the username in the DataSource Table, but currently it is impossible to decrypt the username.

    I recommend you to using manage to review this.

    Thanks,
    Swallow

    • Proposed as answer by Vicky_Liu Thursday, March 12, 2015 8:23 AM
    • Marked as answer by Vicky_Liu Tuesday, March 17, 2015 1:08 AM
    Wednesday, March 11, 2015 6:23 AM

All replies

  • Hi ,

    You'll be able to find this information from report server database table.

    1.Go to server where SSRS is deploy

    2.Connect to SQL Server Management Studio.

    3.You can use below query on REPORTSERVER database;

    Useful table in your scenario.

    select * from DataSource




    Thanks


    Please Mark This As Answer or vote for Helpful Post if this helps you to solve your question/problem. http://techequation.com

    Tuesday, March 10, 2015 3:59 PM
  • Thank you - This is useful - but the username column is encrypted - how do I unencrypt
    Tuesday, March 10, 2015 4:04 PM
  • Thank you - This is useful - but the username column is encrypted - how do I unencrypt

    as per my knowledge there is no way to decrypt it and below link confirm my understanding :)but there is work around also that have shared on technet;

    https://social.msdn.microsoft.com/Forums/en-US/f65b4d50-abaa-400b-a09d-ae1d7d1ea041/decrypt-the-connectionstring?forum=sqlreportingservices
    WITH XMLNAMESPACES  -- XML namespace def must be the first in with clause. 
        (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource' 
                ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' 
         AS rd) 
    ,SDS AS 
        (SELECT SDS.name AS SharedDsName 
               ,SDS.[Path] 
               ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF 
         FROM dbo.[Catalog] AS SDS 
         WHERE SDS.Type = 5)     -- 5 = Shared Datasource 
     
    SELECT CON.[Path] 
          ,CON.SharedDsName 
          ,CON.ConnString 
    FROM 
        (SELECT SDS.[Path] 
               ,SDS.SharedDsName 
               ,DSN.value('ConnectString[1]', 'varchar(200)') AS ConnString 
         FROM SDS 
              CROSS APPLY  
              SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN) 
         ) AS CON 
    -- Optional filter: 
    -- WHERE CON.ConnString LIKE '%Initial Catalog%=%TFS%' 
    ORDER BY CON.[Path] 
            ,CON.SharedDsName;

    Thanks


    Please Mark This As Answer or vote for Helpful Post if this helps you to solve your question/problem. http://techequation.com


    • Edited by BI_Support Tuesday, March 10, 2015 4:18 PM
    • Proposed as answer by Vicky_Liu Wednesday, March 11, 2015 6:14 AM
    Tuesday, March 10, 2015 4:12 PM
  • Hi RudyCat,

    As PrajapatiNeha mentioned that we can find the username in the DataSource Table, but currently it is impossible to decrypt the username.

    I recommend you to using manage to review this.

    Thanks,
    Swallow

    • Proposed as answer by Vicky_Liu Thursday, March 12, 2015 8:23 AM
    • Marked as answer by Vicky_Liu Tuesday, March 17, 2015 1:08 AM
    Wednesday, March 11, 2015 6:23 AM