?
Solved

restrict users from ODBC useage

Posted on 2016-10-19
5
Medium Priority
?
55 Views
Last Modified: 2016-10-20
Windows Server 2008. I need to be able to restrict the ability to get to all ODBC's on the machine (so the can't go into MS Access and link an ODBC table) OR make all ODBC connections for certain users read-only. The problem is that they use an MS Access app which does use and need R/W perms on certain ODBC's... Im worried that if I go with option 2 above, then their Access app will no longer function. I'd much rather hide all ODBC connections from them (take away all ODBC options when linking tables) and then make the Access app so that it does not allow them to see the backend of the .mdb. Possible?
0
Comment
Question by:QMBB
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 6

Expert Comment

by:efrimpol
ID: 41850956
Don't know if any of your users are Admins (they shouldn't be).

But have you tried removing the ODBC connections via Control Panel, then running c:\windows\sysWoW64\odbcad32.exe As Administrator and recreating the ODBC connections?

Then open the ODBC connections via Control Panel and see if they are there or not. If they are there, see if you can change without getting prompted for Admin Credentials.
0
 
LVL 6

Expert Comment

by:efrimpol
ID: 41850957
I have not tried to log into computer as admin and create connections, but that may be another option.
0
 
LVL 38

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 41850980
One way I solved this problem was to not use Windows authentication.  Instead, I had the DBA create individual logons for each user using their normal network login ID as the user and the password was one that the two of us came up with an algorithm for calculating based on the login ID.  The user was never given his password and we didn't store it anywhere.  So, even if the user was sufficiently technically savvy that he could create an Access app and link to SQL Server, he didn't know any credentials that would get him access.

The tables were linked without storing the password so it wasn't visible in the MSysObjects table.  In the login procedure, I calculated the password and attempted to relink the tables.  If that was successful, I let him in. While in my app, he had whatever access the app allowed but he couldn't create his own app because he couldn't link to the tables.

You could go another step further and include an application password that the user controlled.  So he would log in with his userID and his application password, you would link to the tables and then verify his application password.  This prevents Joe from logging in as Sam unless he also knows Sam's application password.
0
 
LVL 6

Expert Comment

by:efrimpol
ID: 41850987
@ Pat Hartman.

That is impressive!
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41850995
Thanks.  It's not foolproof but I've never had anyone able to crack the algorithm that calculated the password so it has worked well for me for over 30 years at at least two dozen client sites.  I always distributed an app as an .mde and later as .accdr so the users couldn't see any code.  Of course, they could steal the FE and pay to have it cracked so the code could be reconstituted and that would give them a fighting chance of cracking the algorithm.  But most people are curious but not malicious so it has been sufficient.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses
Course of the Month15 days, 9 hours left to enroll

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question