Problem creating data sources in MS Reporting Services

Hi,
I have a question about creating data sources using ODBC connections in MS Reporting Services.  The database I need to connect to is the front-end Access database.  The front-end db has a number to link tables to the back-end db and also other linked dbases.  When I attempt to create my data source to the front end I receive error:  

ERROR[42000][Microsoft][ODBC Microsoft Access Driver] You do not have necessary permissions to use the '(unknown)' object.  Have your system administrator or the person who created this object establish the appropriate permissions for you.

I can establish a data source connection for the back end database with no problem.  Is there an issue with creating ODBC Data sources to front-end Access databases in Reporting Services?
keicy01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vadim RappCommented:
I would try two things:

1. establish link from reporting services to access db without any linked tables; this would reveal if linked tables are the problem, or not.

2. invoked object access audit in local security policy, and checked access to which object was denied.

I thought, though, that since the full name of Reporting services is "SQL Server Reporting Services", they are supposed to work only with sql server, not with Access. Maybe I'm wrong. Are you sure it's possible?

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
YurichCommented:
Of course it's possible. You can connect to any ODBC database from MS Reporting Services.

You may have this situation because your instance has been set in Data Sources using some credentials and now you can't connect to it as you don't provide them for your data source.

To check if your MS RS is working fine with MS Access databases, create a very simple basic database in MS Access yourself (one table, a few rows of data), no passwords, no anything. And create connection to it from MS RS. It should be just working fine. If it does, you can try to look in your data source if you have any specific settings for accessing your database, or see your DB Administrator or System Administrator, they may have information necessary for you.

Another thing, MS RS works best with MS SQL, so what you can try doing is to export data from MS Access to MS SQL, the way to do it will be different for MS SQL 2000 and MS SQL 2005, but it's not very hard to do. Then, if your MS Access databse is still active (it's getting updated etc.), you can do daily updates and keep your data up to date, or if it's static, you can just use your new MS SQL database.

Good luck,
Yurich
0
Jeffrey CoachmanMIS LiasonCommented:
keicy01,

Also,

Open the front end db and see if you can "C.R.U.D." the records:
Create
Read
Update
Delete

At least this migt tell you where the problem is.

JeffCoachman
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

dmitryz6Commented:
check please:
1.table you are connecting not linked table in ODBC Database,if yes you need to link to back end database where table is not linked
2.Be sure When you are creating ODBC ,USER for Reporting services got permission to use this ODBC connection.

0
GRayLCommented:
When you think about it, it should not be possible.  If I own the back end and give someone with a front end access to my table(s), it should not be possible for the front end owner to open up the linked tables to a third party - without the back end owner's knowledge.  If you want access to my tables, you have to get it from me, not another party with whom I have allowed a link or links.
0
NosterdamusCommented:
keicy01,

Just a shot in the dark...

I recall seeing an Error 42000 that was related to using a reserved key-word(s) as field name(s). Check if there is any possibility that one (of more) of your fields is using either a RS or an Access reserved word(s). If you have any doubts, just change the field name(s) to something else, e.g. :"ThisCantBeReservedWord"...

HTH

Nosterdamus
0
keicy01Author Commented:
Thanks for all the suggestions. I will give these a try and get back to you shortly.
0
YurichCommented:
I'm still interested
0
GRayLCommented:
>I will give these a try and get back to you shortly.<  -  and??
0
Vadim RappCommented:
RayL, Yurich is not the asker.

I think the most logical solution would be to connect directly to the source(s) rather than to Access database that in turns connects to the sources. With this via-Access solution, in fact the whole investigation is about which of the numerous security complications along this route is violated.
0
GRayLCommented:
I know, but the asker made the statement - http:#a20814803
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.