Linked Server via Windows Authentication RRS feed

  • Question

  • Hi,

    I've two SQL 2005 servers, A (mixed) and B (window auth). I want to create a linked server in A which can connect to B. A's SQL Service account is logged on using a local user account called A\sql_svc. I'm able to create the linked server using my domain ID, which has sysadmin to both A & B. However, if I run the job via SQL Agent, it gave me the below error :

    Executed as user: A\sql_svc. Access to the remote server is denied because no login-mapping exists. [SQLSTATE 42000] (Error 7416).  The step failed.

    What kind of credential should I use to create the linked server so that the job is able to run ?

    Please enlighten.

    TIA !

    Tuesday, November 17, 2009 2:34 AM

All replies

  • Check that the remote login actually has permission to access it's default database ..
    Are you able to access the remote server for normal select queries ? is this problem occurring only from the jobs ?
    What is the account with which your SQL AGENT runs ?
    Thanks, Leks
    Tuesday, November 17, 2009 5:37 AM
  • If I log in using my domain ID, I'm able to query linked_server.master.sys.objects from A.

    The SQL Agent is also running using A\sql_svc. My domain ID has sysadmin in both servers.

    Unless A\sql_svc needs some form of authentication to B ?

    Tuesday, November 17, 2009 6:28 AM
  • Can you try to impersonate the A/sql_svc from A to authenticate the remote machine B with the domain account credentials in your linked server definition . Then run the job again.
    Thanks, Leks
    Tuesday, November 17, 2009 6:37 AM
  • I assume from your description that you have a job with a TSQL jobstep. Unfortunately, Sql Agent runs all tsql jobsteps under sql agent's credentials.
    What you can do is prefix your TSQL script with EXECUTE AS (see msdn for options etc.) You can impersonate your domain user.

    Long term, we plan to add a "Run As User" option for TSQL jobsteps.
    Let me know if it works and what you think.

    Wednesday, November 18, 2009 6:44 PM
  • Hi tibid,

    I've written a test sp with execute as 'my domain id' and schedule it to run via the agent.

    Could not obtain information about Windows NT group/user 'my domain id' , error code 0x5.

    Am I doing it correctly ?

    • Edited by limssd Friday, November 20, 2009 2:55 AM more details
    Friday, November 20, 2009 2:51 AM
  • Hi Lekss,

    A\sql_svc has no credential in machine B. How do I create a linked server with A\sql_svc with my domain account  as both are windows account ?

    Friday, November 20, 2009 2:53 AM
  • The error says the domain id is not used correctly. When using execute as option use 'domainname\username' and make sure this has access to SQL server.
    Thanks, Leks
    Friday, November 20, 2009 4:37 AM
  • This will only work if the SQL Server service is running as some user that itself has permissions to interrogate the domain controller.
    It looks like the Sql Server service account (Local System in your case) does not have permissions to the domain.
    I am not sure how it works when you connect to it directly as domain\yourname?
    Friday, November 20, 2009 6:04 PM
  • Hi,

    I tried a simple test in my local PC by changing the sql services to a domain id and it's able to run the job via the link server. Will try it the server A.


    Thanks !

    Wednesday, January 6, 2010 3:21 AM
  • I've changed the service account of the SQL Agent to a domain ID in server A. And I added that domain account to the server B. Scheduled a job based on time and it triggered successfully.

    Thanks all for your help !

    Monday, January 11, 2010 3:24 AM