April 10, 2012 at 8:22 am
Dear All,
I have been having strange issue with the SQL Server 2008 r2 Database where some of the jobs have stopped but I can't find out by who because it is run under the sa login, the Server has been timing-out; where usually there are no issues with the Server. I know there are lots of reasons why this might be occurring, so I checked if there is blocking the Server, anything with a very high CPU. It seems to be running fine for now but my main concern is how to find out who stopped the job. What else I should do to prevent this from happening again, or is there anything else I should be doing or missing please?
Thank you in advance!
April 10, 2012 at 8:26 am
Disable the sa account and only use individual accounts. Clearly too many people have the sa account credentials...
Jared
CE - Microsoft
April 10, 2012 at 10:34 am
Also make sure the sa account doesn't own the jobs in question.
April 10, 2012 at 3:32 pm
Brandie Tarvin (4/10/2012)
Also make sure the sa account doesn't own the jobs in question.
Why would it matter if sa owned jobs?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 11, 2012 at 11:01 am
opc.three (4/10/2012)
Brandie Tarvin (4/10/2012)
Also make sure the sa account doesn't own the jobs in question.Why would it matter if sa owned jobs?
Because if sa is disabled, then scheduled jobs won't run properly.
Plus, if the OP is trying to track down who is running the job and people have access to the sa account, you create a plain vanilla domain account to own the jobs (or a different sql account that no one else has the password to), then you can start narrowing down the suspects when you trace what login credentials the job is running under. When the new job owner runs the job, you can delete that "suspect" off your list. <- Which also allows you to build a proper time frame for when people are messing with your jobs.
April 11, 2012 at 11:07 am
Brandie Tarvin (4/11/2012)
opc.three (4/10/2012)
Brandie Tarvin (4/10/2012)
Also make sure the sa account doesn't own the jobs in question.Why would it matter if sa owned jobs?
Because if sa is disabled, then scheduled jobs won't run properly.
They run fine. All my local log shipping jobs are owned by sa and sa is disabled. The jobs don't log in as sa, they're just owned by it. Same as databases can be owned by sa even though sa is disabled.
The jobs run as the SQL Agent service account unless there's a proxy set.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2012 at 11:10 am
GilaMonster (4/11/2012)
Brandie Tarvin (4/11/2012)
opc.three (4/10/2012)
Brandie Tarvin (4/10/2012)
Also make sure the sa account doesn't own the jobs in question.Why would it matter if sa owned jobs?
Because if sa is disabled, then scheduled jobs won't run properly.
They run fine. All my local log shipping jobs are owned by sa and sa is disabled. The jobs don't log in as sa, they're just owned by it. Same as databases can be owned by sa even though sa is disabled.
The jobs run as the SQL Agent service account unless there's a proxy set.
Maybe the proxy was the issue on server I saw this behavior on, then. It's been a while since I ran into that problem and we don't use sa at my current workplace.
April 11, 2012 at 11:11 am
Brandie Tarvin (4/11/2012)
opc.three (4/10/2012)
Brandie Tarvin (4/10/2012)
Also make sure the sa account doesn't own the jobs in question.Why would it matter if sa owned jobs?
Because if sa is disabled, then scheduled jobs won't run properly.
Plus, if the OP is trying to track down who is running the job and people have access to the sa account, you create a plain vanilla domain account to own the jobs (or a different sql account that no one else has the password to), then you can start narrowing down the suspects when you trace what login credentials the job is running under. When the new job owner runs the job, you can delete that "suspect" off your list. <- Which also allows you to build a proper time frame for when people are messing with your jobs.
You stole the words right out of my mouth! My default base config is to have sa disabled and have all databases and jobs owned by sa. I also have Policy setup to tell me when any of that gets out of order.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 12, 2012 at 4:44 am
Thank you all for all your comments and opinions!
I was thinking of creating another user with a similar privileges as the SysAmin user but what would be the advantage of creating yet another user, which would eventually perform the same job as the SysAmin apart from sa user should not be used because it is an easy target for hackers to log on to the SQL Server.
1.Do you setup the policy through Alerts or somewhere else, so you can be notified if there are any abnormalities in the use of the logins?
2.Even if the sa account is disabled but the job is owned by the sa, how does that work? Who runs the job?
Thank you!
April 12, 2012 at 4:47 am
tt-615680 (4/12/2012)
I was thinking of creating another user with a similar privileges as the SysAmin user but what would be the advantage of creating yet another user, which would eventually perform the same job as the SysAmin apart from sa user should not be used because it is an easy target for hackers to log on to the SQL Server.
Auditability (every person who is admin should have their own account).
Server hardening. sa is a well known account, anyone hacking in knows it exists so all they have to guess/brute force is the password
Complience
2.Even if the sa account is disabled but the job is owned by the sa, how does that work? Who runs the job?
The job is run by the SQL Server Agent Service account, regardless of job ownership.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 12, 2012 at 5:01 am
What Gail said on the above. Except, if I recall correctly, does the job run under the credentials of the person running it (if it's run manually?), or am I completely off base with that one?
tt-615680 (4/12/2012)
1.Do you setup the policy through Alerts or somewhere else, so you can be notified if there are any abnormalities in the use of the logins?
Set up audit on logins and it audits via the Windows Event Viewer. It's an automatic thing if you allow it (we do). If you want to do auditing above and beyond that, then you have to manually build something.
April 12, 2012 at 5:07 am
GilaMonster (4/12/2012)
tt-615680 (4/12/2012)
I was thinking of creating another user with a similar privileges as the SysAmin user but what would be the advantage of creating yet another user, which would eventually perform the same job as the SysAmin apart from sa user should not be used because it is an easy target for hackers to log on to the SQL Server.Auditability (every person who is admin should have their own account).
Server hardening. sa is a well known account, anyone hacking in knows it exists so all they have to guess/brute force is the password
Complience
2.Even if the sa account is disabled but the job is owned by the sa, how does that work? Who runs the job?
The job is run by the SQL Server Agent Service account, regardless of job ownership.
If I was to go ahead and disable the sa user, wouln't it likely that other applications effected which might be relaying on the sa user?
April 12, 2012 at 5:52 am
tt-615680 (4/12/2012)
If I was to go ahead and disable the sa user, wouln't it likely that other applications effected which might be relaying on the sa user?
Ideally, applications should not be using the sa user account. It's a major security breach to allow an app that kind of permissions in SQL Server. Are these in-house applications or third party?
April 12, 2012 at 8:43 am
Brandie Tarvin (4/12/2012)
What Gail said on the above. Except, if I recall correctly, does the job run under the credentials of the person running it (if it's run manually?), or am I completely off base with that one?tt-615680 (4/12/2012)
1.Do you setup the policy through Alerts or somewhere else, so you can be notified if there are any abnormalities in the use of the logins?Set up audit on logins and it audits via the Windows Event Viewer. It's an automatic thing if you allow it (we do). If you want to do auditing above and beyond that, then you have to manually build something.
Thank you for the reply!
If the job runs manually then yes, we usually login as an sa user but its going to change now where there will be a user which would replace the sa user but would have Similar Privileges.
Thank you!
April 12, 2012 at 10:07 am
Why does the replacement user need similar privledges? You're just shooting yourself in the foot.
Remember, least permissions needed to do the job (figuratively and literally) should be the rule here.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply