ODBC, SQL and Access: File DSN ignores authentication settings

[Tina Robichaux]

I have found info on this problem at MS, but they say this problem
does not occur with SQL 2K and MDAC 2.6 SP2, yet I am still
experiencing it:

I have a SQL user specifically created to SELECT on 5 tables in one
database. I have created an ODBC file DSN using that login/password. I
create an Access DB, attempt to link to the five SQL tables using that
DSN, using a trusted connection. The ODBC drivers ignore the users
permissions, and instead use the PUBLIC permissions.

When the Windows login I am using has access to the database, I get
the tables that login has permissions for.

When the Windows login I am using has no access to the database, I get
a failure for user NULL.

I can forego the trusted connection, enter the login password, and see
exactly the tables I want. I link them to the Access DB, can open them
and see the data.

I close Access and reopen the same Access DB. Now, when I try to open
one of the linked tables, I get the same failure - permissions are no
good.

[MGFoster]

Tina Robichaux wrote:

I have found info on this problem at MS, but they say this problem
does not occur with SQL 2K and MDAC 2.6 SP2, yet I am still
experiencing it:

I have a SQL user specifically created to SELECT on 5 tables in one
database. I have created an ODBC file DSN using that login/password. I
create an Access DB, attempt to link to the five SQL tables using that
DSN, using a trusted connection. The ODBC drivers ignore the users
permissions, and instead use the PUBLIC permissions.

When the Windows login I am using has access to the database, I get
the tables that login has permissions for.

When the Windows login I am using has no access to the database, I get
a failure for user NULL.

I can forego the trusted connection, enter the login password, and see
exactly the tables I want. I link them to the Access DB, can open them
and see the data.

I close Access and reopen the same Access DB. Now, when I try to open
one of the linked tables, I get the same failure - permissions are no
good.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you are using a Trusted_Connection=Yes in your connection string,
that means the SQL authentication will use the NT login authentication
process. Since not everyone has the same login as you set up for the
DSN (or shouldn't) you'll have to remove the "Trusted_Connection"
parameter and hard code the user name and password in the ODBC
connection string. E.g.:

ODBC;DSN=<dsn name>;UID=<user name>;PWD=<password>;... etc.

It is easier to use Roles:

Create a user setting for each user. Then create a Role for your db and
place each user in that Role. For your db's objects assign permissions
only to the Role. Any new user is added to the Role. Then use
Trusted_Connections in the connection string.

Using a Role simplifies user/db-object security administration.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIRGv4echKqOuFEgEQJgBQCg4wFa3m5vi9TlxW8j/PNy1xyQEYIAoOWC
moDtRbmKwARJagVGqSKCMNYG
=GTOV
-----END PGP SIGNATURE-----

[david epsom dot com dot au]

Using a 'Trusted Connection' means using the Windows Login.

If you want to use a SQL User, that means using "With SQL
Server Authentication"

You can put the user name and password into the DSN: you
can save the DSN (file or user DSN) so that only the user
with the appropriate Windows Login can use the DSN. This
allows you to control exactly the permission you give
to each user. However, each user will still be able to
use that DSN for any general purpose, so it will not offer
more security than just putting the Windows User into the
same group as the SQL User that you have defined.

(david)

"Tina Robichaux" <ti**@interland.com> wrote in message
news:38**************************@posting.google.c om...

I have found info on this problem at MS, but they say this problem
does not occur with SQL 2K and MDAC 2.6 SP2, yet I am still
experiencing it:

I have a SQL user specifically created to SELECT on 5 tables in one
database. I have created an ODBC file DSN using that login/password. I
create an Access DB, attempt to link to the five SQL tables using that
DSN, using a trusted connection. The ODBC drivers ignore the users
permissions, and instead use the PUBLIC permissions.

When the Windows login I am using has access to the database, I get
the tables that login has permissions for.

When the Windows login I am using has no access to the database, I get
a failure for user NULL.

I can forego the trusted connection, enter the login password, and see
exactly the tables I want. I link them to the Access DB, can open them
and see the data.

I close Access and reopen the same Access DB. Now, when I try to open
one of the linked tables, I get the same failure - permissions are no
good.