?
Solved

restrict users from ODBC useage

Posted on 2016-10-19
5
Medium Priority
?
64 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 40

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 40

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Transferring FSMO roles is done when an admin wants to split roles between certain Domain Controllers or the Domain Controller holding the Roles has been forcefully demoted using dcpromo / forceremoval
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

862 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