ODBC in MS Access ignores DSN authorization method

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.
Things are working the way they should. Whenever you set
Trusted_Connection to yes, then the Windows login is used, so if that
login doesn't have permissions, you get an error. Instead of using a
DSN, it is recommended that you write code to set the connection
string and relink the tables. You can supply a form that asks whether
to use a trusted connection or a SQL login, construct the connection
string, and link the tables using VBA/DAO. There is code for linking
tables somewhere on the Access mvp site (www.mvps.org) and there are
examples of valid ODBC connection strings at
http://www.able-consulting.com/ADO_Conn.htm. Stay away from any
linking code that uses ADOX--the tables link read-only depending on
the version of MDAC.
--Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On 19 Apr 2004 13:15:16 -0700, tina@.interland.com (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.
|||I understand about the trusted connection setting - but even when I do
not use that, it ignores the DNS username and pw on the next Access
open, and uses my NT permissions (see last paragraph of original post).
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Regarding the known issue that you already have found, it's
the clients that need to be using MDAC 2.6 SP2. You may want
to check the mdac configuration on the client using
component checker. The odbc driver that causes the issue is
sqlsrv32.dll - version 2000.80.194
You can download the MDAC component checker
from:
http://msdn.microsoft.com/data/downl...s/default.aspx
-Sue
On Tue, 20 Apr 2004 08:06:43 -0700, Tina Robichaux
<tina@.interland.com> wrote:

>I understand about the trusted connection setting - but even when I do
>not use that, it ignores the DNS username and pw on the next Access
>open, and uses my NT permissions (see last paragraph of original post).
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!