question

bukkybu-3222 avatar image
0 Votes"
bukkybu-3222 asked Joyzhao-MSFT edited

query ReportServer database connection string user name

I need to query the user name from SSRS shared datasource connection string. Any assistance is greatly appreciated.

sql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered Joyzhao-MSFT edited

Hi @bukkybu-3222 ,
The ConnectionString in DataSources contains information about the embedded data source of the report. They are sensitive data, so they are encrypted; no official can decrypt them, even system administrators. What I mean is that the UserName you need to query is an encrypted existence.
You could only view the creation of all shared data sources through the following statement:

 USE [ReportServer]
 GO
 SELECT Name
 --, [ItemID] --Primary key
 , [Path]
 , [Description]
 --, [CreatedByID] --need link to get anything usable from here
 , Created.UserName as CreatedByUser
 , [CreationDate]
 --, [ModifiedByID] --need link to get anything usable from here
 , Modified.UserName as ModifiedByUser
 , [ModifiedDate]
    
    
 FROM [dbo].[Catalog]
    
 left join (select [UserID]
             , [UserName]
             from [dbo].[Users]) as Created
         on Catalog.CreatedByID = Created.UserID
 left join (select [UserID]
             , [UserName]
             from [dbo].[Users]) as Modified
         on Catalog.ModifiedByID = Modified.UserID
    
 WHERE [Type] = 5 -- 5 = Shared Datasource
 ORDER BY [Path], Name

Best Regards,
Joy


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.