3

I have a job called @test_credentials which runs the following query

select * from openquery(SERVER2, 
'select USER_NAME(),* from openquery(SERVER1,''SELECT USER_NAME() '')')

and outputs those results to a file.

Job '@test_credentials' : Step 1, 'test_credentials' : Began Executing 2015-08-31 17:53:45

guest LinkedServerUser

(1 rows(s) affected)

  1. The linked server definitions for both SERVER1 and SERVER2 are using the linkedserveruser to login to one another (security context option).
  2. Both linked server definitions are defined with the same options
  3. The linkedserveruser user account exists on both servers and is not disabled in anyway.
  4. The service account running the AGENT is identical for both SERVER1 and SERVER2.

So my question is this:

Why is SQL Server Agent logging in as guest on SERVER2 when executing this job?

Improve this question
1

1 Answer 1

Reset to default
2

Personally I would try using ORIGINAL_LOGIN() instead of USER_NAME(). USER_NAME() returns the database principal or user associated with your login in the database you are connecting to. In this case you are probably connecting to master in both cases. If LinkedServerUser doesn't have a specific login in master on SERVER2 then you will see GUEST.

If on the other hand you use ORIGINAL_LOGIN() you will get the server principal that connected, which will probably be LinkedServerUser in both cases.

Improve this answer
3
  • Hi Kenneth, that worked thanks. I'm still having an issue though with a Job that is using this same linked server setup. The investigation continues.
    – Craig Efrein
    Sep 1, 2015 at 7:18
  • Does this look familiar? "NT AUTHORITY\ANONYMOUS LOGON"? If so you have a double hop issue. It's caused when you go from one computer to another and then that machine goes to another. Unless Kerberos authentication is set up correctly between the machines you will get that error.
    – Kenneth Fisher
    Sep 1, 2015 at 11:59
  • Hi Kenneth, thanks for that. Turns out there was yet another sp nested inside the second one that was being executed on an entirely different machine, different environment. Anyway, it took reading every line of SQL code to find it.
    – Craig Efrein
    Sep 1, 2015 at 12:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.