By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,601 Members | 989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,601 IT Pros & Developers. It's quick & easy.

SQL Server Connection User Security

P: n/a
I'm interested in opinion on how to handle the user security when connecting
an Access Application to a SQL server. It will be a front-end mdb installed
on each user's PC, connecting to the shared database.

Option 1 is to create the connection using Trusted Connections. At the
moment, this means we need to add each user into the SQL Server before they
can connect. Is this normal or is this a missed configuration of the SQL

Option 2 is to set-up a generic user like "AppUserX" and specify it into the
UID/PWD of the Access connection string. My question is with 10-20 users
utilising this app and connecting as the same user, does this cause a
problem with SQL user licence connections?
Thanks in advance.
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply

P: n/a
This is a very large and complex issue.

I think studying MS-SQL security may be helpful to anyone:

In my opinion, Access and MS-SQLwhen used together, (beyond the
trivial), may not provide adequate security and for this reason I no
longer create such applications.

Suppose J Doe has login privileges to the database, and SELECT, INSERT,
UPDATE, and DELETE permissions for a Table. He/she will need these to
edit data using a form bound to that Table. What will prevent J Doe
from using this login and these permissions from another Access
Database, from another application, or more or less directly through
Enterprise Manager?
Ah, you say, use a View and restrict the View to data that is within J
Doe's authority. How exactly will you do that? If there are just John,
Jane and Jezebel Doe and users almost never change it will not be too
onerous. But if there are 200 users at 100 different sites, and seven
of these change per week?
Groups? In the case I suggest there will be 100 groups.

MS-SQL server has a solution. It is called application roles. When one
uses application roles it is your application that has permissions, not
users. So beyond your application, the user can do nothing.
With ADPs, application roles do not work, or work so poorly that they
cannot be used in any complex application. This is because application
roles are not really application roles, but connection roles, and ADPS
use many connections, some of which are documented and clear, and some
of which are hidden and cannot be identified, and, worst, some of which
seem to change according to the phases of the moon, days of the week,
or smog levels.

Can one use application roles with mdbs and odbc connections? I don't
know but I'd love to hear from someone who has done so successfully.

Further I may say that no one I know of agrees with me.

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.