Results 1 to 5 of 5

Thread: linked server

  1. #1
    Join Date
    Sep 2002
    Posts
    17

    Unanswered: linked server

    hello
    we have a job which will update the data in one server and insert data in another server.for this we created a linked server and wrote a procedure to update and insert the data in both the servers. when we are executing the procedure manually it is working fine. but when we tried to execute the procedure through a local job. job execution is getting failed and encountered the following error

    Msg 18456, Sev 14: Login failed for user '\'. [SQLSTATE 28000]

    can u please suggest a proper solution for this. we already gave all permissions and roles to both the users.

    thank you.

  2. #2
    Join Date
    Aug 2002
    Posts
    20
    Hi Karraaruna!

    You probably get problem with “user-right”. Your sql-servers and specially sql agents should not be running on local account. You must give the sql-server (with agents) a nt-account that has user-rights on the other machine.

    Becuse when the sql-agent start the job, then the job is personated with the account of sql-agent. With other word, the next machine see the other machine as a user.
    /Mada

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Mada, almost. NT accounts/authentication will only work if both servers are using Active Directory. If you are not using Active Directory then on your linked server proerties window select the Security tab, and set up a login mapping from the NT account your SQL Agent uses to a SQL Server authenticated id on the remote server.
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Aug 2002
    Posts
    20
    Hi Paul Young!


    Active Directory or not, if you give a nt-account "user-rights" on a another sql-server it will be able to execute an stored procedure. It’s no different then any other user.

    It works....

    /Mada

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Agreed, however the original question was about executing a stored procedure on a linked server. If you are using an NT Authenticated ID when you login and are not using Active Directory the linked server will not be able to authenticate the user unless you mapp the NT Authenticated ID to a server authenticated id.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •