Pass parameters from SQL table into batch file

SQL Server Administration
1

Hi guys!

I have a SQL Agent job step which archives a file and also encrypt it. This is working great but the issue is visible password into command line.

How can I call password from a SQL Server table into batch file when archive/encrypt process is taken place?

Thanks

2

So your batch file is something like

MyArchiveAndEncrypt.EXE SomeFile.XXX Password=%1

and your SQL Agent job does

X:\MyPath\MyBatchFile.BAT SecretPassword

??

If so what are you worried is spying on that?

BATch file could query DB to get the password ... but anyone else could do that too.

Pass a time-limited token to the BATch file, which enables it to query the DB to get the password, but if used "at the wrong time" won't get you a valid password

There are probably far more secure methods, but I'm not crypto-skilled!

3

Kristen, I have like this:

  • job step execute .BAT file which contains following:

set root=
CD /D %root%
7z.exe a -t7z -pPASSWORD FileName.7z FileName.sql

  • what I would prefer:

set root=
CD /D %root%
7z.exe a -t7z "Command to retreive PASSWORD from SQL table" FileName.7z FileName.sql

Any idea?

4

I don't see how having the password saved in a database table would make the process more secure.
Instead of putting the command inside a batch file, put the command in the sql agent job step, so the whole command will be inside a database table (msdb.dbo.sysjobsteps for the record). Only sysadmins and member of builtin msdb roles can query that table.

5

"%root%" will be blank ... you need to have

SET root=D:\
CD /D %root%

or similar.

6

I wonder if [the executed command] will be logged anywhere? SQL Error Log for example?

7

In case of failure, the command will be logged in the job history. Again, under the DBAs' control.

8

Kristen, root is not blank. Somehow copy/paste didn't work when I posted.

Spaghettidba, it is a method what are you suggesting but also the password will be seen in the step. I'm trying to present to my superior a solution based on keeping the password somewhere else (such as SQL table) and not in sight (psychological impact :slight_smile: ).

From my point of view, I would rather spend time developing a SSIS package instead of one job with multiple steps but that's another story.

9

I thought it looked odd!

The MarkDown formatting here, which takes hints FROM the text, is a nightmare for this type of thing, silently helping itself to some characters assuming they are formatting instructions :frowning:

You can do

    ```text
        ... some stuff ...
    ```

to encapsulate some "content" verbatim.

10

Don't create a batch file at all - create a stored procedure that reads from a table all of the necessary parameters and build the command. Then execute the command using xp_cmdshell.

11

Needs permissions "loosening" to allow xp_cmdshell - that might already have been done for other such processes, of course ...

12

No, my solution for this doesn't involve xp_cmdshell using at all and I intended to keep this way...or develop a SSIS package.

13

Ok, guys. I did it!
This is the batch file:

for /f %%a in ('SQLCMD -E -S server -Q "set nocount on; select * from table" ') do set output=%%a
echo %output%
set root=D:
CD /D %root%
7z.exe a -t7z -p%output% FileName.7z FileName.sql

14

I didn't know that was possible! Definitely will use that :slight_smile: