Posted by: Colin Smith
Today I ran into an issue with DataSources in SSRS in SQL Server 2008 R2. This system was upgraded from SQL Server 2000 and we had some minor hicups getting it all upgraded and running but we were able to do it without too many issues. We handed the server over to the dev team and they started testing and getting RS configured. They were able to use all the reports and datasources that they had and did not have any major issues. Until today. I got a call saying that they were getting access denied when setting up a new datasource. I searched and searched for the issue. The RS log only showed that the user did not have access to create the object. What is even more strange is that is the case for all users. Even me and I am sysadmin in SQL Server and RS and I am in the content manager role as well. Same error. I found, after some searching, that this may be because IIS is set to allow anonymous access. That might make sense but since I am now on 2008 R2 and that does not use IIS it did not make sense but I changed it anyway to no avail. The other thing that came up was UAC. Well I am on Server 2003 so that is not it either.
Then I found out that if I create the data source and save it without testing it that I can then go back into it and test it just fine and the data source does work to pull data for the reports. I still have not found a resolution to this and would love to hear what some of you think it might be and if anyone else has run into this issue. It is not something that is stopping production but it is something that I would like to get resolved.