June 2, 2008 at 8:44 am
I have an application which is getting info from a MS-Access database, which links Access tables as well as SQL tables. My questions are
1. when I visit the MS-Access database do I use my local station ODBC to view the SQL tables (through Access) or the system is using the server's ODBC to get info.
2. how I could set up the server's ODBC (domain admin needed ? or dbowner of the database is fine) of the linked SQL tables so that all network users could get the info.
Thank you.
June 2, 2008 at 10:29 am
I believe if you query Access, it goes through the DSNs configured inside the Access db to get to the data.
For the SQL stuff, you can setup a DSN the same as you do for Access, but this accesses a database using credentials, not specific tables. If you want to limit this to tables, copy the Access database and distribute to others.
June 2, 2008 at 11:52 am
Thank you for the response.
I am telling you what I have done.
I have setup the ODBC which used a SQL user having the full dbowner rights. I could see all the tables. But when other users access tables of that Access database, they get "ODBC -- connection to 'xxxx' failed."
Do I have to setup an ODBC connection for all users involved ?
Is there a difference between "File data source" and " Machine data source"?
Is the DSN you mentioned under the "File data source" ?
Sorry for too many questions ?
Thanks.
June 2, 2008 at 12:32 pm
Francis Yee (6/2/2008)
Thank you for the response.I am telling you what I have done.
I have setup the ODBC which used a SQL user having the full dbowner rights. I could see all the tables. But when other users access tables of that Access database, they get "ODBC -- connection to 'xxxx' failed."
Do I have to setup an ODBC connection for all users involved ?
Is there a difference between "File data source" and " Machine data source"?
Is the DSN you mentioned under the "File data source" ?
Sorry for too many questions ?
Thanks.
You're going to want to set up the ODBC DSN connection on each machine. Machine Data sources are usually best (meaning - any one who logs onto that machine will then have access to using that DSN): setting them up while an admin on that machine will allow to to kind of keep your connection info secure. File DSN's are convenient, but you might as well hand out the dbowner's password, since it will be in the (text) file DSN, unencrypted.
For what it's worth - you should really look at NOT making the user a DBOwner. Perhaps datareader and datawriter, but DBOwner is a lot of permissions, so you're leaving your DB open to potentially serious abus. Much better yet - properly secure the objects and skip those database roles for a generic user.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 2, 2008 at 1:00 pm
Thanks for the suggestion and advice.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply