DSN-less Connections

[David C. Holley]

This article discusses two methods by which a DSN-less connection can be
made to SQLServer. http://support.microsoft.com/kb/892490

One uses the CreateTableDef the other uses DAO.RegisterDatabase. Any
suggestions as to which would be the preferred approach? Any opinions about
either? I'm inclined to stay awy from the first since it captures user name
and pwd.

 

[Banana]

This article discusses two methods by which a DSN-less connection can be
made to SQLServer. http://support.microsoft.com/kb/892490

One uses the CreateTableDef the other uses DAO.RegisterDatabase. Any
suggestions as to which would be the preferred approach? Any opinions about
either? I'm inclined to stay awy from the first since it captures user name
and pwd.

No different from RegisterDatabase, actually. RegisterDatabase expects a
DSN, which stores the username and passwords in the registry hive
plaintext. Also, I wouldn't really call RegisterDatabase method a
DSN-less connection because it actually creates a new DSN.

If you are concerned about protecting the password, IMHO, the best
policy is to not store the password at all- have the user type in the
password just in the time to execute an ad hoc query. All other objects
(e.g. tabledefs and querydefs)'s Connect property should then contain
only three things: Driver, Server and Database. Access caches the
connection you create and is smart enough to re-use the cached
connection where those three attributes matches.

However, there is a security hole: If the application is allowed to
closing without also quitting Access, it's possible to subsequently open
another .mdb/.accdb while the Access is still open and still have the
full access to the server that original application linked to. For this
reasons, you should ensure that when the last open form closes, this
forces Access to quit and thus close the connection.

AFAIK, this cached connection is not accessible programmically.

HTH.

 

[Stefan Hoffmann]

hi,

If you are concerned about protecting the password, IMHO, the best policy is to not store the password at all- have the user type in the password just in the time to execute an ad hoc query.

As he's using SQL Server he should really think about using Windows
integrated authentication on the SQL Server. So no user/password is
required.

AFAIK, this cached connection is not accessible programmically.

This is correct.

Due this fact, you cannot implement a 'Change User Logon' function
without closing Access.


mfG
--> stefan <--

 

[AG]

Additionally, passthrough queries seem to retain the logon/password if not
using integrated authentication.

 

[Stefan Hoffmann]

hi,

Additionally, passthrough queries seem to retain the logon/password if not
using integrated authentication.

Yup, as Banana said, the connection is cached for all kind of ODBC
related actions.

mfG
--> stefan <--

 

[Banana]

As he's using SQL Server he should really think about using Windows
integrated authentication on the SQL Server. So no user/password is
required.

Yes, that's another option. My only quibble with Windows authentication
is that it does not restrict access to only via this application --
anyone can create a new blank database and get the same access and
bypass the logic embedded in the application.

However, this can be considered to be minor because normally it is
expected that the employees would be trusted to not toy with their
privileges and not all security problems are meant to be solved with
technology. Furthermore, this can be alleviated with proper
security/permissions setup. Nonetheless that's one thing to be aware about.

BTW, I don't think SQL Server is the only one that's capable of Windows
authentication -- I understand Oracle can do this as well. I don't know
how well it does it, though.

 

[Banana]

Additionally, passthrough queries seem to retain the logon/password if not
using integrated authentication.

It's been a while since I tested, but I don't believe this is accurate.
As I explained earlier, you don't have to embed complete connection
string in the passthrough queries. Just embed only three things; driver,
server and database. At the Access startup, run an ad hoc query with the
complete connection string & discard it then all other queries & tables
that connect to the same source will now work because Access caches this
connection. If this ad hoc query isn't run, the passthrough query will
display a connection dialog.

Also, this behavior will be consistent whether you're using integrated
authentication or not.

 

[David C. Holley]

Yes, that's another option. My only quibble with Windows authentication is
that it does not restrict access to only via this application --
anyone can create a new blank database and get the same access and bypass
the logic embedded in the application.

Going with Windows auth doesn't mean that you have automatic access to
SQLServer. Your user ID has to be explicity added to SQLServer security and
given privledges. Even if they can somehow end up 'seeing' the server, they
won't be able to tamper with it - assuming you've set their permissions
appropriately.

 

[Banana]

Going with Windows auth doesn't mean that you have automatic access to
SQLServer. Your user ID has to be explicity added to SQLServer security and
given privledges.

Yes, this is correct. However, I was talking in reference to the
application itself. If someone writes an Access application that does
some validation checks within Access, those would be bypassed if the
user created a new application that used the same credentials. This can
be solved by moving the logic to server-side, using Applications Roles
or setting up the permissions will fix this, but that's a step that
needs to be taken and it can't be taken if the developer isn't aware of
this step.

- assuming you've set their permissions appropriately.

Precisely why I mentioned this.

 

[Douglas J. Steele]

Going with Windows auth doesn't mean that you have automatic access to
SQLServer. Your user ID has to be explicity added to SQLServer security
and given privledges. Even if they can somehow end up 'seeing' the server,
they won't be able to tamper with it - assuming you've set their
permissions appropriately.

I believe Ben's point is that if the user has the ability to interact with
the SQL Server database via the Access application, he/she will also have
the ability to interact with the SQL Server database without the Access
application.

SQL Server does have the concept of Application Roles, but I don't believe
there's any fool-proof way to hide the Application Role credentials in a SQL
Server application, so a determined hacker would be able to determine them.

 

[David C. Holley]

Right, because anyone that we grant access to in SQL Server could use a
System DSN to create their own Access front end and connect to the tables in
SQL Server. The bet that we're making is that the users that we're
installing the app on don't have the interest or ability to do so.

So then, three questions? (And please answer them in-line)

1) Does a ODBC connection (DSN or DSN-less) created on the fly store any
user name/password information if you're using Windows integrated
authentication?

2) If Access creates an ODBC connection (DSN or DSN-less) on the fly, will
the user see the connection if they open the ODBC Data Source Administrator
utility?

3) Only having Access 2007 runtime on the user's machines is another vaible
means of creating an obstacle for them, although they might still be able to
access SQLServer via Excel and ODBC?

 

[David W. Fenton]

Right, because anyone that we grant access to in SQL Server could
use a System DSN to create their own Access front end and connect
to the tables in SQL Server. The bet that we're making is that the
users that we're installing the app on don't have the interest or
ability to do so.

But surely you'd grant them only the same access they'd have in the
application? So what damage could they do?

 

[Banana]

Right, because anyone that we grant access to in SQL Server could use a
System DSN to create their own Access front end and connect to the tables in
SQL Server. The bet that we're making is that the users that we're
installing the app on don't have the interest or ability to do so.

Right. Also, as I noted earlier, this is a case where I think it makes
sense to trust the employees- the employees usually already have an
incentive to keep the system working so they won't want to fiddle with
it. If the company was sufficiently concerned about their disgruntled
employee stealing or tampering with the data, then I would think they
have much more bigger problem on their hands than the problem of
securing it properly! As long their computers are firewalled and
inaccessible externally, this is good enough in my book.

1) Does a ODBC connection (DSN or DSN-less) created on the fly store any
user name/password information if you're using Windows integrated
authentication?

Good question. I don't know this for a fact but I would expect that it
pass some kind of a token or hash to identify a computer/user to the SQL
Server (or maybe to the AD provider). If you look in the registry hive,
however, you won't see anything like that. There's just a key for "Use
Trusted Connection" and LastUser.

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\<Name of DSN>

Note that for SQL Server authentication, the password isn't stored also;
they require that the password be manually entered everytime. However,
for a DSN for other data sources (Access and MySQL for example), the
password is stored there plaintext. Scary!

When you choose "Save Password" in Access at linking-time, what it means
is that the Connect property of the linked table will now embed the
credentials so it's not in Windows Registry but it's certainly in Access
..mdb/.accdb files and it's certainly readable.

This can be prevented on server side by creating a MSysConf table and
inserting in a row with the flag set accordingly. Access will respect
the setting if such table exists on the server. I don't think it'll help
with DSNs though.

2) If Access creates an ODBC connection (DSN or DSN-less) on the fly, will
the user see the connection if they open the ODBC Data Source Administrator
utility?

No. Administrator deals only with DSN definition & configurations, not
the actual connections, generally speaking.

3) Only having Access 2007 runtime on the user's machines is another vaible
means of creating an obstacle for them, although they might still be able to
access SQLServer via Excel and ODBC?

Yes. DSN is not restricted to a single application - if the application
can use ODBC, then it can use any DSN that was created by other
applications/developer. Even in case of using DSN-less connections, it
only means that the user has to find the connection string and use it in
other application to get the same access.

 

[David C. Holley]

Yeah, but its one thing to be able to delete a single record given how the
forms are designed. Its another thing for them to be able to select multiple
records at once and delete them or to have someone create a query that
updates a field in one of the key tables. Its an issue of volume.

 

[David C. Holley]

Right. Also, as I noted earlier, this is a case where I think it makes
sense to trust the employees- the employees usually already have an
incentive to keep the system working so they won't want to fiddle with it.
If the company was sufficiently concerned about their disgruntled employee
stealing or tampering with the data, then I would think they have much
more bigger problem on their hands than the problem of securing it
properly! As long their computers are firewalled and inaccessible
externally, this is good enough in my book.


Good question. I don't know this for a fact but I would expect that it
pass some kind of a token or hash to identify a computer/user to the SQL
Server (or maybe to the AD provider). If you look in the registry hive,
however, you won't see anything like that. There's just a key for "Use
Trusted Connection" and LastUser.

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\<Name of DSN>

Note that for SQL Server authentication, the password isn't stored also;
they require that the password be manually entered everytime. However, for
a DSN for other data sources (Access and MySQL for example), the password
is stored there plaintext. Scary!

When you choose "Save Password" in Access at linking-time, what it means
is that the Connect property of the linked table will now embed the
credentials so it's not in Windows Registry but it's certainly in Access
.mdb/.accdb files and it's certainly readable.

This can be prevented on server side by creating a MSysConf table and
inserting in a row with the flag set accordingly. Access will respect the
setting if such table exists on the server. I don't think it'll help with
DSNs though.

Now that I think about it, I can't see why an ODBC connection would *need*
store a user ID and password if Windows Authentication is being used. The
point of integrated authentication is single sign on access. The user has
authenticated with Window when they logged on, from there its just a matter
of looking at their Windows User Id and determining within the application
that their accessing what rights, if any they have.