1

I have a jar file that I want to run as a step in an SQL Job. However, the jar file has to run on machineA but the SQL job is schedule on serverA.

To make this possible, in serverA's SQL job, I use xp_cmdshell to issue a wmic command to the terminal.

xp_cmdshell permits me to issue a terminal command from an T-SQL script

wmic permits me to issue a terminal command to machine (in this case a java -jar command)

Below is the command I use

EXEC master..xp_cmdshell 'wmic /user:mydomain\myuser /password:mypassword /node:machineA process call create "cmd /c java -jar D:\jars\saveToSharedFolder.jar"'

saveToSharedFolder.jar is a java application that extracts data from the database and writes it to an excel file, then saves this excel file to a shared folder located in a server say, \serverA\files\savedData.xlsx.

The file savedData.xlsx is not being saved in \serverA\files.

When I tried outputting the java error messages to a file, I got this.

java.io.FileNotFoundException: \\serverA\files\savedData.xlsx (Access is denied)
Exception in thread "main" java.lang.reflect.InvocationTargetException
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.eclipse.jdt.internal.jarinjarloader.JarRsrcLoader.main(JarRsrcLoader.java:58)
Caused by: org.apache.poi.openxml4j.exceptions.OpenXML4JRuntimeException: Fail to save: an error occurs while saving the package : null
    at org.apache.poi.openxml4j.opc.ZipPackage.saveImpl(ZipPackage.java:602)
    at org.apache.poi.openxml4j.opc.OPCPackage.save(OPCPackage.java:1557)
    at org.apache.poi.POIXMLDocument.write(POIXMLDocument.java:248)
    at saveToSharedFolder.saveData(saveToSharedFolder.java:115)
    at saveToSharedFolder.main(saveToSharedFolder.java:46)
    ... 5 more
Caused by: java.lang.NullPointerException
    at java.util.zip.DeflaterOutputStream.<init>(Unknown Source)
    at java.util.zip.DeflaterOutputStream.<init>(Unknown Source)
    at java.util.zip.ZipOutputStream.<init>(Unknown Source)
    at java.util.zip.ZipOutputStream.<init>(Unknown Source)
    at org.apache.poi.openxml4j.opc.ZipPackage.saveImpl(ZipPackage.java:531)
    ... 9 more

I know it is a folder permission issue. However, I am quite loss as to what account should I grant write permission to the folder.

When I try to login in machineA using mydomain\myuser and mypassword that I used in the wmic command, and run the jar file in machineA, it successfully saves \serverA\files\savedData.xlsx

I also tried granting the nt service account I get when I run EXEC master..xp_cmdshell 'whoami'. Because that's the account that runs the wmic account right? But it still doesn't create \serverA\files\savedData.xlsx

It's quite weird though, Since I launched wmic using mydomain\myuser and mypassword shouldn't the jar file be run with myuser's credentials and thus be able to create \serverA\files\savedData.xlsx?

Krish
  • 319
  • 4
  • 19

1 Answers1

0

xp_cmdshell runs under the security context of the SQL Server service account if the invoking login is a sysadmin role member. For non-sysadmin role members, xp_cmdshell runs under the security context of the xp_cmdshell proxy account.

Not sure why you are using T-SQL to invoke the process. Instead, consider using a CmdExec job step type to execute the command directly. You can specify the desired security context of the process in the job step configuration (SQL Server Agent service account or proxy). See https://msdn.microsoft.com/en-us/library/ms190264.aspx.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • Hi @Dan Guzman thanks so much for this. I tried creating a proxy for the local admin account of serverA. I also created a cmdexec job that runs wmic command. I already tested with a test sql job that contains only 1 step which is the cmdexec job. However, when I try to edit the sql job to have step 1 as a t-sql script and step 2 as the cmdexec, I'm getting a **A proxy account is not allowed for a Transact-SQL subsystem. (Microsoft SQL Server, Error: 14517)** error – Krish Nov 16 '16 at 07:52
  • @Krish, you don't need to specify a proxy account for the T-SQL step. That shouldn't even be an option for a T-SQL job step type if you are using the SSMS UI. For jobs owned by sysadmin role members, T-SQL job steps execute under the SQL Agent service account. For non-sysadmin owned jobs, T-SQL steps run under the job owner's security context. The CmdExec and T-SQL step security contexts are independent. – Dan Guzman Nov 16 '16 at 11:15
  • you are right. When I created a new job with step1 as t-sql script ans step2 as cmdexec job, that error didn't appear. I figured it was because the previous job I made had only 1 step with cmdexec job type. Then, I changed that step to be a t-sql script and added step2(cmdexec). I think that's where the inconsistencies began. – Krish Nov 17 '16 at 06:29