Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 509
  • Last Modified:

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

0
running32
Asked:
running32
  • 3
  • 3
2 Solutions
 
Scott PletcherSenior DBACommented:
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.
0
 
Scott PletcherSenior DBACommented:
You can run:

EXEC sp_helpremotelogin 'remoteServerName'

to get details on what login(s) are being used to connect to that remote server.
0
 
running32Author Commented:
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
running32Author Commented:
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
0
 
Scott PletcherSenior DBACommented:
I thought the default, if nothing else was specified, was to make the remote connection using the same login and password as the local connection.

Hmm, but the rest of it makes it sound as if that's not happening in your case.  I assume you don't login as "sql admin".  

Does the user log directly into SQL or go thru some front-end?  Perhaps there is code to use a common id to connect to SQL even though each user logs in with his/her own info [?].

Is there a user "sql admin" on the remote box?

You could run profiler and view more details about connection info.
0
 
JesterTooCommented:
You need to add a "linked server login" or allow the linked server to user impersonation.  Here's a link to a Microsoft how-to for setting it up via Enterprise Manager:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_6_secrty_5jcp.asp

and here are sample scripts for doing it thru Query Analyzer...

-- This script sets the linked server to use an explicit SQL Server login/password
use master
go

sp_AddLinkedSrvLogin
   @rmtsrvname = 'LinkedServerName',
   @useself    = 'false',
   @rmtuser    = 'SqlLoginName',
   @rmtpassword= 'SqlLonginPswd'
go
 

-- This script sets the Linked Server to connect using the same login/pswd that was used on the first connection
use master
go

sp_AddLinkedSrvLogin
   @rmtsrvname = LinkedServerName',
   @useself    = true'
go

I believe the last script simply duplicates the default behavior.  Here is another link that provides much more background info on several alternative ways to setting login credentials for linked servers...

   http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_6e26.asp

HTH,
Lynn
 
0
 
running32Author Commented:
Thanks for your help everyone.  It's working now.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now