Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using SQLCMD as an alternative to xp_cmdshell


Using SQLCMD as an alternative to xp_cmdshell

Author
Message
John Reddy
John Reddy
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 212
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
ramakantshankar
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 42
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
srajinigandh
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 12
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
OTF
Old Hand
Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)

Group: General Forum Members
Points: 316 Visits: 4128
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search