October 16, 2007 at 7:45 pm
Hi,
I am confessing over DTS package and DTS schedule job in SQL Server 2000, need advice on doubts below:
1)Based on MS KB Q269074, security context of DTS scheduled job based on job owner (whether a member of SYSADMIN or not).
So, when the schedule jobs is run the authentication setting set in the connection properties within DTS package will totally be ignored and authentication is only just based on the scheduled job owner security context?
2)Based on same article above,
Question: When you right-click the DTS package and you choose to schedule the package, how is the owner assigned?
Answer: The owner of the SQL Agent job depends on how the SQL Server is registered in the Enterprise Manager. If the SQL Server is registered using a Windows NT authentication, the owner of the scheduled job is the account used to start the SQL Agent service. If the SQL Server is registered in SEM using SQL Server authentication (for example, the SA login), the owner of the job is that same SQL Server login.
However, when I schedule a DTS package to run as a scheduled job (SQL Server is register using a NT authentication with local administrator right and SYSADMIN right), the job owner is the same NT login not the SQL Agent service startup account?
Btw, SQLAgent service is startup with LOCAL SYSTEM.
3)If the DTS package of a DTS schedule job is deleted, but the DTS schedule job is still there in SQLAgent job list (I don’t know why it is still there, logically it should be deleted as well), will the DTS schedule job run?
Need to clear the doubts to solve a PROD problem, any kind soul please help.
October 17, 2007 at 8:02 am
I am experiencing a similar issue, hence the reason I have come to SQL Server Central in the hopes of a solution (and possibly a long term relationship)
Synopsis:
SQL Server 2000 Version 8.00
I have a DTS that picks up a text file located on a different server and then imports that data into my database. This job has 1 step in it and runs a command. There are 2 other jobs that depend on the success of this job every day. The command run is: dtsrun /s Oscar /n ioi download /e (/s server name; /n DTS name; /e authentication).
If I run this job from a DOS prompt it works perfectly, and the data is imported without a hitch. The SQL job however, fails every morning.
The job owner is well... the WINDOWS ADMINISTRATOR therefore windows authentication. All other jobs have this owner and are working.
If you have any experience is this area, I would be most grateful for your input.
[font="Arial"]all things in IT are possible, just find a way to do it[/font]
October 17, 2007 at 8:15 am
It's a bit confusing to get at, but my understanding (based on graduation from the old school of hard knocks) of how it works is that it will use the Job Owner's creds WITHIN SQL Server. Meaning - if your task is to move data between two tables, create SQL objects, etc..., it will use the security context of the job owner.
The minute the task involves an object OUTSIDE of SQL (external file, etc...) then it's doing that external portion under the credentials of the SQL Agent service account.
This "split" thing is always a pain, which is why I often resort to scheduling DTS packages with "heavy external" involvement from the OS, and not from within the SQL Agent.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 17, 2007 at 8:47 am
haha, "heavy external" OS involvment is not ideal as I was hoping the jobs would be a way of making life easier.
Thanks for the feedback Matt Miller, perhaps you could answer one more question. If the location of the file is already mapped and therefore requires no authentication when collecting / browsing onto that mapped drive, will this still cause an issue with job?
[font="Arial"]all things in IT are possible, just find a way to do it[/font]
October 17, 2007 at 9:25 am
Steer clear of mapped drives in this case. Just because you see drives mapped when YOU log in, doesn't mean that the service account will. Use UNC notation instead.
In other words, don't use:
v:\filename (where v: might be a drive you see as mapped)
instead, use
\\servername\share\filename
where \\servername\share is something that service account has access to.
did I mention this can get to be a pain yet? :w00t:
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 17, 2007 at 10:49 am
Garrick,
1) If a connection in the package uses Windows Authentication, the connection will be made with the security credentials of the Windows account that runs SQL Server Agent, not the job owner.
2)The job is owned by the Windows account that registered the instance in Enterprise Manager, not by the SQL Server Agent account.
3)Deleting a DTS package does not delete the job that executes it. The job will run as scheduled but, because the package has been deleted, that step will fail.
John,
As Matt indicated, your scheduled package probably failed because the SQL Server Agent Windows account doesn't have permission to read the file on the remote server. And definitely use the UNC path for the file.
Greg
Greg
October 18, 2007 at 12:33 am
Hi Matt, Greg
Thanks again for assistance;
What we have decided to do is run a batch file on the remote server that will export the file to my local DB server, thereby removing the step for the Job to collect it remotely. This appears to be working so many thanks!
Regards,
John
[font="Arial"]all things in IT are possible, just find a way to do it[/font]
October 18, 2007 at 1:05 am
Greg Charles (10/17/2007)
Garrick,1) If a connection in the package uses Windows Authentication, the connection will be made with the security credentials of the Windows account that runs SQL Server Agent, not the job owner.
2)The job is owned by the Windows account that registered the instance in Enterprise Manager, not by the SQL Server Agent account.
3)Deleting a DTS package does not delete the job that executes it. The job will run as scheduled but, because the package has been deleted, that step will fail.
Greg
Greg,
Thanks for that reply. I got further questions on point 1
1) In my case (using the Windows Auth in Connection Properties in DTS package) means the security context is the Local System account, /SYSTEM. My understanding is Local System account has the all priveige within that local server. I couldn't figure out why the scheduled jobs still failed?
What happen if SQL Auth (set SQL login and password) is used in Connection Properties in DTS package, but the scheduled job is owned by a Windows account or SQL account?
What is the security context of job execution in this scenario?
2) That's exactly what I encountered. That's means the MS is wrong in their KB documentation. It's just confussing for a 1st time reader of that KB article who didn't verify the fact.
3) That's crazy right! Logical sense tells that if the DTS package is no longer required (that's reason it got deleted) then why brother keeping the scheduled jobs that execute it. I hope that isn't in SQL 2005 or 2008. Our shop mainly run SQL 2000.
October 18, 2007 at 10:04 am
Garrick,
1) If a connection in the package uses a SQL Server login, it won't affect the job execution at all. Of course, a SQL Server login won't be able to access anything outside of SQL Server, like a text file or Excel file.
BTW, you didn't mention why the job was failing. What was the error?
3) You have to realize that a job might have other steps beside the step that runs a package. We have lots of scheduled jobs that run several DTS packages in separate steps or run a package in one step and create a report in another step. We wouldn't want to delete the entire job just because one of the packages isn't required anymore.
Greg
Greg
October 18, 2007 at 10:13 am
Also - as far as the deletion issue is concerned - you get the same behavior if you're using a stored proc somewhere. You referencing an object from something else is not going to stop you from deleting it, unless it's in use WHEN you try to delete it.
That's not anything specific to DTS.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 18, 2007 at 7:47 pm
Background:
I have a PROD server having database replication using a third party product from EMC called Replistor (i think is was a product by Legato previously). It is replicating the data files (mdf, ldf) based on file IO changes including the system database e.g. master, msdb, model from PROD to DR server. I found out that the all database objects are replicated to DR server however the DTS job failed in DR server.
PROD : SQL 2000 Ent Edn on 2-node cluster Win 2000 Adv Server
MSSQLServer and SQLAgent service acct is a domain acct.
DR : SQL 2000 Ent Edn on standalone Win 2000 Adv Server
MSSQLServer and SQLAgent service acct is LOCAL SYSTEM.
The two servers are on diff segment and DR is in workgroup env. Don’t ask me why, it’s a history. I realized that the DTS scheduled jobs and the DTS packages appeared in DR is owned by a same domain account in PROD (the replication product replicated exactly everything from PROD to DR) that doesn’t exist in DR server bcos DR not in any domain. Obviously it will failed for DTS that has conn properties using Win Auth. So, I changed the owner to ‘sa’ for DTS packages and DTS jobs. So, as Greg have mentioned bcos conn properties using Win Auth so the security context of the job execution is the SQLAgent service acct which is \SYSTEM and it has full rights within the local machine (I know running LOCAL SYSTEM is not recommended but let’s leave that aside). From what I have read and gathered is it has all the rights required.
However, the DTS job failed still failed with the error
"Login failed for user '(null)'. Reason : Not associated with a trusted SQL Server connection."
I am not why since it is should used the Win Auth for authentication.
SQL instance Auhtentication setting in DR is set to mixed mode.
Sorry for the long story… I am finding these strange things happening without answer.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply