November 20, 2009 at 8:39 am
Okay, I've been running into some issues lately, and while trial and error occasionally works, I'd like a better idea on what's going on behind the scenes.
Here's the scenario -- we have a seperate domain account for SQL Services. The Agent, SSIS, the DB engine, and even SSRS.
Our devs are starting to dig into SQL Server, and are using processes that touch several of these services at once.
For example, the Agent runs a SSIS package, that executes a stored proc in the DB. These jobs do things like read files from a seperate machine that's a file share server. Sometimes, the read/writes to files happen in different places. Sometimes the stored proc does it, sometimes they're doing it in SSIS.
I'm getting confused about which account needs access to the file share. Agent, SSIS, the DB engine? Is this documented anywhere? I know I could just give all accounts permissions, but I like to keep the minimum required. It just seems like sometimes, the server account is doing the reading, sometimes the agent...
November 20, 2009 at 9:10 am
The one that should matter is the one that initiates the calls.
For example, with a job, a job will be owned by a specific domain account. The job will access domain resources based on that account. If the job calls an SSIS package, it will still use the job's account to determine authorization.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 20, 2009 at 9:31 am
That sounds reasonable, and makes sense, but it's not what I'm seeing, unless I'm missing something.
When you say owner, you mean the SQL Server Login owner of a job? Or the account specified in the "Run as ..." for the STEP of the job? Either way, they're both set to the Agent account in this case, and the agent has permissions to the share, but I"m still hitting Access Denied. hmmmm...
November 20, 2009 at 9:44 am
For example, the Agent runs a SSIS package, that executes a stored proc in the DB. These jobs do things like read files from a seperate machine that's a file share server. Sometimes, the read/writes to files happen in different places. Sometimes the stored proc does it, sometimes they're doing it in SSIS.
I'm getting confused about which account needs access to the file share. Agent, SSIS, the DB engine? Is this documented anywhere? I know I could just give all accounts permissions, but I like to keep the minimum required. It just seems like sometimes, the server account is doing the reading, sometimes the agent...
No but in SQL Server 2005 and up SSIS package automation account must be admin either direct or a proxy to an admin account because as you are looking at all the data being moved Microsoft wants those accounts going back to someone with permissions to allow such operations.
Kind regards,
Gift Peddie
November 20, 2009 at 9:55 am
Makes sense, but my Agent account has admin already, and the errors I'm getting are OS access denied issues. It lacks the permissions to open the file on the remote file share, despite that share and file giving access to the Agent domain account.
November 20, 2009 at 10:02 am
llevity (11/20/2009)
Makes sense, but my Agent account has admin already, and the errors I'm getting are OS access denied issues. It lacks the permissions to open the file on the remote file share, despite that share and file giving access to the Agent domain account.
That may not be the reason it may be the account is not domain admin or someone have removed the Everybody default group from that folder but application runtimes like the Agent is also a member of the Everybody group.
Kind regards,
Gift Peddie
November 20, 2009 at 11:39 am
Okay, confused even more now. You're saying the domain account that runs the SQL Agent service has to be domain admin? Or the share has to have the Everybody group? Isn't that sort of overkill? It sounds like it would work, but doesn't sound like the most secure way.
November 20, 2009 at 11:49 am
llevity (11/20/2009)
Okay, confused even more now. You're saying the domain account that runs the SQL Agent service has to be domain admin? Or the share has to have the Everybody group? Isn't that sort of overkill? It sounds like it would work, but doesn't sound like the most secure way.
If you are in Windows 2003 and up there is no security risk because the anonymous user is not part of the Everybody group so there is very limited risk and SQL Server 2005 was released two years after Windows 2003.
Kind regards,
Gift Peddie
November 20, 2009 at 12:47 pm
llevity (11/20/2009)
Okay, confused even more now. You're saying the domain account that runs the SQL Agent service has to be domain admin? Or the share has to have the Everybody group? Isn't that sort of overkill? It sounds like it would work, but doesn't sound like the most secure way.
Here is the doc covering accounts for the Agent and it is almost what I have been telling you. The Agent account is important because it also runs Replication and jobs.
http://msdn.microsoft.com/en-us/library/ms345380.aspx
Kind regards,
Gift Peddie
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply