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

  • Question

  • 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:

    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'.

    EXAMPLE BATCH (my_batch_file.bat):

    cscript C:\test\my_vbs_file.vbs 1>C:\test\test.log 2>&1
    TASKKILL /F /IM cmd.exe

    EXAMPLE VBS (my_vbs_file.vbs):

    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




    Regards

    Shell



    • Edited by shell_l_d Monday, March 18, 2019 5:03 AM
    • Moved by Bill_Stewart Wednesday, September 4, 2019 6:47 PM This is not scheduler troubleshooting forum
    Monday, March 18, 2019 5:01 AM

All replies

  • If the database uses "Integrated" security then adding a user and password will not work


    \_(ツ)_/

    Monday, March 18, 2019 5:20 AM
  • The fix is to remove the following line from the database open code in the VBS script:
    "Trusted_Connection=Yes;" & _

    Regards Shell SQL Server 2000/2005

    Monday, March 18, 2019 10:13 PM
  • The fix is to remove the following line from the database open code in the VBS script:
    "Trusted_Connection=Yes;" & _

    Regards Shell SQL Server 2000/2005

    If the database does NOT use integrated security then this would be true.  You said you tested the code at a prompt.  Apparently you didn't test that exact code or you would have seen the same error.


    \_(ツ)_/

    Monday, March 18, 2019 10:28 PM