Solved

restrict users from ODBC useage

Posted on 2016-10-19
5
28 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
  • 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 34

Accepted Solution

by:
PatHartman earned 500 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 34

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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Find out how to use Active Directory data for email signature management in Microsoft Exchange and Office 365.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
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…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now