May 31, 2012 at 1:36 pm
pveilleux (5/31/2012)
SQLKnowItAll (5/31/2012)
http://beyondrelational.com/modules/2/blogs/77/posts/11360/changing-sql-server-service-account-or-password-avoid-restarting-sql-server.aspxGood point. I have always done a reboot to be on the safe side. This client has many, many, many processes that move data among multiple servers, write files/logs to shares on different servers....the books may tell me I don't need to reboot, but I'd rather be safe than sorry. Scheduling 15 minutes of downtime is better than having a client chew me out because the Agent can't write to a share.
In that case, I would make sure to have a separate agent account from the database engine. That 15 minutes will give you time to switch that and keep the 2 separate. Just my 2 cents.
Jared
CE - Microsoft
May 31, 2012 at 1:40 pm
As long as you do things in the proper sequence -- change AD first, then use the SQL tools, not the Windows tools, to change the account pwd -- you will not need a reboot.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 31, 2012 at 1:41 pm
SQLKnowItAll (5/31/2012)In that case, I would make sure to have a separate agent account from the database engine. That 15 minutes will give you time to switch that and keep the 2 separate. Just my 2 cents.
I usually have a separate account for every service on every server, but I inherited these servers. The folks who set them up have zero concept of best practices. The number of headaches it would cause to set this up correctly is not worth the trouble. And there is no real test environment, either.
If we can convince them to migrate to 2012 on new servers, things will be much, much different.
June 1, 2012 at 3:29 pm
pveilleux (5/31/2012)
Hello, all,In digging around on one of our servers, I found what appears to be someone logging in as the SQL Server/Agent service account and running jobs. I'm trying to verify that what I'm seeing is true. When I query the jobs tables in msdb, sysjobhistory.message displays this:
The job succeeded. The Job was invoked by User DOMAIN\SVC_ACCT. The last step to run was step 1 (Job Name).
I read somewhere that if a user is in the sysadmin role, the job will be executed as the Agent service account. However, I found another message that mentions a different user. That user is not only in all the SQLAgent roles in msdb, but is also a sysadmin. I also checked for messages for users who are in sysadmin, but not the SQLAgent roles, to see if they appear. My account falls into that category, and I found instances of my username. No proxy accounts are set up. None of the steps I investigated are defined with a Run As user.
My only conclusion is that someone is logging in using this service account and running the jobs. Can anyone out there think of any other reasons I would see the service account appearing in the log? This could be a huge security hole I need to plug as soon as possible.
thanks...
Just one point of clarity, if by "a user" you meant the "job owner" and not the "user executing the job" then you are correct, however the sentence following the one I bolded has me thinking you meant the latter. It does not matter who or what starts a job in terms of what security context each job step runs as. The context in which each job steps executes is dependent on the job owner and whether or not a proxy account has been specified for that step.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2012 at 9:47 am
opc.three (6/1/2012)
pveilleux (5/31/2012)
Hello, all,In digging around on one of our servers, I found what appears to be someone logging in as the SQL Server/Agent service account and running jobs. I'm trying to verify that what I'm seeing is true. When I query the jobs tables in msdb, sysjobhistory.message displays this:
The job succeeded. The Job was invoked by User DOMAIN\SVC_ACCT. The last step to run was step 1 (Job Name).
I read somewhere that if a user is in the sysadmin role, the job will be executed as the Agent service account. However, I found another message that mentions a different user. That user is not only in all the SQLAgent roles in msdb, but is also a sysadmin. I also checked for messages for users who are in sysadmin, but not the SQLAgent roles, to see if they appear. My account falls into that category, and I found instances of my username. No proxy accounts are set up. None of the steps I investigated are defined with a Run As user.
My only conclusion is that someone is logging in using this service account and running the jobs. Can anyone out there think of any other reasons I would see the service account appearing in the log? This could be a huge security hole I need to plug as soon as possible.
thanks...
Just one point of clarity, if by "a user" you meant the "job owner" and not the "user executing the job" then you are correct, however the sentence following the one I bolded has me thinking you meant the latter. It does not matter who or what starts a job in terms of what security context each job step runs as. The context in which each job steps executes is dependent on the job owner and whether or not a proxy account has been specified for that step.
So, there is no proxy account at all. You bring up a good point that I hadn't dug into yet: is the user name I'm seeing in the message column the same as the job owner?
If I'm understanding what you're getting at correctly, then if a login owns a job, we will see that login name in the message column. This should be regardless of sysadmin rights, since the login would need to have proper permissions in msdb. If a login is sysadmin and does NOT own the job, we will see the service account for SQL Agent in the message column.
I'm going to cross-check that. Thanks for the input...
June 6, 2012 at 12:48 pm
pveilleux (6/6/2012)
opc.three (6/1/2012)
pveilleux (5/31/2012)
Hello, all,In digging around on one of our servers, I found what appears to be someone logging in as the SQL Server/Agent service account and running jobs. I'm trying to verify that what I'm seeing is true. When I query the jobs tables in msdb, sysjobhistory.message displays this:
The job succeeded. The Job was invoked by User DOMAIN\SVC_ACCT. The last step to run was step 1 (Job Name).
I read somewhere that if a user is in the sysadmin role, the job will be executed as the Agent service account. However, I found another message that mentions a different user. That user is not only in all the SQLAgent roles in msdb, but is also a sysadmin. I also checked for messages for users who are in sysadmin, but not the SQLAgent roles, to see if they appear. My account falls into that category, and I found instances of my username. No proxy accounts are set up. None of the steps I investigated are defined with a Run As user.
My only conclusion is that someone is logging in using this service account and running the jobs. Can anyone out there think of any other reasons I would see the service account appearing in the log? This could be a huge security hole I need to plug as soon as possible.
thanks...
Just one point of clarity, if by "a user" you meant the "job owner" and not the "user executing the job" then you are correct, however the sentence following the one I bolded has me thinking you meant the latter. It does not matter who or what starts a job in terms of what security context each job step runs as. The context in which each job steps executes is dependent on the job owner and whether or not a proxy account has been specified for that step.
So, there is no proxy account at all. You bring up a good point that I hadn't dug into yet: is the user name I'm seeing in the message column the same as the job owner?
Only if the owner of the job happens to be the same as the login that started the job, but that would be happenstance.
If the job is owned by the same domain account that is used to run the SQL Server Agent service and that domain account has a login that is a sysadmin then they would match.
If I'm understanding what you're getting at correctly, then if a login owns a job, we will see that login name in the message column.
I am not sure we're on the same page yet. Let us say JobA is owned by sa and it is invoked by windows user DOMAIN\UserName, then you will see this in the step output:
Message
The job succeeded. The Job was invoked by User DOMAIN\UserName. The last step to run was step 1 (Step Name).
This should be regardless of sysadmin rights, since the login would need to have proper permissions in msdb.
You are correct in that the user invoking the job would need proper access to msdb, or more accurately proper Database Role membership in one of the SQL Server Agent Fixed Database Roles.
If a login is sysadmin and does NOT own the job, we will see the service account for SQL Agent in the message column.
No. You will see the name of the login who invoked the job.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2012 at 1:20 pm
This is good stuff, info I couldn't find in any of the BOL.
So, here's what I have:
JobA is owned by sa. SQL Agent and SQL Server are both run by DOMAIN\SvcAcct. I have many users in sysadmin, including DOMAIN\SvcAcct. In msdb, some of the users in sysadmin are in all 3 of the SQL Agent roles. For JobA, I see this in sysjobhistory:
The job succeeded. The Job was invoked by User DOMAIN\SvcAcct. The last step to run was step 1 (StepName).
What I was expecting - and hoping - to see was DOMAIN\UserName instead of DOMAIN\SvcAcct.
What I am afraid of is, one of the users has the password for DOMAIN\SvcAcct and is logging in as that account to run jobs. The jobs in question are not scheduled. I see other instances of jobs owned by sa that have run, and have a domain account for a user who is in sysadmin in the log. To me, it appears to be the same scenario, just one is a person, the other is a service account.
Based on your last comment in the last response, it sounds like I have a big security problem on my hands.
June 6, 2012 at 1:23 pm
In msdb, some of the users in sysadmin are in all 3 of the SQL Agent roles..
If someone is a sysadmin, they are automatically in all fixed database roles. They have full access. You could add an SQL Agent role, or take it away... It doesn't matter if they are sysadmin.
Jared
CE - Microsoft
June 6, 2012 at 1:25 pm
Setup auditing on logins. This will allow you to determine if someone is logging in with the service account.
June 6, 2012 at 1:34 pm
SQLKnowItAll (6/6/2012)
In msdb, some of the users in sysadmin are in all 3 of the SQL Agent roles..
If someone is a sysadmin, they are automatically in all fixed database roles. They have full access. You could add an SQL Agent role, or take it away... It doesn't matter if they are sysadmin.
Not only that no one needs to be in all three Roles. The Roles build on one another meaning the lower privved Role has a subset of the perms the next Role has, and so on. Someone who has all three Roles really only needs to be in the Operator Role.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2012 at 1:36 pm
SQLKnowItAll (6/6/2012)
If someone is a sysadmin, they are automatically in all fixed database roles. They have full access. You could add an SQL Agent role, or take it away... It doesn't matter if they are sysadmin.
I understand that - thanks, Jared......
Setup auditing on logins. This will allow you to determine if someone is logging in with the service account.
I have login auditing set up at the server level. Problem is, every time SQL Agent connects, it gets logged. And this account logs in cross-server, so we get many, many entries containing IP's of the other SQL boxes. I will look into what the SQL 2008 Auditing feature can provide. Thanks....
June 6, 2012 at 1:43 pm
pveilleux (6/6/2012)
SQLKnowItAll (6/6/2012)
If someone is a sysadmin, they are automatically in all fixed database roles. They have full access. You could add an SQL Agent role, or take it away... It doesn't matter if they are sysadmin.I understand that - thanks, Jared......
Are you sure? Because you keep saying statements like this:
In msdb, some of the users in sysadmin are in all 3 of the SQL Agent roles..
and I am wondering why. Not trying to be a jerk, its just that that statement is irrelevant, so I want to make sure we are on the same page.
Jared
CE - Microsoft
June 6, 2012 at 1:45 pm
pveilleux (6/6/2012)
This is good stuff, info I couldn't find in any of the BOL.So, here's what I have:
JobA is owned by sa. SQL Agent and SQL Server are both run by DOMAIN\SvcAcct. I have many users in sysadmin, including DOMAIN\SvcAcct. In msdb, some of the users in sysadmin are in all 3 of the SQL Agent roles. For JobA, I see this in sysjobhistory:
The job succeeded. The Job was invoked by User DOMAIN\SvcAcct. The last step to run was step 1 (StepName).What I was expecting - and hoping - to see was DOMAIN\UserName instead of DOMAIN\SvcAcct.
What I am afraid of is, one of the users has the password for DOMAIN\SvcAcct and is logging in as that account to run jobs. The jobs in question are not scheduled. I see other instances of jobs owned by sa that have run, and have a domain account for a user who is in sysadmin in the log. To me, it appears to be the same scenario, just one is a person, the other is a service account.
Based on your last comment in the last response, it sounds like I have a big security problem on my hands.
You say they are sysadmins, well they could be doing something like this which does not require them to know the password of the service account and would still get the Agent log entry to show that DOMAIN\SvcAcct invoked the job and not themselves:
EXECUTE AS LOGIN = N'DOMAIN\SvcAcct'
GO
EXEC msdb.dbo.sp_start_job
@job_name = 'JobA'
GO
REVERT
GO
With your level of concern, I think at a fundamental level you need to address who has sysadmin on your instance.
Also, are you sure you're not mixing up your messages of "invoked by" and "executed as"? That can be a common misconception too:
Job Level Output:
Message
The job succeeded. The Job was invoked by User DOMAIN\DOMAINUSER. The last step to run was step 1 (Step Name).
Step Level Output:
Message
Executed as user: DOMAIN\AGENTSVCACCT. The step succeeded.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2012 at 1:49 pm
SQLKnowItAll (6/6/2012)
pveilleux (6/6/2012)
SQLKnowItAll (6/6/2012)
If someone is a sysadmin, they are automatically in all fixed database roles. They have full access. You could add an SQL Agent role, or take it away... It doesn't matter if they are sysadmin.I understand that - thanks, Jared......
Are you sure? Because you keep saying statements like this:
In msdb, some of the users in sysadmin are in all 3 of the SQL Agent roles..
and I am wondering why. Not trying to be a jerk, its just that that statement is irrelevant, so I want to make sure we are on the same page.
Throwing things like that out for completeness. I've thought some things to be irrelevant when asking for help, when it turns out it is very relevant. And having done tech support on Sybase ASE for 2.5 years, the more info I have, the better. I'd rather have too much info than get "oh, yeah, by the way..." after troubleshooting something for 2 days.
Been working with SQL Server for over 12 years, since version 7. I get the whole sysadmin thing very well.
June 6, 2012 at 1:51 pm
pveilleux (6/6/2012)
SQLKnowItAll (6/6/2012)
pveilleux (6/6/2012)
SQLKnowItAll (6/6/2012)
If someone is a sysadmin, they are automatically in all fixed database roles. They have full access. You could add an SQL Agent role, or take it away... It doesn't matter if they are sysadmin.I understand that - thanks, Jared......
Are you sure? Because you keep saying statements like this:
In msdb, some of the users in sysadmin are in all 3 of the SQL Agent roles..
and I am wondering why. Not trying to be a jerk, its just that that statement is irrelevant, so I want to make sure we are on the same page.
Throwing things like that out for completeness. I've thought some things to be irrelevant when asking for help, when it turns out it is very relevant. And having done tech support on Sybase ASE for 2.5 years, the more info I have, the better. I'd rather have too much info than get "oh, yeah, by the way..." after troubleshooting something for 2 days.
Been working with SQL Server for over 12 years, since version 7. I get the whole sysadmin thing very well.
Understood! Just making sure 😉 Thanks for being complete, I guess I am just not used to seeing it.
Jared
CE - Microsoft
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply