August 27, 2007 at 8:09 pm
By default in SQL Server 2005 xp_cmdshell is disabled & to use it you have to enable it via the "Surface Area Configuration Tool".
Is there an alternative to xp_cmdshell or is it disabled by default due to it's known security issues?
I'm just wondering if there is a new feature in SQL 2005 that replaces xp_cmdshell.
August 28, 2007 at 12:49 am
Hi Trigger,
as far as I know there's know replacement for xp_cmdshell. The reason why it's disabled is just as you guessed the security issues around usig it. In case you only need to use it once in a while for a short time, I usually enable it at the beginning through sp_configure and disable it afterwards again.
But if you need it constantly, like when using Red-Gate SQLBackup you need to enable it constantly.
Markus
[font="Verdana"]Markus Bohse[/font]
August 28, 2007 at 2:08 am
As Markus mentioned, you can just enable xp_cmdshell using:
EXEC sp_configure 'xp_cmdshell', 1
However, I'm wondering why you are seeking a replacement. Anyway, in the broader sense there are alternatives to xp_cmdshell; these can be more powerful, but generally are also more difficult to set up. Alternatives include CLR stored procedures for example. Also, many things can be achieved via the Service Broker.
Regards,
Andras
August 28, 2007 at 4:09 pm
In SQL Server 2000, I had a Stored Procedure that used to send a NETSEND message using xp_cmdshell when I was about to restore a Database. The NESEND message is as below. However, if you have an alternative solution please elaborate!
CREATE PROCEDURE USP_KillUsers @DB VARCHAR(20)
AS
DECLARE @spid SMALLINT,
@status VARCHAR(32),
@loginame SYSNAME,
@hostname SYSNAME,
@blk CHAR(8),
@dbname SYSNAME,
@cmd VARCHAR(255),
@KILLSTATE VARCHAR(32),
@NOTIFYSTATE VARCHAR(255),
@NOTIFYMSG1 VARCHAR(255),
@NOTIFYMSG2 VARCHAR(255),
@NOTIFYMSG3 VARCHAR(255)
CREATE TABLE #usrkill
(
spid SMALLINT,
ecid INT,
status VARCHAR(32),
loginame SYSNAME,
hostname SYSNAME,
blk CHAR(8),
dbname SYSNAME NULL,
cmd VARCHAR(255)
)
INSERT INTO #usrkill EXEC sp_who /* INSERT INTO TEMP TABLE */
SELECT @NOTIFYMSG1 = 'NET SEND'
SELECT @NOTIFYMSG2 = @@servername + ' : YOUR CONNECTION TO DATABASE ' + UPPER(@DB) + ' WILL BE DISCONNECTD IN 10 SECONDS '
DECLARE CNOTIFYUSR SCROLL CURSOR FOR
SELECT DISTINCT HOSTNAME, loginame
FROM #usrkill
WHERE (dbname = @DB)
AND (hostname <> @@SERVERNAME)
OPEN CNOTIFYUSR
FETCH FIRST FROM CNOTIFYUSR INTO @hostname, @loginame
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@hostname IS NULL) or (@hostname = 'BigP3') or (@hostname = '')
SELECT @hostname = @loginame
SELECT @NOTIFYSTATE = @NOTIFYMSG1 + ' ' + RTRIM(@hostname) + ' "' + @NOTIFYMSG2 + '"'
EXEC master..xp_cmdshell @NOTIFYSTATE, no_output
FETCH NEXT FROM CNOTIFYUSR INTO @hostname, @loginame
END
CLOSE CNOTIFYUSR
DEALLOCATE CNOTIFYUSR
-- PAUSE SCRIPT FOR 10 SECONDS
WAITFOR DELAY '00:00:10'
DECLARE CUSRKILL SCROLL CURSOR FOR
SELECT SPID
FROM #usrkill
WHERE dbname = @DB
AND loginame NOT LIKE 'INAP\SQLService%'
OPEN Cusrkill
FETCH FIRST FROM CUSRKILL INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @KILLSTATE = 'KILL ' + CONVERT(CHAR, @spid)
PRINT @KILLSTATE
EXEC (@KILLSTATE)
FETCH NEXT FROM Cusrkill INTO @spid
END
CLOSE Cusrkill
DEALLOCATE Cusrkill
DROP TABLE #usrkill /* CLEAN UP TEMPDB */
GO
August 30, 2007 at 2:27 am
Trigger,
You could use database mail to send an email to users instead, maybe not as in intrusive as a netsend but you could send it in advance, you must be restoring non critical servers if you are taking this approach anyway?
SSIS? You should be able to write a SSIS package using the 'Notify Operator Task' to use net send to inform users then run a script to restore the database, not sure if this will require xp_cmdshell under the hood though.
Alerts? Just and idea but with a bit of investigation you may be able to set up a user-defined event and associate an alert to it that in turn netsends users, you could define the user-defined event to be fired when you change the database status for example (in preparation to restoring it), this is all a guess though haven't investigated it?
Or if you have more time you could delve into Notification Services?
ll
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply