locked
Linked server odd behaviour RRS feed

  • Question

  • Hi all,

    I wonder if anyone can help with this as I'm at the end of what I can think of.

    I have an openquery linked server query that works fine under my sysadmin login in a query window. As this code will run in a sqlagent job I'm trying to simulate it running under the sqlagent service account which isn't an admin. As I understand it SQL Server runs a SETUSER to impersonate the credentials if the sqlagent service isn't an admin. The linked server is set to "use current context" as the security setting in linked server.

    So I've run a setuser statement with the Sqlagent service user but I run the code again it comes up with an error of :

    Msg 15274, Level 16, State 1, Line 1

    Access to the remote server is denied because the current security context is not trusted.

    So I know it works fine when I run it under my account in a new query window but any use of setuser gives the above error.

    If I change the sqlagent job to be owned by sa or my user which is an admin we get an

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [SQLSTATE 28000] (Error 18456).

    Just for reference this agent job has been working fine for months, nothing has been released as far as I know. The agent job has 30 steps but only 2 keep failing since about a week ago. Both of these steps reference a linked server that points to a READONLY HA copy of the data.

    I think it may be something to do with authentication but can't understand why it's failing or what to try next.

    If anyone has anything they can think of then I would really appreciate suggestions.

    Thanks

    Ian.

    Wednesday, April 10, 2019 1:56 PM

Answers

  • Well it seems this has happened before and resulted in a reboot which solved the problem. Thanks for taking the time to reply
    Wednesday, April 10, 2019 4:12 PM

All replies

  • Image result for linked server login mapping

    use remote login and password


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Wednesday, April 10, 2019 2:01 PM
  • Thanks for that, doesn't answer why it's been working for several months and just stopped. Plus we need the credentials passing through.
    Wednesday, April 10, 2019 2:10 PM
  • Did you change the password of the user that you are using?
    Wednesday, April 10, 2019 2:18 PM
  • Well it seems this has happened before and resulted in a reboot which solved the problem. Thanks for taking the time to reply
    Wednesday, April 10, 2019 4:12 PM
  • SETUSER impersonates a database user, and once you do that you cannot access any server objects, whence the message about not trusted.

    It does not help if you do EXECUTE AS LOGIN, since self-mapping does not work in a impersonated context. (That would be a major security hole if it did!)

    Then again, this is not very relevant, since was only when you tried to emulate the agent job.

    The SQL Server Agent account is always sysadmin in SQL Server, as I recall.
    As for why the login fails with the message 'NT AUTHORITY\ANONYMOUS LOGON', I don't know, but it is likely to be due to something outside SQL Server. (Presumably Kerberos.) What sort of account is Agent running under? A local machine account or a domain account?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, April 10, 2019 9:28 PM