Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

Execute BAT file from SQL Server

Aug 15 2011 9:16PM by Robert Dennyson   

You can use xp_cmdshell to call a bat file from SQL Server, below are the scripts for that

-- Calling batch file from SQL Server, this is batch file without parameter

EXEC MASTER..XP_CMDSHELL 'C:\SAMPLEBATFILE.BAT'
Read More..   [0 clicks]

Published under: SQL Server Tips ·  ·  ·  · 


Robert Dennyson
11 · 14% · 4420
10
 
6
 
 
0
Incorrect
 
0
Interesting
 
0
Forgotten



Submit

16  Comments  

  • This is a hotly contested subject but, no matter which side of the wall one speaks from, virtually no DBA will allow for such a thing unless one of the few methods to secure the use within Stored Procedures so that the user does not need or have the ability to execute xp_ComdShell directly is also deployed.

    commented on Aug 16 2011 12:23AM
    Jeff Moden
    159 · 1% · 305
  • Not only batch files , but we can invoke executable files as well.

    commented on Aug 16 2011 12:34AM
    Paresh Prajapati
    6 · 23% · 7464
  • Not only batch files, you can executed all the command prompt related windows commands. It spawns a Windows command shell and passes in a string for execution.

    Like: EXEC xp_cmdshell 'dir *.exe'; GO

    you can also fire 'Format C:'

    commented on Aug 16 2011 2:12AM
    Hardik Doshi
    20 · 9% · 2864
  • There are a couple of things to be aware of:

    xpcmdshell is disabled by default. This is by design as xpcmdshell can be a huge security hole. It was left in the product in case there was absolutely no other way to do what the programmer needed to do and no not break existing software. It should only be used when there is no other way to accomplish a task.

    If run by a member of the sysadmin role, xpcmdshell executes under the context of the account under which the SQL Server process runs. When xpcmdshell is called by a user that is not a member of the sysadmin role, xpcmdshell runs under the context of the proxy account set up by using spxpcmdshellproxyaccount. If a proxy account is not set up, xpcmdshell will fail.

    A much easier (and safer) way to run a batch file from SQL Server is to set up a SQL Server Agent job that calls the batch file, then run the job using spstartjob. This has the following benefits:

    • The batch file can be run under any security context set up as a SQL Server Agent Proxy.
    • The output can be logged to a table (msdb.dbo.sysjobstepslog) with no additional code.
    • The output can be logged to the Windows Application Event Log with no additional code.

    I can't seem to get the editor to allow me to post code without it completely munging the format. If you'd like the code to set up a SQL Server Agent job to run a batch file, send me an email.

    commented on Aug 16 2011 7:42AM
    Marc Jellinek
    95 · 2% · 586
  • It should only be used when there is no other way to accomplish a task.

    I disagree... quite strongly, in fact. There are several very safe methods to deploy xpCmdShel- runs directly from T-SQL which negate the need for complexities such as SQL Server Agent, SSIS, and other diversions. It also allows for some incredible additions to T-SQL through the use of such tools as PowerShell and rivals the flexibility offered by CLR's. As is true with CLR usage or any other tool including the use of SQL Server Agent (which CAN be just as deadly as if using xpCmdShell directly), you need to know the right way to do it.

    commented on Aug 16 2011 12:15PM
    Jeff Moden
    159 · 1% · 305
  • @Jeff Moden:

    Can you provide pointers to the safe methods to deploy xp_cmdshell?

    commented on Aug 16 2011 9:10PM
    Marc Jellinek
    95 · 2% · 586
  • Recently we have used the XPcmdshell and also we have used below statements to check file

    [code] DECLARE @NSFileName varchar(255) varchar(255) DECLARE @NSFile_Exists INT INT

    EXEC master.dbo.xpfileexist @NSFileName, @NSFileExists OUT [/code]

    To activate XP_cmdShell we can use the below statement

    [code] use master go spconfigure 'show advanced options', 1 go reconfigure with override go spconfigure 'xpcmdshell',1 go reconfigure with override go spconfigure 'show advanced options', 0 go reconfigure with override go [/code]

    commented on Aug 17 2011 5:01AM
    Manas Ranjan Dash
    39 · 5% · 1484
  • @simplymanas

    Once you've activated xp__cmdshell, how do you prevent someone from submitting the following query:

    EXEC xp__cmdshell 'net stop MSSQLSERVER'

    commented on Aug 17 2011 5:34AM
    Marc Jellinek
    95 · 2% · 586
  • Once you've activated xp__cmdshell, how do you prevent someone from submitting the following query:

    EXEC xp__cmdshell 'net stop MSSQLSERVER'

    @Mark,

    THAT is the whole problem with the common methods that people use out of ignorance of the correct way to use xp_CmdShell. Outside of my place of work, I only know of one other person in the entire world who actually uses xp_CmdShell correctly and I know hundreds of DBA's in the world.

    There are 3 ways to safely execute xp_CmdShell from T-SQL. I'm actually writing an article for SQLServerCentral.com on the subject so I don't want to short circuit that, too much. The bottom line on each of thse 3 methods is that the user/login calling the stored procedure CANNOT RUN XP_CMDSHELL DIRECTLY!

    However, one method involves the use of certificates and seems a bit complicated (especially to maintain during modifications to the code) to me because the certificate must be instantiated in Master, copied to a file, loadied into the target DB, instantiated there, and then deleted from the file (or put in a really safe place to keep people from getting their hands on it). Like I said, this method seems a bit complicated to me.

    The second method is to create the stored procedure in the Master DB and simple give people privs to EXECUTE it. I don't like that method for obvious reasons (user code in Master DB) but it is effective for "common" code such as creating a directory listing.

    The third method is super simple and is available as of SQL Server 2005. It only requires two things... it first requires that the stored procedure contain a "WITH EXECUTE AS OWNER" and then a user login with "SA" privs must be the one to promote it to production. Obviously, that "OWNER" login shouldn't be an individual because people come and go. Equally as obvious, we don't allow developers to promote their own code. In fact, most of them can't even see the production server. If they could, THAT would be a serious violation of security. ;-)

    Of course, you must set up the proxy for xp_CmdShell that you normally need for all 3 methods but none of that is difficult and is only required once per server.

    Oddly enough, if you're not using the third method for all stored procedures and your users have anything other than "PUBLIC" privs and EXECUTE on the necessary stored procs, your security model could probably stand a review whether or not you're using xp_CmdShell or not. ;-)

    As a side bar, I practice what I preach ;-)... I know the third method works really well and is super simple to use because that's what the System DBA and I use at work and it has greatly streamlined our ETL code because we don't need to jump back and forth between some pretty intense processing code and things like SSIS just to export some data.

    commented on Aug 18 2011 10:28AM
    Jeff Moden
    159 · 1% · 305
  • @Jeff: Fair enough. I've used option 3 (denying a user direct access to xp_cmdshell, wrapping it within a stored proc marked with EXECUTE AS) but only when there is no other way.

    You mentioned "... just to export some data..."

    If all you are trying to do is export data (and you don't want to use SSIS), why not use BCP? This can be run from any job scheduler and you wouldn't need xp_cmdshell to trigger the script.

    Something as simple as this will get you what you want (this produces a list of databases deployed on an instance). Put this into a batch file then call it from whatever you are using for job scheduling.

    To output a text file (comma field delimiter, carriage return row delimiter)

    bcp "select name from master.sys.databases" queryout {output file} -c -S {SERVER[\INSTANCE]} -T
    

    To output an attribute-centric XML file:

    bcp "select name from master.sys.databases FOR XML AUTO" queryout {output file} -c -S {SERVER[\INSTANCE]} -T
    
    commented on Aug 19 2011 6:35AM
    Marc Jellinek
    95 · 2% · 586
  • @mark and @jeff thank you so much for these useful points. When I checked the same with the DBA over here they said that it will be a privilage to admin only and they will not provide the uses of xp_cmdshell to normal users. Even while using it in a SSIS package, they will run that under admin user.

    But thank you so much for letting us know about so many options available.

    commented on Aug 19 2011 9:26AM
    Manas Ranjan Dash
    39 · 5% · 1484
  • Manas, if you want to run OS task within SSIS, you have 'Execute System Task' Component, no need to go for xp_cmdshell.

    In addition, to Jeff's and Mark's discussion, let me add one more point. In normal environment you probably have SQL Server configured to under domain account. If this account has access or by any chance it has elevated rights to other machines in the network you can use AT to schedule some actions on other machine and wreak havoc on more servers.

    It's really dangerous option and you have to know what you're doing when you enable xp_cmdshell.

    commented on Aug 27 2011 12:08PM
    Szymon Wojcik
    67 · 3% · 877
  • If all you are trying to do is export data (and you don't want to use SSIS), why not use BCP? This can be run from any job scheduler and you wouldn't need xp_cmdshell to trigger the script.

    @Marc,

    While it's true that you can run BCP from any job scheduler, since using xp_CmdShell can easily be made to run securely, I don't bother with the extra "hoop" to jump through.

    commented on Aug 27 2011 3:52PM
    Jeff Moden
    159 · 1% · 305
  • When I checked the same with the DBA over here they said that it will be a privilage to admin only and they will not provide the uses of xp_cmdshell to normal users. Even while using it in a SSIS package, they will run that under admin user.

    @Manas,

    While it's true that something needs to run xpCmdShell as SA, it does NOT have to be individual uses. xpCmdShell can be executed just as securely as any SSIS package. Ya just gotta know how to do it. Granting "normal" users privs to run xp_CmdShell isn't one of them. Your DBA just needs to do a little more research.

    commented on Aug 27 2011 3:58PM
    Jeff Moden
    159 · 1% · 305
  • It's really dangerous option and you have to know what you're doing when you enable xp_cmdshell.

    @Szymon,

    Actually, you need to know what your doing when you use anything especially when it comes to security and a whole lot of people fall really short there. Quick... raise your hand! How many of you have granted individual "normal" users, logins, or roles the dbDataReader or {gasp!} dbDataWriter privs? What's to keep those folks from wiping out a column in a table with a simple update?

    Nothing!

    There is absolutely no reason for any users, other than the DBA's to even have read privs on a production database. If you know what you're doing, everything will be done through stored procedures and the users (including GUI and Web logins) will have only EXECUTE privs on those. They won't even be able to see them. Coincidently, setting your server up in such a secure method is also exactly what is needed to safely run xp_CmdShell from a proc. Properly locking down a database in such a fashion will also greatly reduce or even eliminate hack attacks. Just imagine... someone screws up and makes a login stored procedure that's subject to SQL Injection. Because of that, an attacker makes his/her way in... as a user with absolutely NO PRIVS!!! What's that attacker going to be able to do to your system?

    Nothing!

    Proper use of xp_CmdShell isn't the problem. Poor security of most systems is. ;-)

    commented on Aug 27 2011 4:12PM
    Jeff Moden
    159 · 1% · 305
  • Blessed to be in such a forum. Learning has been intresting these days. Thanks all.

    commented on Aug 27 2011 9:48PM
    Manas Ranjan Dash
    39 · 5% · 1484

Your Comment


Sign Up or Login to post a comment.

"Execute BAT file from SQL Server " rated 5 out of 5 by 10 readers
Execute BAT file from SQL Server , 5.0 out of 5 based on 10 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]