Problems with linked server security (mdb)

I have a view in SQLDB A that is built out of a linked server that points at
an Access (mdb) database.
The linked database is set that security should make connections 'without
using a security context'.
This works perfectly for user sa. If I select from the view I get data.
If I use another SQL authenticated user and make the same select I get:
Server: Msg 7416, Level 16, State 2, Procedure uvTYarDuty, Line 2
Access to the remote server is denied because no login-mapping exists.
Why? As I said the linked server is set to use no authentication which is
supposed to work perfectly for mdb databases...so why cant the non-sa user
read the linked database?
needless to say they have select permissions on the view itself.
This is driving me crazy ;)
Al Blake, Canberra, AustraliaAl Blake
When the "Be made without using a security context" is selected, any queries
submitted by a login not defined will be passed to the remote server using
guest permissions
Do you have a guest user? Are you sure that the "Not be made" radio button
is selected?
Linda Deng from Microsoft wrote
You can resolve the problem by mapping the SQL Agent startup account on the
local server to a remote user on the remote server. You can make it through
the SQL Enterprise Manager. Here's how the mapping can be done:
1. Right-click on the linked server and select the Properties option.
The
Linked Server Properties dialog will appear.
2. Select the Security tab.
3. Add the account that the SQL Agent uses in the 'Local login' field.
4. Add the username on the remote server that the SQL Agent is to use
in
the corresponding 'Remote user' field.
5. Supply the appropriate password.
"Al Blake" <al@._delete_this_.blakes.net> wrote in message
news:uygffQdKFHA.4064@.tk2msftngp13.phx.gbl...
> I have a view in SQLDB A that is built out of a linked server that points
at
> an Access (mdb) database.
> The linked database is set that security should make connections 'without
> using a security context'.
> This works perfectly for user sa. If I select from the view I get data.
> If I use another SQL authenticated user and make the same select I get:
> Server: Msg 7416, Level 16, State 2, Procedure uvTYarDuty, Line 2
> Access to the remote server is denied because no login-mapping exists.
> Why? As I said the linked server is set to use no authentication which is
> supposed to work perfectly for mdb databases...so why cant the non-sa
user
> read the linked database?
> needless to say they have select permissions on the view itself.
> This is driving me crazy ;)
> Al Blake, Canberra, Australia
>
>|||Thanks for the reply,
Can you clarify - do you mean I should have a guest user in SQL, in Access
or both?
If non-mapped logins are made using guest permissions then why does it work
for the sa user?
I have NO logins defined AT ALL in the linked server, so surely sa should
bre treated in the same way as any other SQL user when attempting to open
the Access tables?
...but sa works and another user doesnt....and I have the 'no security
context' checkbox selected (if I change that then sa cant connect either so
it is obviously having some effect).
Regards
Al.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uEvcu4eKFHA.2812@.TK2MSFTNGP15.phx.gbl...
> Al Blake
> When the "Be made without using a security context" is selected, any
> queries
> submitted by a login not defined will be passed to the remote server using
> guest permissions
> Do you have a guest user? Are you sure that the "Not be made" radio button
> is selected?
> Linda Deng from Microsoft wrote
> You can resolve the problem by mapping the SQL Agent startup account on
> the
> local server to a remote user on the remote server. You can make it
> through
> the SQL Enterprise Manager. Here's how the mapping can be done:
> 1. Right-click on the linked server and select the Properties option.
> The
> Linked Server Properties dialog will appear.
> 2. Select the Security tab.
> 3. Add the account that the SQL Agent uses in the 'Local login'
> field.
> 4. Add the username on the remote server that the SQL Agent is to use
> in
> the corresponding 'Remote user' field.
> 5. Supply the appropriate password.
>
> "Al Blake" <al@._delete_this_.blakes.net> wrote in message
> news:uygffQdKFHA.4064@.tk2msftngp13.phx.gbl...
> at
> user
>