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.

Answer 2

Could this be related to the boot order of services on your machine.

Just a guess: Maybe there is new functionality in SP3 that checks if the connection credentials are valid. If they are not valid they are cleared.

The problem would then happen if this check is done before SQL server has had time to start. This would explain why they are cleared when the machine restarts.

Answer 3

I have recently experienced the same problem, but I can't connect it to a reboot. It seemed to happen when I checked the solution from source control - we use Team Foundation Server. After disabling the service account a bazillion times, it somehow healed itself and began behaving. I found this post and checked my project folder for the rptproj.user file that benson mentioned, and it has a modified date of the day I had problems, but a create date of close to what I can remember as having created the project, so I will pay attention to this in the future.

Did anyone come up with anything new on this issue?

Answer 4

I realize you may have read this already, but something here could help? http://msdn.microsoft.com/en-us/library/ms159846.aspx

I would pay attention to how the SSRS was installed and also what accounts the servies run as, as well as an domain logon policies.

Answer 5

I haven't gotten around to trying it, but i recently just saw that if you click the edit button to get the connection properties you can save the password. I'll get around to trying this (or you can try it).