I am running a project to go over all logins of all our SSRS 2012 datasources and I need a complete list of all datasources, INCLUDING the loginname used to connect. I couldn't find it anywhere.
I am convinced the datasource loginnames must be stored somewhere inside the database, or the RDL's themselves.
Does anyone have a clue how I can list all datasource login names of these different datasources?
See attached pic for details:
The Content column in Catalog table only contains the connection string and whether the data source is using Windows credentials (authentications) or not the username
SELECT SDS.name AS SharedDsName
,SDS.[Path]
,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
FROM dbo.[Catalog] AS SDS
WHERE SDS.Type = 5
I did found the Username of the data source with this query :
select DSID, ItemID, SubscriptionID, Name, Extension, Link,
CredentialRetrieval, Prompt, ConnectionString,
OriginalConnectionString, OriginalConnectStringExpressionBased,
UserName, Password, Flags, Version
FROM DataSource
But it's encrypted – not only the password is encrypted, but also the username itself.
I would appreciate a lead into the right direction.
Thanks in advance!
Roni.
Best Answer
After doing some digging, I doubt you'll be able to find an unencrypted list anywhere. It's storing it in the report server, so the SSRS engine is encrypting it on it's own; i.e it's generating a key and salt in the engine code and using it to encrypt\decrypt those columns. You won't be able to query it via T-SQL, but you might have better luck using PowerShell to parse through the HTML on the reporting front end.
I believe the tag in question is the value for input named "ui_txtStoredName". I attached a picture as an example.