SQL Server Performance

Local Linked Server SQL Job failing

Discussion in 'ALL SQL SERVER QUESTIONS' started by cnikirk, May 10, 2013.

  1. cnikirk Member

    Hi,

    I have a SQL Server that has a linked server to itself. Two databases. I have a SQL job that runs a stored procedure that moves data between the two databases through the linked server. This job out of the blue just started failing with this error.

    "Login failed for user 'domain\user'. [SQLSTATE 28000] (Error 18456) 2013-05-09 22:00:01.8989434 [SQLSTATE 01000] (Error 0). The step failed.

    If I run this stored proc manually there are no issues. If I had a linked server to a different physical server then I would have some idea of how to fix this but since this all takes place on one server, I'm lost. The user that it says is failing is the windows account that SQL Server and the SQL agent run under, so the account is not the issue.

    The linked server is running "Be made using the login's security context". I have also tried re-creating the job and tried running the job as other users, etc, but nothing works. Like I said this job worked for a while, then just stopped working. There were no changes made to the linked server, stored proc or job.
    Any ideas? Thank you!
  2. davidfarr Member

    I am somewhat puzzled why anyone would create a linked server that is linked to itself, especially one that is "Be made using the login's security context". I cannot see any advantage or purpose in that.
    If the databases are on the same server, in the same server instance, then it is surely easier to just move the data directly between the databases ?
    Best advice right now; get rid of the linked server connection and have the job or stored proc work directly between the databases.

    If you are using Windows Authenticated accounts, these accounts are sometimes subject to the Windows domain policy of password expiration after a time period.
    Futhermore, Windows accounts can become 'locked' if someone attempts to use them more than 3 times with an incorrect password.

    Have you checked that those Windows accounts still have valid passwords that have not expired, and that the accounts are not locked ?
    The fact that your SQL Service is still running using these same accounts is not necessarily proof that the accounts are still valid. You would only notice a problem with the service account if you try to restart the SQL service.
  3. cnikirk Member

    While somewhat complex to explain we are running a custom program that was designed with 2 physical sql servers in mind via a linked server. This program got moved over to another customer who only has 1 sql server. Rather than rewrite the code base, a local linked server was put in place. Other people are doing this as well, although I would not call it common.

    I am using windows authenticated accounts, but I have restarted SQL and the box itself and it logs in correctly with the windows user in question and the SQL Services log in correctly as well. The account is set to never expire and the credentials are fine so that isn't the problem. Something is wrong with the job, because running the stored procedure manually works just fine. I have re-created the job and no luck there either. Very odd, especially since it worked fine for a while.
  4. davidfarr Member

    Another consideration;
    As you may already know; when you create a SQL Agent job, then you specify the job owner and the job runs using the permissions of that job owner.
    Also, when you create a job step of a T-SQL type, then you select a database from a listbox control, the database on which the T-SQL query should execute. The default database in that list (if you select no other) is the master database.

    Setting the correct database name for the T-SQL job step can sometimes be overlooked and this can be a problem if the query does not use full object path names (databasename.owner.object)
    or if the job owner does not have permissions on the database that was selected for the T-SQL job step.
    Not a problem in your case ?
  5. cnikirk Member

    Thank you, but yes I have already checked those. The owner of the job is the same user (the default windows login for the server and host). The default database is definitely set correctly. I also tried running the job as a different user that is an SA and the error returned is different but still doesn't work. (Access to the remote server is denied because the current security context is not trusted).
  6. cnikirk Member

    I decided to look at the windows user inside SQL that the services run under. The user is of course an SA, but under the user mapping it shows that db1 has the user domain\user, but for db 2 the user is DBO. I thought about trying to make them match and both use domain\user, but when I try to change that it says " cannot alter the user dbo". I don't think this matters, but it is the only other oddity I see.
  7. davidfarr Member

    The real puzzle here (for me at least) is why the stored proc runs fine when executed manually but fails as a job under the same user, and also that it previously executed without problem.
    There are several reasons why you could see the errors that you describe ("security context is not trusted" and "login failed") but no reasons that I can see which would exclusively explain why these errors only occur on the Agent job.
    If the windows user was not "trusted" (which is usually related to having local administrator rights on the server), or if the "domain\user" vs "dbo" was a cause, then the stored proc should fail when executed manually too, and yet this is not the case.

    You might find something of interest in the SQL Server Agent error log, which would provide further clues regarding the Agent service and any connection problems it may have to the SQL Server service.
  8. cnikirk Member

    This afternoon I'm going to changed the linked server security to what that does. The security context doesn't matter much to us as long as everything works.
  9. ghemant Moderator

    To best of my knowledge and experience there must be something different between two users ( 1: Who runs the job, 2 : Who can run the SP fine from SSMS/Query Window). Are you testing how your SP will work when you deploy it and it will have to access some db using link server ? Is this what you are trying to achieve ?
  10. Shehap MVP, MCTS, MCITP SQL Server

    Normally linked servers have some quirks related to security login mapping between the 2 DB servers so to be resolve them basically please try the following :
    · Configure the linked server using “Be made using this security context” and select any SQL login
    · Make sure this login has sufficient privileges on the related tables
    
    Try it again and let please let me know your feedback
  11. sonnysingh Member

    SQL Login user should have sufficient level of privileges to performance required actions on the related tables. Then shouldn't be having any issue at all unless you have done any other setting that you might not remember.

Share This Page