April 6, 2011 at 10:11 pm
annasql (4/6/2011)
This makes a lot of sense.I hope there is a microsoft article explains this in detail. Because we are planning upgrade all our sql 2005 to 2008 on windows 2008, this just caused a lot of confusion about the security.
So from what Jeff said, there is no tie-in from the login used to run the service and the service SID. So does that mean in this case, our domain account who is used to run the service account is NOT an admin, only the service itself is granted sysadmin, (I'm not sure how a service is granted sysadmin) but only know a login can be granted sysadmin.
So if we want that login to be a sysadmin, we have to explicitly add it to login list and grant it sysadmin, Correct?
Thanks
Correct - if you want the service account to be sysadmin you have to add it as a login to SQL Server.
A service is granted access because the SID has been added to SQL Server. When you see the login 'NT SERVER\SQLSERVERAGENT' or 'NT SERVICE\MSSQLSERVER', those logins are the service SID that was created for the SQL Server Agent or SQL Server service.
I am still looking for any documentation on this - but what I found was a support article that showed how to add the service SID back into SQL Server because these accounts are not accessible through any GUI interface.
Here is an article that outlines the concept of the per-service SID: http://blogs.technet.com/b/askperf/archive/2008/02/03/ws2008-windows-service-hardening.aspx
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 8, 2011 at 2:56 pm
Thanks, the article helps a little bit.
So if there is no tie in, then if we use that domain account which run sql agent service for other purpose for example to create maintence plan and run some ETL sql jobs using that account, then we have to add that account to sysadmin, not sure that will compromise the purpose of security the windows use sid for restricted security.
April 11, 2011 at 4:34 am
Server->Security->Server Roles->Sysadmin and look at the account name...
April 11, 2011 at 11:08 am
annasql (4/8/2011)
Thanks, the article helps a little bit.So if there is no tie in, then if we use that domain account which run sql agent service for other purpose for example to create maintence plan and run some ETL sql jobs using that account, then we have to add that account to sysadmin, not sure that will compromise the purpose of security the windows use sid for restricted security.
If your service account needs sysadmin rights to run those jobs and/or processes, then that is what it needs and you would have to add it as a login and grant those rights.
I would probably not do that - instead, I would use proxy accounts to grant the appropriate rights needed for each job. The only time you need to use the proxy accounts is when the job needs OS level privileges or network privileges.
If you change the job owner to 'sa' - there isn't anything you need to worry about as far as the service account is concerned. The agent job will run in the context of the agent service login - which has sysadmin rights.
If you need to make sure no agent jobs runs at an elevated privilege like that - then you setup a proxy account that has limited rights and all agent jobs use the proxy account to run instead. The owner of the job stays the same.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 11, 2011 at 11:28 am
Further to what Jeffrey has said, you get the option during the SQL Server 2008 install to use service account SID based security or traditional global\local group security. Obviously service based SID is more secure as any old so and so can simply put themself into the local\global group and gain Sysadmin access to your SQL instance
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 11, 2011 at 12:04 pm
Still a little confused, so when a sql agent job runs, it runs as the SID, or runs as the service account we use?
In our case we have two servers that use the same service account. One is sql 2005, one is sql 2008.
A job runs an ETL on a sql 2005 box, and have to access the database on this SQL 2008 box.
It failed the first time, then we added the service account on sql 2008 box to be db owner of that database, then it worked.
How this can be explained?
In another case, if it's a job only on sql 2008 box, since it is running as SID, so it should have sysadmin, so it should have all its need, correct, so why we need to add the rights to service account?
Thanks
April 11, 2011 at 12:06 pm
Also I think this is also related with job owner.
Does sql agent job run in the context and permissions of the SID, or of the service account, or job owner?
April 11, 2011 at 9:02 pm
If the SQL Server agent job is owned by a member of the sysadmin role, it will run in the context of the service itself.
On Windows Server 2008 systems, if you installed with SID based security - that will be the service SID which has been added to SQL Server as a login with sysadmin rights. If you installed with group\local group security - then the local group was added to SQL Server as a login and granted sysadmin rights. In this case, the context will be the service account that is running SQL Server.
If the SQL Server agent job is owned by a non-privileged (non sysadmin) login, the agent job will run in the context of that owner with that owner's rights.
However, if you setup a proxy account and setup the agent job to use that proxy account - it will run in the context of the proxy account with the proxy accounts privileges.
Now, you have to remember that the service SID is only used by the service itself. So, when you run an SSIS package and it needs to authenticate to SQL Server using Windows authentication - it cannot use the service SID for that authentication. It has to use the service account (or job owner, if not sysadmin) which is why the package tried to authenticate with the service account and failed.
This happens because the agent job is actually starting a separate process outside of SQL Server. SSIS packages are run using Integration Services and not the database engine.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 11, 2011 at 9:29 pm
This is very helpful. Let me study what you said, it may take me a little bit to totally understand this. Thank you.
April 26, 2011 at 10:36 am
Jeff, not sure I understand this, when you say:
"I would probably not do that - instead, I would use proxy accounts to grant the appropriate rights needed for each job. The only time you need to use the proxy accounts is when the job needs OS level privileges or network privileges."
Do yo mean only time I need sysadmn account is when the job ....
if a login has sysadmin rights(not windows admin), does it mean it can access all the files on the file system on the local server?
If not what file system level access does it have?
April 26, 2011 at 8:01 pm
SQLMyFriend (4/26/2011)
Jeff, not sure I understand this, when you say:"I would probably not do that - instead, I would use proxy accounts to grant the appropriate rights needed for each job. The only time you need to use the proxy accounts is when the job needs OS level privileges or network privileges."
Do yo mean only time I need sysadmn account is when the job ....
if a login has sysadmin rights(not windows admin), does it mean it can access all the files on the file system on the local server?
If not what file system level access does it have?
No, normal agent jobs that don't need access to either the file system or network resources would not need to be run using the proxy account. You would setup different proxy accounts for different types of access (e.g. a proxy account to run SSIS, another to run BCP, etc...). Only when that job needs those elevated rights would it be set to use the proxy.
Just because a login to SQL Server is a sysadmin in SQL Server does not mean that login has any OS rights or network rights at all. For example, if you setup a SQL Server login call 'mysa' and granted sysadmin rights to that login - that login would not have any OS rights at all. But, since it is a sysadmin any scheduled jobs would run using the the SQL Server Agent service account - with whatever rights the service account has.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 26, 2011 at 9:33 pm
No, normal agent jobs that don't need access to either the file system or network resources would not need to be run using the proxy account. .
Thanks, that makes sense, I have been wondering why use proxy account, your answer helps me a lot. SSIS package usually need to access some file system drive for example for a flat file source connection, so that's many probably proxy account is created to use for SSIS packages.
You would setup different proxy accounts for different types of access (e.g. a proxy account to run SSIS, another to run BCP, etc...). Only when that job needs those elevated rights would it be set to use the proxy
So the account I need to assign permissions that the ssis package needed is the account that I set up in the credential, correct?
But if different ssis packages have different database access, then I need to grant that account all kinds of permissions, then why I just not give it a sysadmin right, but that contradicts the purpose of using proxy.
For this, I'm confused again.
Just because a login to SQL Server is a sysadmin in SQL Server does not mean that lhogin has any OS rights or network rights at all. For example, if you setup a SQL Servege r login call 'mysa' and granted sysadmin rights to that login - that login would not have any OS rights at all. But, since it is a sysadmin any scheduled jobs would run using the the SQL Server Agent service accouunt - with whatever rights the service account has.
For SQL agent service account, what file system level rights does it usually have?
I guess for sure it has access to data and log directory.
Thank you.
Viewing 12 posts - 46 through 57 (of 57 total)
You must be logged in to reply to this topic. Login to reply