odbc connections for users on vpn

[ableone]

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.

 

[Stefan Hoffmann]

hi,

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.

Depending on the interaction of your application with local data
(Outlook, documents, printing) it may be worth considering using a
Terminal Server...


mfG
--> stefan <--

 

[sleepy]

hi,

Depending on the interaction of your application with local data
(Outlook, documents, printing) it may be worth considering using a
Terminal Server...


mfG
--> stefan <--

I do use TS for some apps; however this particular app will benefit from
a local install of Access.

I hope someone can read what I wrote and offer some feedback re the odbc
options (dao btw).

 

[Guest]

It's not clear why the remote users are not part of the domain.

The ordinary way to do this in a MS Windows environment
would be to give the users Domain logins, and use MS VPN
with trusted authentication. Remote users can also use domain
login in disconnected mode (the credentials are cached locally),
but that is not necessary: using VPN users login to the VPN
if their local login is not trusted.

Using a 'hardware' (router) VPN, with no user VPN login, the
ordinary way would be to always use domain login over the
VPN.

Using a Cisco VPN client in this situation is making a rod for
your own back: the authentication routines are not standard,
except in the sense that they are the standard Cisco VPN
authentication routines, and Cisco is the MS of VPN. There
is no way to do "trusted authentication" using the Cisco authentication,
you do have to do a separate authentication.

Assuming that you are using MS VPN, your problem is that
the ODBC is not connecting over the VPN. You have multiple
connection paths to the server. This is bad. You need to
set up your subnets and server address and dsn and dns
so that there is no way to connect to the ODBC server
except over the authenticated VPN connection.

(david)

PS, terminal services is probably a better choice if possible.

 

[ableone]

Informative post, thank you. Yes this is using a cisco vpn and there is
nothing I can do about that part. TS is not an option for this
particular app because the users need to interact with locally installed
software.

So, given the cisco vpn, would you guess that the best solution for now
is to keep using system dsns like I currently am?

 

[Guest]

[rant] There are no good solutions -- role based security with
Access/SQL Server is broken, just like DAO/SQL Server
transactions. [/rant]

You still have several options, but you need to ask in a Cisco
or Networking or Windows Server forum.

You can configure the Cisco Software VPN Client to load
before login (use the latest version to avoid a security problem
with some earlier versions). With the client loaded before
login, you can do Windows Login with the Cisco VPN client
already loaded and the VPN already started - so you can
do domain login over the VPN.

A ?user dsn? might be better than a system dsn - ?It
would be protected by Windows login?

The other things I can think of would require more
cisco/windows knowledge than I have. I think that there
must be a way to do domain login after Windows login,
I just don't know it.(Other than TS).

There may be a way other than MS VPN or dial up to get
a 'demand dial' connection: I just don't know it. You may
be able to configure a trusted kerberos login server, or you
may be able to configure your VPN server to do a system
login. - I don't know how.

I think you still need to ask somewhere else about domain
login after windows login, there may be a trivial user solution
that I don't know about -- I talk big, but I don't know about
this stuff either...

(david)
`

Informative post, thank you. Yes this is using a cisco vpn and there is
nothing I can do about that part. TS is not an option for this
particular app because the users need to interact with locally installed
software.

So, given the cisco vpn, would you guess that the best solution for now
is to keep using system dsns like I currently am?

is

 

[ableone]

You're input is helpful, thanks. Have you heard of any way to piggyback
a trusted odbc connection with a remote desktop session? Some of the
users have rd sessions running in addition to the locally installed
application.

I'll see what I can find out about the cisco config option.

[rant] There are no good solutions -- role based security with
Access/SQL Server is broken, just like DAO/SQL Server
transactions. [/rant]

You still have several options, but you need to ask in a Cisco
or Networking or Windows Server forum.

You can configure the Cisco Software VPN Client to load
before login (use the latest version to avoid a security problem
with some earlier versions). With the client loaded before
login, you can do Windows Login with the Cisco VPN client
already loaded and the VPN already started - so you can
do domain login over the VPN.

A ?user dsn? might be better than a system dsn - ?It
would be protected by Windows login?

The other things I can think of would require more
cisco/windows knowledge than I have. I think that there
must be a way to do domain login after Windows login,
I just don't know it.(Other than TS).

There may be a way other than MS VPN or dial up to get
a 'demand dial' connection: I just don't know it. You may
be able to configure a trusted kerberos login server, or you
may be able to configure your VPN server to do a system
login. - I don't know how.

I think you still need to ask somewhere else about domain
login after windows login, there may be a trivial user solution
that I don't know about -- I talk big, but I don't know about
this stuff either...

(david)
`

Informative post, thank you. Yes this is using a cisco vpn and there is
nothing I can do about that part. TS is not an option for this
particular app because the users need to interact with locally installed
software.

So, given the cisco vpn, would you guess that the best solution for now
is to keep using system dsns like I currently am?

In article <[email protected]>,

 

[Guest]

You're input is helpful, thanks. Have you heard of any way to piggyback
a trusted odbc connection with a remote desktop session? Some of the
users have rd sessions running in addition to the locally installed
application.

Don't know.

You could try Named Pipes (instead of TCP/IP) and "Net use " -- if you
aren't already connected to the target system, connecting to a file share
will establish a trust relationship that Named Pipes will use.

(david)

I'll see what I can find out about the cisco config option.

[rant] There are no good solutions -- role based security with
Access/SQL Server is broken, just like DAO/SQL Server
transactions. [/rant]

You still have several options, but you need to ask in a Cisco
or Networking or Windows Server forum.

You can configure the Cisco Software VPN Client to load
before login (use the latest version to avoid a security problem
with some earlier versions). With the client loaded before
login, you can do Windows Login with the Cisco VPN client
already loaded and the VPN already started - so you can
do domain login over the VPN.

A ?user dsn? might be better than a system dsn - ?It
would be protected by Windows login?

The other things I can think of would require more
cisco/windows knowledge than I have. I think that there
must be a way to do domain login after Windows login,
I just don't know it.(Other than TS).

There may be a way other than MS VPN or dial up to get
a 'demand dial' connection: I just don't know it. You may
be able to configure a trusted kerberos login server, or you
may be able to configure your VPN server to do a system
login. - I don't know how.

I think you still need to ask somewhere else about domain
login after windows login, there may be a trivial user solution
that I don't know about -- I talk big, but I don't know about
this stuff either...

(david)
`

Informative post, thank you. Yes this is using a cisco vpn and there is
nothing I can do about that part. TS is not an option for this
particular app because the users need to interact with locally installed
software.

So, given the cisco vpn, would you guess that the best solution for now
is to keep using system dsns like I currently am?

In article <[email protected]>,

 

[ableone]

Don't know.

You could try Named Pipes (instead of TCP/IP) and "Net use " -- if you
aren't already connected to the target system, connecting to a file share
will establish a trust relationship that Named Pipes will use.

(david)

How would I try that? I don't see a named pipes option in the odbc data
sources dialog. I've never used named pipes; I see that I'd need to
enabl in sql server config (for the server). But beyond that I am not
sure what to do; will read around.

 

[Guest]

Choose named pipes in the in the DSN "client configuration".
instead of using 'connect to sql server to obtain configuration"


Your SQL Server can be set up to default to Named Pipes,
but it probably isn't.

If using a DSN'less connection, specify the network driver
you wish to use, ;network=dbxxxxxx; I don't know the value.

Named pipes are simple and generic in that they look exactly
like a file, you can do a windows 'file open' on a file, or a serial
port, or a named pipe, but for networking that means named-pipe
over TCPIP, so when TCPIP became ubiquitous, it became more
common to use the TCPIP driver. However, right now people
report better results using the 'native sql server driver' rather than
the older TCPIP driver. I don't have the current drivers installed
on my PC, and I haven't read any discussion about what they actually
do.

(david)