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.

David C. Holley wrote:
> 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.

hi,

On 05.03.2010 15:02, Banana wrote:
> 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 <--

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


--

AG
Email: npATadhdataDOTcom


"Stefan Hoffmann" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> hi,
>
> On 05.03.2010 15:02, Banana wrote:
>> 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 <--

hi,

On 05.03.2010 16:16, AG wrote:
> 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 <--

Stefan Hoffmann wrote:
> 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.

AG wrote:
> 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.

"Banana" <Banana@Republic> wrote in message
news:4B912616.1040104@Republic...
> Stefan Hoffmann wrote:
>> 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.

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.

>
> 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.

David C. Holley wrote:
> 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.

"David C. Holley" <David.C.Holley> wrote in message
news:(E-Mail Removed)...
>
> "Banana" <Banana@Republic> wrote in message
> news:4B912616.1040104@Republic...
>> Stefan Hoffmann wrote:
>>> 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.
>
> 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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)