SQL Server Agent jobs running under SA
-
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.
-
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.
-
-
Sean Pearce (6/19/2013)
http://sqlblog.com/blogs/tibor_karaszi/archive/2009/09/19/sql-server-agent-jobs-and-user-contexts.aspxThanks 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).
-
No, it's not a dirty read. And I can't reproduce your issue.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Brandie Tarvin (6/19/2013)
Sean Pearce (6/19/2013)
http://sqlblog.com/blogs/tibor_karaszi/archive/2009/09/19/sql-server-agent-jobs-and-user-contexts.aspxThanks 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
-
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“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
And are you sure no one logs into SSMS as SA?
Then executes the job?
-
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 (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“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
I don't believe it's on a DMZ, but that's something I'd need to ask the server team.
EDIT: I'm pretty sure it's not as our corporate security guidelines are specifically against allowing a SQL Server on the DMZ. But I'm still going to ask the question of the server team just to be sure.
-
Brandie Tarvin (6/20/2013)
wolfkillj (6/19/2013)
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?
No. msdb is always owned by the sa account.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Robert Davis (6/20/2013)
msdb is always owned by the sa account.Not true, actually.
-
Please tell us when it is not owned by sa barring someone doing something stupid like manually changing the owner from sa to another account.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
OK, I don't know if this might help, but I found a similar issue elsewhere:
Yes, it's from 1999 and concerns Sybase, but it sure sounds like a similar issue. It also sounds like there was never really a "fix" for the issue.
Not sure if this will help, but it likely won't hurt...
Jason
(PS, I do see on one of my servers in sysprocesses an NTLogin showing a LoginName of SA right now. This is for the "FSAgent Task" which some quick Googleing shows as being used by the FileStream feature (which we are using))
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply