Solved

restrict users from ODBC useage

Posted on 2016-10-19
5
22 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 5

Expert Comment

by:efrimpol
Comment Utility
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 5

Expert Comment

by:efrimpol
Comment Utility
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
Comment Utility
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 5

Expert Comment

by:efrimpol
Comment Utility
@ Pat Hartman.

That is impressive!
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Script is being strange 8 48
identify all unused queries, forms and reports 10 26
lync 2013 7 30
Multiple queries for a form 14 12
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

772 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

11 Experts available now in Live!

Get 1:1 Help Now