How to set up a generic datasource without credentials in SSRS
I am using SQL Server 2005 with Reporting Services. I have a lot of reports, some of them use common data sources and some do not; some are moving to web services, some to SQL Server databases, and now I am trying to connect to a DB2 database.
I have successfully created a shared data source and report in Visual Studio 2005. I can simply retrieve the data and display it in my report in the designer. Now I am trying to install the report and datasource via ssrs web service (same as for all other reports).
The problem is that most of our data sources use Integrated Security, and for this data source I use "No credentials required" as they are listed in the connection string. Here's my setup code:
Dim definition As New ServiceProxy.DataSourceDefinition
definition.ConnectString = connectionStringIncludingUsernameAndPassword
definition.Extension = "OLEDB"
definition.CredentialRetrieval = ServiceProxy.CredentialRetrievalEnum.None
ServiceWebService.CreateDataSource(dataSourceName, containingFolderFromRoot, True, definition, Nothing)
This also works great and gives no errors, and seems to create the datasource correctly in ssrs. But when I go to run the report, I get this error:
The current action could not be completed because the user's data source credentials that are required to run this report are not saved in the report server database. (RsInvalidDataSourceCredentialSetting)
My report is correctly connected to my data source, and my data source is correctly configured for credentials that are not required, so it is still the same as inside Visual Studio. I have no idea.
a source to share
The problem was several times:
- the username and password were specified in the connection string, not inside the server.
- Db2 connect software installed on report server is deprecated (8 instead of 9)
- the DB2Users user group had no members
After fixing these problems, the report works!
a source to share