Credentials for Data Sources - Changing Authentication

I have Visual Studio installed on my local laptop, as well as on the report server. The only place reporting services is installed is on the report server.

I have a solution called “Performance Monitoring”, and within that solution is a report of the same name. That report uses 2 data sets, “DSOne” and “DSTwo” and these data sets are pulled from 2 shared data sources. I can edit the report locally and preview it just fine and the results are what we’re looking for.

Right now, the data sources are all set to Windows authentication. We would like to tie the data sources to a specific user ID and password, so that I or anybody else is able to view the report without having to have a separate login for all those people. I have tried changing the data sources' properties in Visual Studio to reflect using a specific login/pass (my own). I have tried this in Report Manager too – editing the data sources there to use my login and pass. Both of these methods have not worked.

In Visual Studio, in the Solution Explorer, I double click on one of the data sources and go to the credentials tab. “Use Windows Authentication” is checked, so I check “use a specific user name and password” instead and then enter my login and password. When I try to preview the report, it gives me the following error and I am stumped as to why this is:

“An error has occurred during report processing. Cannot create a connection to data source “DSTwo”. Login failed for user “xxx”.”

I have also tried editing the login and password through the “edit” button on the “general” tab of whatever data source I’m working with. The “Use Windows NT Integrated Security” box is checked, so I check the “Use a Specific Name and Password” box and again, type in my login info. When I hit “Test Connection”, I get the following:

“Test connection failed because of an error in initializing provider. Login failed for user ‘xxx.”

Hopefully this provided enough detail as to what the heck is going on here. I’ve been playing with these processes for a few days and for whatever reason, it is not working. I’m hoping somebody may have some insight as to why this is.

Thanks,
Matt

If you're using stored windows credentials, you need to check the "use as windows credentials" checkbox on the data source properties page.

-Lukasz