Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SQL Server Agent jobs running under SA Expand / Collapse
Author
Message
Posted Tuesday, June 18, 2013 7:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:12 PM
Points: 5,615, Visits: 6,413
I have an issue with some of my jobs. They are owned by the same domain account that the SQL Server Agent uses. When they run as scheduled everything appears fine. When they are run manually, we occasionally get feedback that they are running under the SA account.

I'm scratching my head on this one trying to figure out why they run under the proper account when scheduled but sometimes (not always) run under SA when manually executed by a member of the DBA team.

Books Online is not helpful and my google-fu does not seem to come up with articles pointing out the authentication routines of the SQL Agent jobs. Does anyone have links they can point me to that might explain this issue?

EDIT: More information.

The agent is using an account assigned to the SysAdmin role (I don't control this) and there are no proxy accounts being used on any of the job steps.

We are getting the information from a login watch job that looks at master.dbo.sysprocess and gets the login information from that table.

So what process is Microsoft using to load the sysprocesses table? I'm wondering if that might hold a clue as to why we're seeing SA as the account when a job is manually run.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1464661
Posted Wednesday, June 19, 2013 7:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:12 PM
Points: 5,615, Visits: 6,413
More detail as requested on another post.

I am sysadmin. The owner of the job is sysadmin. When I manually run the job I get an email stating:


The host MyServer has logged in with the functional login 'sa' using SQLAgent - TSQL JobStep (Job 0x2E8AC23BE1934F499506377F463F3BF6 : Step 1). They are using SPID 115.


This email is part of our Login Auditing process. We pull the values from the below code snippet:

 SELECT           
@@SERVERNAME,
p.hostname,
p.spid,
p.dbid,
p.login_time,
p.last_batch,
p.loginame,
d.[name],
p.PROGRAM_NAME
FROM master.dbo.sysprocesses p (nolock)
INNER JOIN master.sys.databases d ON p.dbid = d.database_id

The 'sa' part of that email comes from p.loginame, which in turn comes from master.dbo.sysprocesses.

So, could this be a dirty data issue (because of the nolock) or is this something else?

I'm certainly not logging in as SA. I'm logged in under my Windows account.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1465162
Posted Wednesday, June 19, 2013 8:09 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 2:41 AM
Points: 976, Visits: 3,040
http://sqlblog.com/blogs/tibor_karaszi/archive/2009/09/19/sql-server-agent-jobs-and-user-contexts.aspx



The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1465189
Posted Wednesday, June 19, 2013 8:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:12 PM
Points: 5,615, Visits: 6,413


Thanks for this link, but it doesn't quite help me figure out why it's changing my login context to 'sa'. I'm sysadmin, so it shouldn't do that so far as I know.

Unless something is going hooey between the context translation of me and the job owner (both accounts are sysadmin).


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1465213
Posted Wednesday, June 19, 2013 9:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 9, 2014 12:08 PM
Points: 1,618, Visits: 1,553
No, it's not a dirty read. And I can't reproduce your issue.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1465256
Posted Wednesday, June 19, 2013 10:40 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 12:50 PM
Points: 1,061, Visits: 2,580
Brandie Tarvin (6/19/2013)


Thanks for this link, but it doesn't quite help me figure out why it's changing my login context to 'sa'. I'm sysadmin, so it shouldn't do that so far as I know.

Unless something is going hooey between the context translation of me and the job owner (both accounts are sysadmin).


What does your job do (i.e., what kinds of steps - T-SQL commands, execute SSIS packages, etc.)? Do all steps happen on the same server\instance? What is the service account for the SQL Agent service?


Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1465286
Posted Wednesday, June 19, 2013 11:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:07 PM
Points: 2,049, Visits: 3,592
Brandie Tarvin (6/18/2013)
When they are run manually, we occasionally get feedback that they are running under the SA account.


Is there any correlation to who is running the job and this occurring? Can you run a trace and get more details for all the manual executions of this job and see if you can find some correlation?

Can you give any details into what the job is doing? Is it possible that the database is owned by team member that is a sysadmin and when the user is executing the job / job step that it is switching context. Far stretch, I know. Grasping at ideas....


David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #1465303
Posted Wednesday, June 19, 2013 2:59 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 7:31 PM
Points: 675, Visits: 6,816
And are you sure no one logs into SSMS as SA?
Then executes the job?
Post #1465393
Posted Thursday, June 20, 2013 6:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:12 PM
Points: 5,615, Visits: 6,413
wolfkillj (6/19/2013)

What does your job do (i.e., what kinds of steps - T-SQL commands, execute SSIS packages, etc.)? Do all steps happen on the same server\instance? What is the service account for the SQL Agent service?


T-SQL jobs running a stored procedure that restores a database.

SQL Server Agent is running under a domain account (plain vanilla) with sysadmin access. That same account owns the jobs in question.

Greg Edwards-268690 (6/19/2013)
And are you sure no one logs into SSMS as SA?
Then executes the job?


Yes, I am sure no one is logged in under SA and running these jobs. How do I know this? I'm the one running the job.

David Benoit (6/19/2013)

Is there any correlation to who is running the job and this occurring? Can you run a trace and get more details for all the manual executions of this job and see if you can find some correlation?


Not sure what you mean by correlation. I can tell you this happens intermittently, not all the time. So getting a trace would require us to place an open ended trace on the server and hoping this happens during that time frame.

David Benoit (6/19/2013)

Can you give any details into what the job is doing? Is it possible that the database is owned by team member that is a sysadmin and when the user is executing the job / job step that it is switching context. Far stretch, I know. Grasping at ideas....


1) See above.
2) Database ownership? You mean as in who owns MSDB? Well, now that I look at it, the SA account does own MSDB. I wonder if that's the issue.

Does this make sense to everyone? MSDB being owned by SA would force other accounts to use its credentials when running jobs?


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1465641
Posted Thursday, June 20, 2013 7:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:07 PM
Points: 2,049, Visits: 3,592
Brandie Tarvin (6/20/2013)

Does this make sense to everyone? MSDB being owned by SA would force other accounts to use its credentials when running jobs?


Not me or I wouldn't be asking such questions. LOL. :)

First, on the trace, I would say that yes, it would be an open ended trace that was specific in filtering for the procedure the job is running to see if you can get any further information on what might be different between one run and the other.

Also, another "out there" question, is the machine that this job is on in a DMZ or something like that where you may be experiencing a double-hop issue and thereby the credentials of the executor are not being shown?


David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #1465673
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse