Post reply

Issue with Linking Servers

  • April 17, 2013 at 3:49 pm

    #271288

    This is my current situation.

    I have job scheduled to run every morning that collects data from various servers to a local database on Server A and then we will use that data for reports, etc. The owner of the job is the same user as the SQL service service account and this account has access to all our sql servers and runs other jobs without any issues.

    When the job is ran I receive this error in the history log:

    Executed as user: domain\sqluser. Login failed for user 'sa_Link'. The user is not associated with a trusted SQL Server connection. [SQLSTATE 28000] (Error 18452). The step failed.

    I have created links to each of the server's I am connecting to.

    I have used the following to create the links between the servers:

    EXEC sp_addlinkedserver

    @server=N'ServerB', -- Remote Computer Name

    @srvproduct=N'', -- Not Needed

    @provider=N'SQLNCLI', -- SQL Server Driver

    @datasrc=N'ServerB'; -- Server Name And Instance

    Then under server objects-Linked Servers I see the linked server.

    For the Security tab I don't have any users in the local login.

    The only change here I have done is select the "Be made using this security context" with the sa_link user account and password. This is a local SQL user and is both on server A and server B with the same password.

    Under server options I have also enabled RPC and RPC Out

    Now I have setup the sql domain service account setup to delegate via kerberos due to a need the occurred between to other server's and a programmers PC (2nd hop not liking NTLM, etc).

    But since this is only one hop NTLM or Kerberos will work just fine.

    I've been trying to figure out what I am missing or did incorrectly that isn't allowing the SP's in the job to run.

  • April 17, 2013 at 11:16 pm

    #1607529

    Hope this help...!

    exec sp_dropserver @server = 'ASMREPLICADB' ,@droplogins = 'droplogins'

    go

    -- Add linked server

    EXEC sp_addlinkedserver

    @server = 'ASMREPLICADB'

    , @srvproduct = ''

    , @provider= 'SQLOLEDB'

    , @datasrc= '10.164.0.152'

    GO

    -- Add login (create / use same login account on source and destination )

    sp_addlinkedsrvlogin @rmtsrvname = 'ASMREPLICADB'

    ,@useself = 'FALSE'

    ,@locallogin = 'ASMDBSQLDBA'

    ,@rmtuser = 'ASMportaluser'

    ,@rmtpassword = 'ASMportaluser'

    GO

    -- Test a query

    select top 1 * from [ASMREPLICADB].[geo].[sku]

    GO

    EXEC master.dbo.sp_addlinkedsrvlogin

    @rmtsrvname = N'ASMREPLICADB',

    @locallogin = NULL ,

    @useself = N'False',

    @rmtuser = N'ASMportaluser',

    @rmtpassword = N'ASMportaluser'

    Cheers,
    - Win
    "Dont Judge a Book by its Cover"

  • April 18, 2013 at 12:10 pm

    #1607939

    I shall give that a go and give you an update when i'm done.

    Thank you 🙂

  • April 18, 2013 at 1:51 pm

    #1607991

    Unless you've already done it, you might need to enable the "Data Access" option as well

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • April 24, 2013 at 7:36 am

    #1609498

    Thank you both for your help.

    I am now able to run job's against that database. I am now having a different issue that will need to be resolved in a different thread.

    Thank you again.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply