Forcing MS Access db to connect to SQL as logged-in user
-
At our company, we have some business users with Access that create databases, store their credentials in the link to the SQL table, and then distribute the Access file to others in their group. When these other users run the Access file, they show on SQL with the first user's credentials.
The first user has view access to the SQL table, but some of the other users may not, so they now have elevated privileges to that SQL table. Is there a way in SQL to force use of logged-in user credentials instead of stored credentials? Will we need to change how our DBAs provide access to tables, and use a View or Stored Procedure with extra code instead? Right now, if someone needs to view data, they just give them view perms on the table.
I just started with this company, and so am fighting years of 'it always worked before, why change?' however my role is security, and making sure someone does not access data they should not have, is important.
In Oracle, they can compare the logged-in user with the credentials presented, and code around that. Since I am not a DBA myself, I do not know if this is possible in SQL, or if there is a way on the SQL server to disable stored credential processing, or force use of the actual logged-on credentials instead.
Any assistance is appreciated, thank you in advance!
Marc
-
Hi Marc,
I am not too sure about the internals of Access regarding linked servers, but it sounds to me that the issue is related to the way the credentials are passed to SQL Server.
I would look into the following options:
> Request that your users use Network authentication when linking the tables; this should resolve the problem since SSPI works by impersonating the currently logged on user
> Disable database logins in SQL Server to force Network accounts to be used; however this rarely works as it usually breaks other applications that depend on database logins exclusively
There are other solutions available if you do not trust the users, but this is all I can think of with out-of-box features. And you are correct, Oracle does have the network logon even if a database account is used; this is not the case with SQL Server.
Hope this helps.
Herve
Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing -
We have some users that use Access to gain back-end access to our SQL Database. We created an Access database with linked tables to the SQL tables. However, the links DO NOT HAVE the SQL login information saved. Therefore, the first time the user opens Access and tries to open a table, they must enter their SQL username/password.
Let me know if you need more info.
Norene
Have a good day,Norene Malaney
-
Yes, that is how we want ours built too. However, there are 50+ business users who have MS Access, and so they build their own Access DBs. They are not managed or created by the IT department. We ask them not to embed their credentials, but they still do. It is my understanding that there is no way to prevent them from doing this, we can only rely on the users doing it the right way/preferred way, and training them.
Unfortunately, so many of them just want to do it the 'easy way'. By embedding their credentials, then they don't have to worry about each user in their group having SQL access, they can all get what they need via the Access creator's credentials.
Other than preventing them from using Access at all, I don't know of a way to force Access to not store credentials, or for SQL to compare the credentials received to who the logged-on user is, or to ignore stored credentials, and do everything as the logged on user.
My goal is to find a way to prevent them from embedding credentials, or getting SQL to ignore them...
Thanks for your input!
Marc
-
Marc -
You need to switch your users from SQL Server security to integrated/windows security - assuming that your SQL Server is in the same domain as your users it's a pretty straightforward exercise. My experience when dealing with the users is that it's relatively easy to train them to just check "use windows authentication", after all it's one less user name/password combination they have to remember. The eventual goal would be to entirely wean the organization from the use of SQL Server accounts altogether as the benefits of integrated/windows security are pretty well known - MS recommends using integrated/windows security for database access as a best practice.
You don't mention whether or not SOX, HIPAA, etc. are a factor but shared credentials are a no/no in just about every environment I've ever seen, it may be that your companies IT policies already disallow sharing network user names/passwords? If yes, there is a logical extension from network credentials to database credentials...
-
To piggy back on Joe's comment, if your organization is large enough, then your internal auditors can be 'used' to help carry the word both inside the IT organization, to encourage a policy of integrated logins using Windows security rather than separate SS2k5 passwords as well as notifying the users that embedding their credentials is an absolute no-no.
Alternately, by using roles and allowing sharing within the groups, you may be able to 'simplify' the process for these independent folks without causing a loss of security or running afoul of the rules. For a very small price, everyone can safely fly under the radar, without fear of crashing and burning.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply