SQL Server Reporting Services Datasource keeps losing database login credentials

Question

In my development environment, every time I reboot windows (which must be done at least daily for me), all of my Shared SSRS Datasources lose their credentials.

Currently I have them set up to log into the database using a fixed credential, but on reboot all the datasources pop over to using no credentials. Granted, it's only in the dev environment, and I can just check out/update the datasource/check back in and it will work fine... until I reboot again.

FYI, I've been using these Shared Datasources for at least 2 years and no problems, but in the last month or so, it's been a recurring daily problem.

Help?

Answer 1

I'm assuming you are talking about the Shared Data Sources in a Report Server project in Visual Studio, as opposed to a Data Source created directly on Reporting Services. The latter, the data is stored all in the ReportServer database that was specified when setting up SSRS.

Now, as for the .rds file used in Visual Studio, if you open the file up in a text editor, notice that the username and password is not stored in the file. It is actually stored in the .rptproj.user file. So, check that someone didn't remove the .user file from source control (.user files shouldn't be in source control, but in your case...).

This is scenario is testable by entering your credentials, saving all files, and exiting Visual Studio. Find and delete the .rptproj.user file, and open your Report Server project up again and see the credentials gone!

A work around is add the "User ID=user;Password=pass" as part of the Connection String. When the .rds is opened up, the Connection String won't show this portion, but the Credentials tab should have the right values.