VBS Open Database Connection Fails Via Task Schedule Via Batch, Tries To Connect As Task Schedule User Not Database User

[shell_l_d]

I have a Task Schedule running a Batch file which calls a VBS Script to create a connection to a database among other stuff.


The VBS script opens a connection to the database as a specified user ("User ID=my_db_user;Password=my_db_pswd;"). The issue is when it is run via Task Scheduler, the userid & password in the VBS code is completely ignored and it tries to open the database as the Task Schedule User 'MYDOMAIN\MY_TASK_SCH_USER' instead but there is no such user in the database, nor do we want to create one.

Why please?


Eg: Task Schedule setup similar to:
* General
** When running the task, use the following user account: 'MYDOMAIN\MY_TASK_SCH_USER' (different to 'Author')
** Run whether user is logged on or not
** Run with highest privileges (tried with & without this)
** Configure for: Windows Vista, Windows Server 2008
* Action:
** Start a Program: C:\test\my_batch_file.bat
** Start in (optional): C:\test\

The server has Windows Server 2008 R2 Standard, SP1 & is 64bit



EXAMPLE ERRORS:
<code>
2019-03-18 14:46:59 - ERROR: Unable to open the database connection.
2019-03-18 14:46:59 - ERROR #1: Error Description: (-2147217843) Login failed for user 'MYDOMAIN\MY_TASK_SCH_USER'.
<code>



EXAMPLE BATCH (my_batch_file.bat):
<code>
cscript C:\test\my_vbs_file.vbs 1>C:\test\test.log 2>&1
TASKKILL /F /IM cmd.exe
<code>



EXAMPLE VBS (my_vbs_file.vbs):
<code>
Option Explicit
On Error Resume Next

Dim objConn, objRecSet

' Create database connection
Set objConn = CreateObject("ADODB.Connection")
If Err.Number <> 0 Then
' Error handling
Wscript.Echo FormatDateTime(Now) & " - ERROR: Unable to create database connection &/or record set."
End If

' Open a connection to the database
objConn.Open _
"Provider=SQLOLEDB;" & _
"Data Source=MYDBSERVER;" & _
"Trusted_Connection=Yes;" & _
"Initial Catalog=MyDatabase;" & _
"User ID=my_db_user;Password=my_db_pswd;"
If Err.Number <> 0 Then
' Error handling
Wscript.Echo FormatDateTime(Now) & " - ERROR: Unable to open the database connection."
Wscript.Echo FormatDateTime(Now) & " - ERROR #1: Error Description: (" & Err.Number & ") " & Err.Description
End If

' Close the database connection & record set
objConn.Close
Set objConn = Nothing

' Returns the given date in the format: "yyyy-mm-dd hh:mm:ss"
Function FormatDateTime( dte_Date )
FormatDateTime = Year(dte_Date) & "-" & _
Right("0" & Month(dte_Date) ,2) & "-" & _
Right("0" & Day(dte_Date) ,2) & " " & _
Right("0" & Hour(dte_Date) ,2) & ":" & _
Right("0" & Minute(dte_Date),2) & ":" & _
Right("0" & Second(dte_Date),2)
End Function
<code>

Continue reading...