asked on

Error 18456, Login Failed

I have a trigger, the trigger is connected to a linked server which updates a fields in a table on another sql server.

The sql tables are connected to an access application which when a field is updated fires the trigger. As an admin the trigger works fine for me but when I login as a user I get the error 18456 Login failed for user sqladmin (this is the user entered into the linked server security.

This has something to do with permissions but I'm a bit lost as to what?

Thanks

Scott Pletcher

On the local server, the linked server login for the remote server may be set to NULL = 'self'.

That is, when connecting to the remote server, SQL will attempt to use the same login -- and password -- as it's using on the local server. If that login doesn't have sufficient permissions on the remote server, then of course that doesn't work :-).

You must either:
1) give permissions on the remote server to all logins that might run the trigger
2) change the linked server to always use a specific login, with sufficient permissions :-) of course, and password on the remote server.

Scott Pletcher

You can run:

EXEC sp_helpremotelogin 'remoteServerName'

to get details on what login(s) are being used to connect to that remote server.

running32

ASKER

Sorry to be so slow but could you explain something to me. Btw, thanks for your explanation, it's getting clearer.

The user connects to the sql server using windows permissions, this part is working fine and they are able to update the database etc. When I add the trigger code and they are now updating the another server through a linked server.

I have the security on the linked server set to Be made using this security context: and then the name of the remote login and password.

Does this get overwritten with the persons NT login and password? because the error I am getting is login failed for user "sql admin"

thanks

running32

ASKER

Update when I run the sp above I get There are no remote logins for the remote server bc-sql. How can I perform the update and not the user is this is the case?

Thanks

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

JesterToo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

running32

ASKER

Thanks for your help everyone. It's working now.