Using SQLCMD as an alternative to xp_cmdshell

• John Reddy

  Ten Centuries

  Points: 1250

  More actions

  September 12, 2011 at 3:42 pm

  #247784

  Hi,

  I have a development question that i would like to ask the group.

  I am working on building a stored procedure that would do a simple add linked server operation but with a few security concerns built into it.

  The stored procedure will be given the name of the linked server and the linked server properties and the user id to use for connecting to the linked server.

  But the password for the user account is stored in another server which can be retrieved from executing an operating system command from the command prompt.

  I know that I can use the xp_cmdshell extended stored procedure from within my stored procedure to execute the same command and get the password.

  But, the catch is, our company standards restrict us from directly using the xp_cmdshell from within the queries.

  I know that i can use the SQLCMD to execute operating system commands by indicating the " !! " before the command.

  My question is how do i invoke the SQLCMD from within my stored procedure, execute the operating system command using the sqlcmd, get back the password, store it in a variable and then continue with adding the linked server.

  Ultimately, this stored procedure will be replacing a legacy script that the application has been using.

  Suggestion would be much appreciated.

  Thanks

  -

  John

• ramakantshankar

  SSC Enthusiast

  Points: 139

  More actions

  September 13, 2011 at 1:29 am

  #1380435

  Hi,

  As you said we can use the SQLCMD to execute operating system commands by indicating the " !! " before the command.

  I have created a POC on using OPerating System Commands in a Stored procedure.

  Following is the Code for the created Stored Procedure. In the Script for Procedure the Operating Systems commands are used:

  CREATE PROCEDURE SQLCMD_TEST

  AS

  !!MKDIR "C:\TEST"

  :OUT "C:\TEST\test.TXT"

  SELECT @@VERSION AS 'SERVER VERSION'

  !!DIR

  GO

  SELECT @@SERVERNAME AS 'SERVER NAME'

  GO

  I am able to successfully execute the procedure:EXEC SQLCMD_TEST

  Also if required you can keep the Execute procedure in a SQL script and pass the Script location to the SQLCMD syntax as INPUT file.

  Please revert back if this works for you.

  Thanks and Regards,
  Ramakant
  ---------------------------------------------------------------
  Ramakant Shankar
  InfoCepts | www.infocepts.com
  Off: +91 712 224 5867 Ext 8388, +1 301 560 2591 Ext 8388
  ---------------------------------------------------------------

• srajinigandh

  Mr or Mrs. 500

  Points: 571

  More actions

  September 18, 2013 at 2:20 am

  #1651022

  ramakantshankar (9/13/2011)

  ---

  Hi,

  As you said we can use the SQLCMD to execute operating system commands by indicating the " !! " before the command.

  I have created a POC on using OPerating System Commands in a Stored procedure.

  Following is the Code for the created Stored Procedure. In the Script for Procedure the Operating Systems commands are used:

  CREATE PROCEDURE SQLCMD_TEST

  AS

  !!MKDIR "C:\TEST"

  :OUT "C:\TEST\test.TXT"

  SELECT @@VERSION AS 'SERVER VERSION'

  !!DIR

  GO

  SELECT @@SERVERNAME AS 'SERVER NAME'

  GO

  I am able to successfully execute the procedure:EXEC SQLCMD_TEST

  Also if required you can keep the Execute procedure in a SQL script and pass the Script location to the SQLCMD syntax as INPUT file.

  Please revert back if this works for you.

  Hi ramakantshankar ,

  I understand this script was working for you and its some time back , just wondering which version did you try this and its notworking in Sql server 2012.Any ideas please ?

  Error is 'Incorrect syntax near !'

  Regards

  Raj

• OTF

  SSCertifiable

  Points: 6104

  More actions

  September 18, 2013 at 7:02 am

  #1651107

  srajinigandh (9/18/2013)

  ---

  ramakantshankar (9/13/2011)

  ---

  Hi,

  As you said we can use the SQLCMD to execute operating system commands by indicating the " !! " before the command.

  I have created a POC on using OPerating System Commands in a Stored procedure.

  Following is the Code for the created Stored Procedure. In the Script for Procedure the Operating Systems commands are used:

  CREATE PROCEDURE SQLCMD_TEST

  AS

  !!MKDIR "C:\TEST"

  :OUT "C:\TEST\test.TXT"

  SELECT @@VERSION AS 'SERVER VERSION'

  !!DIR

  GO

  SELECT @@SERVERNAME AS 'SERVER NAME'

  GO

  I am able to successfully execute the procedure:EXEC SQLCMD_TEST

  Also if required you can keep the Execute procedure in a SQL script and pass the Script location to the SQLCMD syntax as INPUT file.

  Please revert back if this works for you.

  Hi ramakantshankar ,

  I understand this script was working for you and its some time back , just wondering which version did you try this and its notworking in Sql server 2012.Any ideas please ?

  Error is 'Incorrect syntax near !'

  Regards

  Raj

  Assuming you are running the code in Sql Server Management Studio, the error you are getting is probably because the editor is not in Sql Cmd Mode.

  In order to do this, you will have to select "Sql Cmd Mode" from the "Query" Menu Items.

  Now that being said, encapsulating those statements within a Stored Procedure is a new one on me.

  I'm not so sure that is valid.

  How will that Procedure run without directly invoking SqlCmd?