Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mortenharket
Starting Member
3 Posts |
 Posted - 2004-02-10 : 13:54:25
|
| Hello. This is a follow-up to an earlier question I asked at(http://www.devdojo.com/braindump.php?show=163). However, after somesuggestions and links to Knowledge Base articles, the issue still remains. The scenario in brief is thus: We have a DTS package in SQL Server 2000 that contains only 1 step. The package was created on the SQL Server machine itself (and not some remote machine). This step is a VB6 executable. This exe resides on the SQL Server machine itself and looks at a settings.ini file where some parameters are set. One of these parameters tells the exe which folder to write the files to while executing. This folder is a mapped drive that is mapped on the SQL Server machine. This "external" mapped drive physically resides on a Linux machine. --------------------------------------------------------- The issue is thus: When the package is executed (in Enterprise Manager->Data Transformation Services->Local Packages), it works perfectly fine. However, when we schedule the package, it does not work. No files are written to the mapped drive. --------------------------------------------------------- Articles referred to: http://support.microsoft.com/default.aspx?scid=kb;en=us;q269074 --------------------------------------------------------- Tests carried out: As suggested in the above article, we did the following: 1) Changed the settings.ini file. The parameter that initially had a value J:\FTP\SomeFolder is now \\FTPComputer\FTP\SomeFolder. That is we changed it to using UNC from using drive letters. 2) We created a new user with Admin privileges to the SQL Server machine. The user is called sqlserver. This user has access to all the databases in SQL Server and is also included in the System Administrator roles under Server Roles in Enterprise Manager. 3) On the Linux machine, this user has full access to the \FTP folder. 4) We log on to the SQL Server machine as user sqlserver. 5) In Enterprise Manager, under Management->SQL Server Agent->Jobs, the job owner is sqlserver. 6) In Enterprise Manager, under Management->SQL Server Agent, right click SQL Server Agent->Properties->General Tab, Service Startup Account is set to System Account (i.e. Windows system account). These were the drive mapping and security context suggestions that we followed. The scheduled job does not write to the folder on the Linux machine. Note that if we have the exe write to a folder on the SQL Server's local drive, it works either way (execute package or schedule package). We are going to try using a Windows mapped drive instead of a Linux mapped drive to see if this really hasanything to do with Linux. I will post those results here too. If anyone does have any further suggestions, I will be grateful! Thanks. |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-10 : 13:59:56
|
| When a package is run by you in QA/EM, it runs under the context of thew client's drive mappingsWhen it runs as a scheduled job it runs under dql server service account that the sql agent is set up with...basically the context of what the server has...Any help?Whats the file path look like?Brett8-) |
 |
|
|
mortenharket
Starting Member
3 Posts |
Posted - 2004-02-10 : 14:20:56
|
| Hi Brett,The SQL Server Agent is set up with the Windows account login, if thats what you meant. I right click on SQL Server Agent, Properties, and under the General tab, it is selected to System Account. I am logged in to the SQL server as user: sqlserver. Under this login, I have mapped the drive (drive letter J) in Windows explorer using the username as sqlserver and password for that account. Also, when I schedule the package in EM (right click on the package under Data Transformation Services->Local Packages), it gets added under Jobs in SQL Server Agent and the job owner is sqlserver. Also, sqlserver has System Administrator role under Server Roles in Security in EM.Should I change the SQL Server Agent account from System Account to Windows Account and then type in the username as: .\sqlserver with the password? Will that help?I tired changing the SQL Server Agent Service Startup account to Windows Account earlier, but I had typed sqlserver as the username. It threw an error saying that it could not find that account name. So I'll try .\sqlserver.The mapped file path is: J:\FTP\DailyValues\DataIn the application's settings.ini file, I changed it to a UNC path as: \\FTPMachine\FTP\DailyValues\Data\. Thanks for the quick response.Morten |
|
|
|
mortenharket
Starting Member
3 Posts |
Posted - 2004-02-10 : 15:56:26
|
| Hey Brett, I fixed it. I logged in to the SQL Server machine as user: sqlserver. In EM, I right clicked on SQL Server Agent, Properties and under the General tab I set the Service Startup Account to This account and set the username as <SQL Server machine name>\sqluser with the password. It works now.I had tried doing this earlier in the morning and it would show a message: SQL Server Agent will need to be restarted. On saying Yes, it would throw an error saying that the username is not a valid Windows NT account name and would reset the Service Startup Account as System Account. It suddenly accepted it.Problem fixed, and I learnt a lot in these 2 days. Thanks. |
|
|
|
|
|
|
|
|
DTS package scheduled as a Job - Problem