When you attempt to run this in BIDS, are you using the same account that you use to run SSMS?

Many people use different accounts to launch SSMS versus to launch BIDS.

Once you confirm that, and get it working in BIDS, then we can move on to getting it to work via SSRS.