Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using SQLCMD as an alternative to xp_cmdshell Expand / Collapse
Author
Message
Posted Monday, September 12, 2011 3:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 04, 2013 12:36 AM
Points: 36, 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
Post #1173815
Posted Tuesday, September 13, 2011 1:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 06, 2012 9:39 AM
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
---------------------------------------------------------------
Post #1173927
Posted Wednesday, September 18, 2013 2:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 26, 2013 8:59 AM
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
Post #1495804
Posted Wednesday, September 18, 2013 7:02 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128
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?
Post #1495910
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse