SQL Script to Run DOS Batch File

Question

Post reply

SQL Script to Run DOS Batch File

Anonymous

Mr or Mrs. 500

Points: 559
More actions
September 26, 2011 at 2:21 pm

#248463

I am running SQL Server 2008. I have created a batch file that executes the dtexec.exe program on my local PC which in turn runs an SSIS Package stored on the network.

My goal is to enable one of my users to execute this batch file from their workstation as needed. My issues are these:

1) I don't want to have SQL server installed on their workstation. I don't know if there is a way for them to only have dtexec.exe without the rest of the sql server software.

2) I don't want to use the xp_cmdshell due to security risks that I've read about.

I would like to create a stored procedure that calls the DOS batch file. However, I do not know what the sql coding would be for the script. Any examples or suggestions would be greatly appreciated.

Thank you.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

MyDoggieJessie SSC-Forever Points: 44461 More actions · Answer 1

Give them an excel spreadsheet with a command button on it, when they press it it calls your routine. Very simple to do easy to lock down with a password, keeping the user out of the code. If you want, send me your code in I'll throw it toegether for you...

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 2

pelusodm (9/26/2011)

I would like to create a stored procedure that calls the DOS batch file. However, I do not know what the sql coding would be for the script. Any examples or suggestions would be greatly appreciated.

Thank you.

I'm not sure how you would do that from an sp without 'xp_cmdshell'.

You could create a CLR assembly that has an stored procedure that does that. I'm not sure if the EXTERNAL_ACCESS permission set would allow you to call external executables from the CLR code but maybe it's safer than 'xp_cmdshell'?

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

MyDoggieJessie SSC-Forever Points: 44461 More actions · Answer 3

The Excel file (VBA code) would execute an sp, but the sp itself would have to use xp_cmdshell (like you said). But since the connection to the SQL Server/DB is controlled at the code level, the user wouldn't really have access to that layer. All you'd really need is a proxy account that is able to execute xp_cmdshell

You could also use VBA to run the batch file instead and bypass using xp_cmdshell altogether.

I would reccomend keeping it as simple as possible...

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Give the user absolutely no privs except to call a stored procedure that calls a job with a command task in it.

Better yet... why not just have the user run the batch file directly instead of via proc?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Anonymous Mr or Mrs. 500 Points: 559 More actions · Answer 5

Anonymous

Mr or Mrs. 500

Points: 559

September 27, 2011 at 6:35 pm

#1387499

MyDogJessie - Thank you for your "out-of-the-box" Excel command button approach. I should have indicated in my post that I am a sql server newbie as I don't comprehend the details of your solution. Would you mind explaining your solution in more fundamental terms. Thank you so very much.

Anonymous Mr or Mrs. 500 Points: 559 More actions · Answer 6

Anonymous

Mr or Mrs. 500

Points: 559

September 27, 2011 at 6:38 pm

#1387500

Jeff,

As I mentioned to MyDoggieJessie, I am a SQL Server newbie and would appreciate a more fundamental response of your post.

Thank you!

Anonymous Mr or Mrs. 500 Points: 559 More actions · Answer 7

Anonymous

Mr or Mrs. 500

Points: 559

September 27, 2011 at 6:42 pm

#1387501

Andre,

Thanks for your response to my post. If you could describe your solution in simpler terms (I'm a SQL Server newbie), it would be much appreciated.

Thanks

MyDoggieJessie SSC-Forever Points: 44461 More actions · Answer 8

Send me what you have and I'll send it back. Executing the batch file directly is one option, executing it via a stored proc is another. Both can easily be done via excel and it gives the user the comfort of using a program they are most likely familiar with...

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 9

Just keep in mind that your client must have MS Office installed for that to work. 🙂

But that could work.

Now to the experts: Is a CLR stored procedure a bad solution in this case?

But Jeff's solution is probably the best one given the security reasons.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 10

pelusodm (9/27/2011)

Andre,

Thanks for your response to my post. If you could describe your solution in simpler terms (I'm a SQL Server newbie), it would be much appreciated.

Thanks

Hmm... no offense but if you're not understanding the replies of everyone here maybe you should have posted this in the "SQL Server - newbies" forum section. Just saying. Hope you find a solution to your problem. 🙂

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

MyDoggieJessie SSC-Forever Points: 44461 More actions · Answer 11

Again, we want to help...but you've got to provide what we request or it's difficult for anyone to help you out - I've asked for you to send me what you've got (a good place to start would be the batch file you want to run)...

Excel 2000, 2003, and 2007 will all work - the steps below are assuming you will use 2007 - If you want to use the Excel approach:

1. Open Excel, create new spreadsheet

2. If you don't see the Developer Ribbon, go to Excel Options, Popular, check "Show Developer tab in the Ribbon"

3. Click on Developer from the file menu, click on "Design Mode"

4. Click "Insert" and click on the ActiveX command button - use your mouse to create your button

It should now say "CommandButton" and be grey.

5. Double-click the button you just created and paste in the following code so it looks like this:

Private Sub cmdRunBatchFile_Click()

Dim ret As Integer

ret = Shell("C:\Temp\test\test.bat", 1)

End Sub

6. Replace "C:\Temp\test\test.bat" with the actual path and filename to your bat file.

7. Save as "Macro-Enabled Workbook"

TEST!

8. Unselect "Design Mode" from the Developer Ribbon, then click the button you created.

9. If you see command window, it worked.

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

MyDoggieJessie SSC-Forever Points: 44461 More actions · Answer 12

MyDoggieJessie

SSC-Forever

Points: 44461

September 28, 2011 at 10:44 am

#1387944

Should look something like the attached images...

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 13

pelusodm (9/26/2011)

I am running SQL Server 2008. I have created a batch file that executes the dtexec.exe program on my local PC which in turn runs an SSIS Package stored on the network.

My goal is to enable one of my users to execute this batch file from their workstation as needed. My issues are these:

1) I don't want to have SQL server installed on their workstation. I don't know if there is a way for them to only have dtexec.exe without the rest of the sql server software.

2) I don't want to use the xp_cmdshell due to security risks that I've read about.

I would like to create a stored procedure that calls the DOS batch file. However, I do not know what the sql coding would be for the script. Any examples or suggestions would be greatly appreciated.

Thank you.

pelusodm (9/27/2011)

Jeff,

As I mentioned to MyDoggieJessie, I am a SQL Server newbie and would appreciate a more fundamental response of your post.

Thank you!

My apologies... this one slipped throught the cracks.

Considering that you already have a batch file that works, I think that the "Command Button in Excel" method that MyDoggieJessie identified in the previous post would do the trick for you. The only difference might be WHERE the batch file is located... chances are (and it should be) located on a different machine than the end-user's machine. That shouldn't be a real problem, though... instead of using drive letter and path in MyDoggieJessie's example (in the bit maps attached with that post), you'd simply use an UNC path (\\machinename\sharename\path\batchfilename.bat). Of course, you'd need to make a "share" at the file system level.

You could also make a local (on the user's machine) batch file that would call the remote batch file using similar methods. That way you could build a shortcut icon on the user's desktop to do the job.

All of this is a bit of an advanced subject for newbies... do you have anyone in your shop that could help you directly?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions