May 19, 2010 at 9:30 am
Hi,
I wonder if you could help me explain this by answering the questions...
I have a DTS package. It ran fine. It does a createobject in VBScript to a file location on another server, using a D$ mapping.
Our network team are going through a security audit and are disabling local admin on all production servers. The local admin has been disabled on the server running the DTS for some time, but has only just been disabled on the server with the D$ location.
The job fails when run as a scheduled job, but works when run manually thru dts designer.
I changed the owner of the package from the local admin of the dts host, to a domain services account with the correct privileges.
I changed the owner of the scheduled task to that same domain account.
The SQL Server Agent service is running as a domain account with privileges.
Nothign worked.
However, this has been resolved by using a D mapping rather than D$.
I need to understand why this happened....
Why did the job work until the local admin was turned off on the D$ server?
Why does it now work using D mapped straight?
Why can I browse to D$ using the users involved?
Which "user" is the master of all these? We have the DTS owner, the SQL Job owner, and the SQL Agent Service account - Do the owners have no effect? What is the point of them? Confusing (I don't think many people really know - a search on the web shows a lot of guesswork to get to a resolution)
Finally another weird one - My own account is in the local admin group of the sql server with the dts on it. If I go into the package and make a change and save it switches the owner back to being the old local admin that is now disabled on this server. Weird.
Anyway - Please please PLEASE help me understand this.:hehe:
May 20, 2010 at 3:19 pm
Sounds strange. Is it possible that your security team is doing more than just locking the local admin account out? Such as changing the impersonation level in group policy?
Joie Andrew
"Since 1982"
May 25, 2010 at 5:10 am
I will get them to check.
In order to resolve this, I am trying to answer my own questions through investigations I have made. If someone could confirm I am correct that would be great!
Why did the job work until the local admin was turned off on the D$ server?
Because regardless of the permissions of the job running under SQL Server Agent, it must be defaulting to Local Admin permissions when connecting to a $ share.
Not certain that this is correct.
Why does it now work using D mapped straight?
Because it is then using the SQL Server Agent account rather than local admin.
Sounds correct...
Why can I browse to D$ using the users involved?
Haven't got a clue why this would work but my first question wouldn't. It's a contradiction!!!
Help!
Which "user" is the master of all these? We have the DTS owner, the SQL Job owner, and the SQL Agent Service account - Do the owners have no effect? What is the point of them? Confusing (I don't think many people really know - a search on the web shows a lot of guesswork to get to a resolution)
The account that SQL Server Agent is running under is the master. It is that account that has to have the relevant permissions to everything on the various servers - with database permissions based off of this, or overridden by a connection or connection string otherwise.
The owners are just there to decide who has permissions to modify the package.
Just found this on Microsoft site...
SQL Server 2000
The security context in which the job is run is determined by the owner of the job. If the job is owned by a login that is not a member of the Sysadmin server role, then the package is run under the context of the account setup as the SQL Agent Proxy Account, and has the rights and permissions of that account.
For SQL Agent Proxy to be able to run jobs that connect to SQL Server, the SQL Agent Proxy account must have proper Windows/NT permissions and be granted login access to SQL Server with appropriate database permissions. For the jobs that execute a DTS package, the SQL Agent Proxy Account must have read and write permissions to the temp directory of the Account the SQL Server Agent is running under. For example,
c:\Documents and Settings\<Account>\Local Settings\Temp
If the job is owned by an account (either a SQL Server login or a Windows NT authenticated login) that is a member of the Sysadmin role, the SQL Agent job runs under the context of the account used to start the SQL Agent service.
Also, if the job is owned by a Windows NT domain account and if the package is stored in the SQL Server or SQL Server repository (not as a file), you must start the SQL Server service by using an account from the same domain or an account from a trusted domain. For example, if the SQL Agent job is owned by an account from the USA domain, then the account used to start the SQL Server service must be either from the USA domain or a domain trusted by the USA domain. If the SQL Server is started using a local account, the package fails to run.
That mops that up!
My own account is in the local admin group of the sql server with the dts on it. If I go into the package and make a change and save it switches the owner back to being the old local admin that is now disabled on this server. Weird.
I think this is because of a bug where the creator always stays the same regardless of the owner. If you make any changes it will automatically switch the owner back to the original creator - TOTAL GUESS.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply