How to Use ACCDB as data source in SSRS 2008

[Beth Gibson]

When passing credentials from SSRS 2008 to MS Access 2007 in a Report Project on a client machine, the following works fine:

Connection string: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=MyDatabase.accdb

Credentials: User Name = admin Password IS BLANK

The problem is after deployed to a SSRS server the Credentials cannot be passed as 'admin' with no password.

As I understand it, since the data source resides on a separate share drive in another server, the credentials to access that share must pass first, then the USER ID of admin must pass to MS ACCESS to actually use this data source.

I've tried storing credentials to access the server share then pass the User ID = admin in the connection string itself, but to no avail.

I do not want to set up a work group in MS Access just to resolve this issue.

I may look into writing VBA to convert all Users to Admin knowing that only those users with access to the share drive should get in anyway - but that's under another category in this forum.

Any ideas on how to configure SSRS to pass regular credentials to access a server share drive along with the User ID = ADMIN for logging into the MS Access Database on that share?

[ck9663]

To access a shared drive, it must be first accessible by your sql server. I assume it is, coz you're just asking about passing credentials. I have not tried the setup that you currently have. All I can think of is some security account delegation issue, try reading this...

Good Luck!!!

~~ CK