batch file with sqlcmd needs correct user permissions

Question

I have a SQL script which was working in SQL Management Studio, but could not be executed as batch.
I found that when I use the import.csv file from the path C and not the desired network path (D:), that it is working

Batch

rem ---------- SQL Import/Merge Configbuilder File ----------
SET LOGFILE=MyLogFile.log
call :Logit >> %LOGFILE% 
exit /b 0
:Logit
sqlcmd -S localhost\SERVER1 -d Database-U sa -P Password -i "Import.sql"
CMD

import.sql

BULK
INSERT Configbuildertemp
FROM 'D:\Batchfiles\Configbuilderimport.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
GO

Question:

Which user is executing the sqlcmd request in the batch file when I (connected on server with administrator) the desired goal is to execute through Windows task scheduler?

I could however insert in batch to copy file to C:\ and delete it again after execution, but that would be bulls.

Answer 1

In Windows task scheduler you can define for each task which user account should be used to run the task. If you define the user account which has permissions to read files from network share containing Configbuilderimport.csv for the task running the batch file, you can use a batch file like this:

rem Remove a perhaps already existing network share mapped to drive D:.
%SystemRoot%\system32\net.exe use D: /delete /yes 2>nul

rem Temporarily map \\ServerName\NameOfShare to drive D: using credentials
rem of user account running this batch file as defined in task scheduler.
%SystemRoot%\system32\net.exe use D: \\ServerName\NameOfShare /persistent:no

rem ---------- SQL Import/Merge Configbuilder File ----------
SET LOGFILE=MyLogFile.log
call :Logit >> %LOGFILE%

rem Remove network share mapped to drive D:.
%SystemRoot%\system32\net.exe use D: /delete /yes 2>nul
exit /b 0

:Logit
sqlcmd.exe -S localhost\SERVER1 -d Database-U sa -P Password -i "Import.sql"
goto :EOF

But if the batch file is executed by local administrator account which of course does not have permissions to access files on \\ServerName\NameOfShare it would be necessary to specify password, domain and user name in the batch file. This is more insecure in comparison to task defined with right user account as everybody with access to the batch file can read the password. The password entered in task scheduler for the user account used for running the batch file is encrypted by Windows.

rem Remove a perhaps already existing network share mapped to drive D:.
%SystemRoot%\system32\net.exe use D: /delete /yes 2>nul

rem Temporarily map \\ServerName\NameOfShare to drive D: using credentials
rem specified here directly in the batch file in the line below.
%SystemRoot%\system32\net.exe use D: \\ServerName\NameOfShare password /user:domain\username /persistent:no

rem ---------- SQL Import/Merge Configbuilder File ----------
SET LOGFILE=MyLogFile.log
call :Logit >> %LOGFILE%

rem Remove network share mapped to drive D:.
%SystemRoot%\system32\net.exe use D: /delete /yes 2>nul
exit /b 0

:Logit
sqlcmd.exe -S localhost\SERVER1 -d Database-U sa -P Password -i "Import.sql"
goto :EOF