Windows async batch cannot open database requested by the login

77 views Asked by At

I'm running SQL Server 2005 (Express) on Windows Server 2003 SP2. I'm using a scheduled task to launch a batch program that runs for several hours, during which many child processes are launched.

Almost all these child processes are running synchronously, so the root batch program waits for each child program to complete.

As one of the last actions in the root batch program, several child processes are called asynchronously using start. This allows programs that depend on the work done by this scheduled task to start working on the updated data. The root batch program finishes by sending an email.

scheduled.bat

This is the batch program which is scheduled in Windows Scheduled Tasks.

cd backend
for /f "skip=1" %%x in ('wmic os get localdatetime') do if not defined timestamp set timestamp=%%x
taskkill /F /FI "USERNAME eq admincm" /FI "WINDOWTITLE eq AtlasSync*"> Sync.Lte.%timestamp:~0,14%.log
title AtlasSync-%timestamp:~0,14%
sqlcmd -t 600 -d ATLAS -i Sync.Lte.build.sql -o Sync.Lte.bat
Sync.Lte.bat>> Sync.Lte.%timestamp:~0,14%.log

Sync.Lte.Bat (truncated)

The root batch program which calls sqlcmd, putty and mail several times. It connects to a remote host, runs a few scripts there, then collects all the log files. It FTPs the data back to the Windows server, where it gets imported into the "ATLAS" database. As soon as the data has been imported, dependencies are called via start.

D:
cd D:\PATH\Sync\backend

echo #!/bin/sh> DirtyNodes.Lte.sh
echo cd /home/cm/PATH/SYNC/>> DirtyNodes.Lte.sh
...

echo open 12.34.56.78> DirtyNodes.Lte.ftp
echo cm>> DirtyNodes.Lte.ftp
...
ftp -v -s:DirtyNodes.Lte.ftp

echo echo `date` -- cd /home/cm/PATH/SYNC/> DirtyNodes.Lte.putty
echo cd /home/cm/PATH/SYNC/>> DirtyNodes.Lte.putty
echo echo `date` -- chmod 755 DirtyNodes.Lte.sh>> DirtyNodes.Lte.putty
...
D:\PATH\TOOLS\putty -ssh -load AtlasSync -l cm -pw XXXXX -P 22 -m DirtyNodes.Lte.putty 12.34.56.78

echo #!/bin/sh> Refresh.Lte.build
echo MOS=/home/cm/PATH/SYNC/Refresh.Lte.mos>> Refresh.Lte.build
echo rm -f $MOS>> Refresh.Lte.build
...

echo #!/bin/sh> Refresh.Lte.sh
echo cd /home/cm/PATH/SYNC/>> Refresh.Lte.sh
echo echo `date` -- Running refresh amosbatch>> Refresh.Lte.sh
...

sqlcmd -t 600 -d CM -h -1 -W -i IpDatabase.Lte.build.sql -o ipdatabase
echo open 12.34.56.78> Refresh.Lte.ftp
echo cm>> Refresh.Lte.ftp
...
ftp -v -s:Refresh.Lte.ftp

echo echo `date` -- cd /home/cm/PATH/SYNC/> Refresh.Lte.putty
echo cd /home/cm/PATH/SYNC/>> Refresh.Lte.putty
echo echo `date` -- chmod 755 Refresh.Lte.build>> Refresh.Lte.putty
...
D:\PATH\TOOLS\putty -ssh -load AtlasSync -l cm -pw XXXXX -P 22 -m Refresh.Lte.putty 12.34.56.78

echo open 12.34.56.78> Import.Lte.ftp
echo cm>> Import.Lte.ftp
...
ftp -v -s:Import.Lte.ftp

cd D:\PATH\Sync\tables
unzip -o Tables.Lte.zip
cd D:\PATH\Sync\backend
sqlcmd -t 600 -d ATLAS -i Import.Lte.build.sql -o Import.Lte.sql
sqlcmd -t 600 -d ATLAS -i Import.Lte.sql -o Import.Lte.log

start D:\PATH\Harmony\backend\ConsistencyCheck.bat
start D:\PATH\Compass\backend\ConsistencyCheck.bat

cd D:\PATH\MAIL\XXXXX
mail.exe -s "Atlas Synchronized" -f "D:\PATH\Sync\backend\Import.Lte.log" -b [email protected] [email protected]

ConsistencyCheck.bat

This batch is started from Sync.Lte.bat asynchronously. It uses the same credentials as the user running the root batch program.

D:
cd D:\PATH\Harmony\backend
sqlcmd -t 10800 -d ATLAS -i ConsistencyCheck.sql -o ConsistencyCheck.log

ConsistencyCheck.log

Sync.Lte.bat runs just fine, and the email at the end is sent. But in ConsistencyCheck.log, I find the following errors:

Cannot open database "ATLAS" requested by the login. The login failed.
Login failed for user 'XXXXX\admincm'.

  • I have made both XXXXX\admincm and admincm db_owner of ATLAS.
  • All programs called from the scheduled task are run under the same user. All other programs (including sqlcmd calls to ATLAS) authenticate just fine, but the started programs do not.
  • When running ConsistencyCheck.bat manually (interactive RDP session) as another user, it runs fine.
  • When running ConsistencyCheck.bat manually as XXXXX\admincm, it runs fine.
  • When scheduling a test from XXXXX\admincm in a couple of minutes (making sure I'm fully logged out of that user in an attempt to emulate normal environment), it runs fine.
  • When the original scheduled task runs at night, the async'd batches fail with the mentioned errors above.
0

There are 0 answers