I have a small app I'm testing (access 2003) which gets installed on the
workstations of remote users. These remote users are connected to the
server (win 2003) via vpn. Normally the remote users are not part of the
same domain. The app queries sql server express data via pass through
queries.
Up till now the app has build a system dsn for each remote user's
workstation, and plugs in the sa acct/pswd. I am sure that's not very
secure and would like to take a better approach.
I don't understand trusted authentication very well, but would like to
use it if possible(?) The server does not user AD. In my limited testing
I find that desite the fact that I'm able to connect to the server via
vpn and use remote desktop etc, a system dsn that I create locally using
trusted authentication fails with "login failed for user ''. The user is
not associated with a trusted SQL Server connection". I suppose this is
because my local user account is not in the same domain, it makes sense.
Is there any way in this scenario that I can use trusted authentication?
If not, what's the most secure approach to take? I could create a sql
server user account that has adequate rights to the db (and less that
sa) and then continue to create/maintain a system dsn on each remote
workstation. Or I could use a dsn-less connection, but I think that'd
still mean storing the sql server user acct pswd in the passthrough
queries. The app will be distributed as an mde, and there are other
security measures taken, but none of that is very robust. I use the
function PassThroughFixupDAO() from the Chipman book, which would allows
me to dynamically rewrite the passthrough query, but the query would
retain the user name and password if I redo the connection string.
Compared to that a system dsn seems more secure, because the pswd is not
stored in plain text.
|
