0

I'm looking to monitor various events that may occur within a SQL Server Maintenance Plan. My desired method for doing this is to have the maintenance plan do an HTTP POST with the data. After some research I think my preferred approach is to use cURL but I understand this requires enabling xp_cmdshell which is not ideal from a security standpoint.

So, I'm trying to put together a bit of T-SQL that will check if xp_cmdshell is enabled and if not enable it long enough to run the command and then disable it again. However, what I have below is not cooperating and I'm pretty sure it has something to do with all those GO's...

DECLARE @cmdstatus INT
SET @cmdstatus = (SELECT CONVERT(INT, ISNULL(value, value_in_use)) AS config_value FROM  sys.configurations WHERE  name = N'xp_cmdshell')
IF @cmdstatus = 1
EXEC xp_cmdshell 'curl --user user:password --data "task=test&status=success" https://www.example.ca/index.php';
GO
IF @cmdstatus = 0
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO
EXEC xp_cmdshell 'curl --user user:password --data "task=test&status=success" https://www.example.ca/index.php';
GO
EXEC sp_configure 'xp_cmdshell', 0;
GO
RECONFIGURE;
GO
EXEC sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

Thanks for any help.

Jason Cruickshank
  • 13
  • 3
  • 1
    A couple of problems - GO isn't T-SQL, it is a batch separator for client tools like Management Studio. So that isn't going to work as a job step. Also, what happens if two users cross paths when running this code? Without some kind of semaphore you will have race conditions trying to set that option. Finally, why does SQL Server have to post this information to a URL? Put the data in a queue table, then you can have anything externally process it. PowerShell, C#, VBScript, etc. can all take information and post it to a URL, and be scheduled to run every minute. Use the right tool for the job. – Aaron Bertrand Feb 12 '15 at 18:46
  • Thanks, it was just a matter of removing GO from everywhere to get this to work as expected. I'll have to give the potential for a race condition some thought, not sure how that can be dealt with. I don't expect that to be a concern generally as this is going into a scheduled maintenance plan but never say never. – Jason Cruickshank Feb 12 '15 at 19:48
  • 1
    Still don't understand why SQL Server needs to hit the URL. – Aaron Bertrand Feb 12 '15 at 19:49

0 Answers0