Running an executable from DTS Package

No...The executable is located on c:\Program Files

09.11.2007 at 09:04AM PDT, ID: 19869810

Rank: Guru

The error code you got indicates a DTS security issue. Are you using a UNC or an actual drive letter to point to the executable?

You need to make sure that the account you're using to run SQL Agent has permission to access the folder(s) in question.

09.11.2007 at 09:20AM PDT, ID: 19869942

I logon to the sql server box as a domain administrator, and that domain administrator account is a sqlserver account with administrative access. The SQL Agent account runs under an account called sqlsched, and I don't see this account in the SQL logons. What permissions should the sqlsched account have?? I don't seem to have any other problems with the other jobs that I have created using this same account. Does this sqlsched account need administrative rights in sqlserver?

09.11.2007 at 09:25AM PDT, ID: 19869980

Rank: Guru

It will depend on whether you're accessing the folder through UNC / Share or drive letter. Logging on as a domain admin doesn't give you any additional permission unless the share explictly grants it.

You sqlsched account should have permission to a share, if a share is being used.

Are you using a UNC path / Share or an actual drive letter to point to the executable?

09.11.2007 at 09:34AM PDT, ID: 19870060

Rank: Guru

Why don't you just use the File Transfer Protocol Task from th DTS package that is exactly for transfering file through FTP.

Accepted Solution

09.11.2007 at 09:35AM PDT, ID: 19870069

The Process task in the DTS program uses a letter designation (c:\Program Files\)

09.11.2007 at 09:39AM PDT, ID: 19870101

Rank: Guru

I'm not sure I understand where the problem is.

09.11.2007 at 09:39AM PDT, ID: 19870104

Rank: Guru

Good to know...there is no security issue via UNC or a share, however, the error code you got still indicates a security issue.

What permission does your sqlsched account have to the folder under c:\program files\?

This permission problem is at the OS level and not SQL Server. Likely you won't be able to resolve it by giving it any more permission in SQL Server.

09.11.2007 at 09:45AM PDT, ID: 19870148

I have created at least 10 jobs and scheduled them using the SQLSched account and have never encounted any problems. These jobs access files both from the local machine as well as from domain file shares. I have never had a problem with this account before as far as local machine or file share access goes.
I just don't understand why it runs when I execute from the DTS window, but not when I schedule it.

09.11.2007 at 09:58AM PDT, ID: 19870231

Rank: Guru

because whe you execute it from the DTS window it runs under your windows account but when it runs from job uses whatever is set for the SQL Agent account which usualy is SEVERNAME user and that user doesn't have the same permissions as you. You can set for the SQL agent to use a different user with proper permission.

You haven't answered why using the FTP task was a problem.

09.11.2007 at 09:59AM PDT, ID: 19870243

Zberteoc -- I think I am going to have to try and use the FTP task. I was trying to avoid it because I have to get all the information (user account, passwords, etc) from the vendor, and they are not very responsive sometimes. I just thought that since this program was already installed and configured, I could easily call it sql scheduler.

09.11.2007 at 10:02AM PDT, ID: 19870264

Rank: Guru

If you set a log file for the package ( open in design mode, rightclick on background > Package Properties > Logging ) you'll se the different accounts used in the two cases.

09.11.2007 at 10:49AM PDT, ID: 19870614

Rank: Master

apirnia:

The SQL Server agent uses different permissions than MS SQL Server. You have to change both of them to the same thing. Right click your Server name in Enterprise manager 2000 and select properties.
Then click on the security tab. In here the top portion is permissions for SQL Server and the bottom part is for the services. They have to be the same or same permission.

You can also go to >>Control Panel >> Administrative Tools>> Services

Under list of the services look for SQL Server (If using SQL 2005) in 2000 you should look for
SQLSERVERAGENT and MSSQLSERVER They both need to run under same account and permission. You can change them here

09.11.2007 at 11:04AM PDT, ID: 19870750

apirnia -- OKAY...the account (sched) is used to run both the SQLServer and SQLServer Agent Services. However, I notice that this domain user (sched) does not have a SQL Server Login. I know this is a stupid question, but does the user account that run the SQL Server Services need a SQL Server Login?? If so, what level of permissions should I request for it?

09.11.2007 at 11:09AM PDT, ID: 19870803

Rank: Guru

Your domain user sched does not need to have a SQL Server login. That account DOES need permission to the file system under C:\program files\ to gain access to any file there.

Earlier you indicated that you've made 10 other jobs that do things in the file system with no trouble. Are any of them accessing files in the specific location as the failing executable?

09.11.2007 at 11:15AM PDT, ID: 19870852

The other jobs have accessed various folders on local c drive, but not specifically in the c:\program files directory.

09.11.2007 at 11:17AM PDT, ID: 19870875

Rank: Guru

Give that folder,c:\program files , (with all subf) acces to everyone an will work.

09.11.2007 at 11:33AM PDT, ID: 19871018

Rank: Master

apirnia:

Usually what I do when I implement things like this I give the highest possible permission to the user or account untill I get the program to work. Once everything looks good and I know that it works I take away the permission ang change it to minimum requiered.

The user sched does need READ,WRITE,EXECUTE access to the folder but does not have to be in SQL Server security. If you are the administrator I would try changing the services to the Domain Admin to get things started and change from there, or try adding the sched user to the Domain Admin group.

The other thing that you have to make sure is the person/user that is creating the DTS package. The DTS is also need to be created by the user sched.

09.11.2007 at 11:45AM PDT, ID: 19871129

I made the c:\program files\tvpost director a domain share and granted everyone full control of it. I still get the same error about the return code.

09.11.2007 at 12:29PM PDT, ID: 19871482

Rank: Guru

If you made it a share the user account will need folder permissions and share permissions.

09.11.2007 at 12:32PM PDT, ID: 19871518

Rank: Guru