Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
job failure under linked server

Proposed job failure under linked server

  • Thursday, November 15, 2012 12:48 PM
     
     
    Sign In to Vote
    0

    I have 2 instance, A and B. On instance A, in order to let a job access and manipulate data in a database on instance B, a linked server is created. Linked server connection test is successful and from instance A I can even open a new query and retrieve data on instance B. I have created a login with sysadmin access on both instance using the same domain account and set that login as the job owner. But the job failed with an error: "Login failed for user 'login_name'", "Cannot open database "xxx" requested by the login." Here is an interesting thing, the 'login_name' in the error message is not the job owner.

    Anyone has any idea? I have been stuck by it for a long time. I really appreciate your time and efforts. I look forward to your reply.

    regards,

    Oliver

     

All Replies

  • Thursday, November 15, 2012 1:03 PM
     
     
    Sign In to Vote
    0
    If job owner is a login which is a sys-admin, then the job would always be run either as SQL Agent Service account or Proxy depending upon what option you have for “Run As” in that Agent Job step.
     
  • Thursday, November 15, 2012 1:15 PM
     
     
    Sign In to Vote
    0
    Thanks for your reply, V.Keerthi. The 'login_name' in the error message is exact the SQL Agent service account. But I also created a sysadmin login using that domain account on both instances, and the issue is still there.
    • Edited by Oliver_Wang Thursday, November 15, 2012 1:16 PM
    •  
     
  • Thursday, November 15, 2012 1:31 PM
     
     
    Sign In to Vote
    0
    Could you please check if it is failing with same error or a different one?
     
  • Thursday, November 15, 2012 2:53 PM
     
     
    Sign In to Vote
    0
    Yes, I already checked. the error message is still the same.
     
  • Thursday, November 15, 2012 3:20 PM
     
     
    Sign In to Vote
    0

    Hi,

    You can go through the below link to know

    http://blog.sqlauthority.com/2008/11/04/sql-server-fix-error-4064-cannot-open-user-default-database-login-failed-login-failed-for-user/

    please check services - which account is used for startup of the SQL agent.

    System account is no good - only local resources are available.

    Use "This account" with administrative rights on the Windows & SQL server.

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

     
  • Thursday, November 15, 2012 9:57 PM
     
     
    Sign In to Vote
    0

    The job owner is a Windows login, right? OK, so the SQL Agent account is logged into Windows. It can play the job owner inside SQL Server, but I don't think it should be able to impersonate a Windows user outside the instance. (At very least it would need a Windows permission to do this.) So the linked server access will be by the service account for Agent.

    What you could do is to set up a CmdExec job that starts SQLCMD with the RUNAS command in Windows. I'm not a Windows guy, but it seems that you can use the /SAVECRED to provide the password.

    The alternative would be to grant access to the service account for Agent to the linked server, or set up mapping to an SQL login.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Friday, November 16, 2012 6:00 AM
     
     
    Sign In to Vote
    0

    Thanks for your reply, Erland. I'm interested in your second solution. The issue here is under an active-active cluster environment. For the startup domain login, I have already set it as sysadmin on both instances, and also I mapped it to the database's db_owner on instance B. Did I miss anything here?


     
  • Friday, November 16, 2012 8:19 AM
     
     
    Sign In to Vote
    0

    So the service account for Agent is already granted access to the other instance? If a login is granted membership to sysadmin, that also means that it maps to dbo in all databases, so you should do nothing more.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Friday, November 16, 2012 8:36 AM
     
     
    Sign In to Vote
    0
    Yes, it seems I have done what I should do, but the issue is still there. : (
     
  • Friday, November 16, 2012 10:02 PM
     
     Proposed
    Sign In to Vote
    0

    Permit me to recap, you have Agent on Instance A running as Domain\AGENT_A (or is it a local account like NetworkService?) You have granted AGENT_A access to instance B as sysadmin. When you run the job, access to the linked server fails.

    What happens if you log in as Domain\AGENT_A interactively?

    What happens if you run a CmdExec job which logs into Instance B?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Friday, November 16, 2012 11:33 PM
     
     Proposed Has Code
    Sign In to Vote
    0

    I believe that if you set up a credential for the Windows Login that should run the job, then make a SQL Agent proxy using that credential, you will be on your way.  

    As Erland mentioned earlier, this will be a CMDExec job step, with the proxy assigned to the step.  Use SQLCMD something like this, using a Trusted Connection, which will be the Proxy's Credential's account name and password.  Your step will look like:

    SQLCMD -Q "EXEC ProcToRun" -S LocalServer -d LocalDB -E

    This way the EXEC ProcToRun has logged in as the Windows account.  This has moved it from being a service account that is trying to get through the linked server, to being a logged in user.  Therefore (assuming it has the needed rights) the ProcToRun will run in very much the same context as when you run the procedure from SSMS.

    At least, that is what I have had to do.

    RLF

     
  • Wednesday, November 21, 2012 2:20 PM
     
     
    Sign In to Vote
    0

    Hi Everyone,

    Sorry for late response since I need to go through a process to get the password of startup login. Thanks for your advice and I have tried logging on both instances using the domain account. I can do query, configuration and whatever since this login has sysadmin privilege. For the CmdExec job, I haven't used it because I don't know what scripts I need to use but I just can't understand why an account with sysadmin privilege still need to be granted more access. 

    regards,

    Oliver

     
  • Wednesday, November 21, 2012 10:43 PM
     
     
    Sign In to Vote
    0

    but I just can't understand why an account with sysadmin privilege still need to be granted more access. 


    Windows != SQL Server. I can't say exactly what is going, but it seems that there some sort of double-hop issue. Windows does not trust SQL Server to vouch for the account when it comes that way.

    For a CmdExec job, for testing purposes you could make it as simple as:

    SQLCMD -S OTHERSERVER -E -ddatabase -Q"SELECT COUNT(*) FROM sys.objects"

    I don't expect it to be possible to run the full job this way, since I assume that the job accesses tables in the local server. This was only a suggestion for troubleshooting.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Thursday, November 22, 2012 1:43 PM
     
     
    Sign In to Vote
    0

    Hi Erland,

    I already tried the scripts you provided for a testing. In the scirpts, I use the database I want to access and replace 'sys.objects' with an user-defined table under the database and the test job runs successfully, which means the domain account is able to connect to the database on the linked server. The issue is becoming more confusing. 

    regards,

    Oliver

     
  • Thursday, November 22, 2012 10:22 PM
     
     
    Sign In to Vote
    0

    So it seems that this is a problem with trust and delegation in Windows. I will have to admit that this is not my area of expertise. But for some reason Windows does not trust SQL Server to vouch for the Agent account.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Friday, November 23, 2012 3:39 PM
     
     
    Sign In to Vote
    0

    Is the SQL Server service account the same account as the SQL Agent service account or different? (I did not see that mentioned above.)  What account are you expecting to make the linked server connection? 

    It seems that the issue is not the rights granted the login, but when does the login happen for running your job.  If the SQL Server runs as domain\SSaccount and the SQL Agent runs as domain\SAaccount, the question would be:  At what level does my job step actually 'log in'?

    If it is logged in at the domain level, then the credentials should pass through the linked server just fine.  (Do your SQL Server and SQL Agent services run under different logins?)  If the step logs in again from within SQL Agent (which is running as its own service account) then it causes a security context switch that does not have the domain context needed to run successfully on another server.

    This is all learned from various postings and (of course) some significant trial and error.  I am sure that my description is somewhat flawed, but it is in the vicinity.

    Option 1: Create a proxy and credential for your sysadmin account.  Use that to run the CMDEXEC step described earlier. Since the credential (which contains a password) actually logs in again, then it will have the needed security context.

    Option 2: I believe that if the SQL Server, SQL Server Agent, and the scheduled job all run as the same account, the context will pass through without any context change.

    I did search around trying to find a clear explanation of this (which I could use to improve my explanation) but no success so far.

    RLF 

     
  • Monday, November 26, 2012 10:17 AM
     
     
    Sign In to Vote
    0

    Hi Russel,

    Thanks for your reply. The job, SQL server service and SQL agent are using the same domain account which is also windows admin on both servers. But I still got the same problem. By the way the CMDEXEC scirpts is like ""C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTEXEC" /DTS "\MSDB\OmsAggregates_Export" /SET "\Package". I don't know whether this information gives you more idea on it. Thanks.

    regards,

    Oliver