none
SQL Server blocked access

    Question

  • We have an SBS 2003 SP1 installation with SQL 2005, ISA & EXCHANGE. Every 15 minutes we get the following informational message to event log file:

     

    SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

     

    Why we get this message ? What is happening to SQL 2005 and we get this message ? How can ww stop it ? Is it safe to stop it ?

     

    W\e appriciate any help. Thanks.

     

    Friday, September 12, 2008 2:41 PM

Answers

  • Something like this should do it:

     

    Code Snippet

    DECLARE @db AS NVARCHAR(128)

    DECLARE @SQL AS NVARCHAR(MAX)

     

    SELECT name

    INTO #databases

    FROM sys.databases

    WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')

     

    WHILE EXISTS (SELECT * FROM #databases)

    BEGIN

    SELECT TOP 1

      @db = name

    FROM #databases

     

    SET @SQL = 'SELECT '

    SET @SQL = @SQL + 'OBJECT_NAME(id) AS Objects_On_' + @db

    SET @SQL = @SQL + ' FROM ' + @db + '.sys.syscomments'

    SET @SQL = @SQL + ' WHERE text LIKE ''%xp_cmdshell%'''

     

    EXEC (@SQL)

     

    DELETE

    FROM #databases

    WHERE name = @db

    END

     

    DROP TABLE #databases

     

     

    Friday, September 19, 2008 2:45 PM

All replies

  •  

    I think one of schedule job or some store procedure is calling for xp_cmdshell.

    Check which job or sp is calling for it,

    or else enable the xp_cmdshell using

    Code Snippet

     

    sp_configure 'show advanced options',1

    GO

    RECONFIGURE

    GO

    sp_configure xp_cmdshell,1

    GO

    RECONFIGURE

     

     

    • Proposed as answer by Sharjeel Ahsan Sunday, August 05, 2012 11:10 PM
    Friday, September 12, 2008 2:49 PM
    Moderator
  •  Are you having any Sql Server jobs in which you are using xp_cmdshell ? ? By default its turned off, to enable it you need to:

    use master

    go
    sp_configure 'show advanced options',1
    reconfigure with override
    go
    sp_configure 'xp_cmdshell', 1
    reconfigure with override
    go

    Refer this for more info, http://msdn.microsoft.com/en-us/library/ms190693(SQL.90).aspx

    - Deepak
    Friday, September 12, 2008 2:55 PM
  • We have a daily backup as a schedule job, that runs smoothly. How can I find out which store procedure might call xp_cmdshell ?

     

    I do not want to enable it because I do not know what will happen...!

     

     

    Friday, September 12, 2008 3:10 PM
  •  

    We have a daily backup as a schedule job, that runs smoothly. I do not want to enable it because I do not know what will happen...!

     

    Friday, September 12, 2008 3:11 PM
  • Enabling xp_cmdshell makes no harm to your server.

    Look into the error logs to see which job is failing because of it

     

    Friday, September 12, 2008 3:15 PM
    Moderator
  • I guess xp_cmdshell is disabled for a reason. Security issue...

    So, instead of enabling it, you should find out where it's being used.

     

    Try something like this:

    Code Snippet

    SELECT J.name

    FROM msdb.dbo.sysjobs AS J

    INNER JOIN msdb.dbo.sysjobsteps AS JS ON J.job_id = JS.job_id

    WHERE JS.command LIKE '%xp_cmdshell%'

    Friday, September 19, 2008 7:16 AM
  • Hi tican,

     

    It's not a job in msdb. Probably the xp_cmdshell is somewhere in a user procedure. Do you know a qucik way (like the script you sent me) to check all procedures to all databases ?

    Friday, September 19, 2008 10:46 AM
  • Something like this should do it:

     

    Code Snippet

    DECLARE @db AS NVARCHAR(128)

    DECLARE @SQL AS NVARCHAR(MAX)

     

    SELECT name

    INTO #databases

    FROM sys.databases

    WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')

     

    WHILE EXISTS (SELECT * FROM #databases)

    BEGIN

    SELECT TOP 1

      @db = name

    FROM #databases

     

    SET @SQL = 'SELECT '

    SET @SQL = @SQL + 'OBJECT_NAME(id) AS Objects_On_' + @db

    SET @SQL = @SQL + ' FROM ' + @db + '.sys.syscomments'

    SET @SQL = @SQL + ' WHERE text LIKE ''%xp_cmdshell%'''

     

    EXEC (@SQL)

     

    DELETE

    FROM #databases

    WHERE name = @db

    END

     

    DROP TABLE #databases

     

     

    Friday, September 19, 2008 2:45 PM
  •   I just wanted to add a couple of notes regarding the code shared by Tican on the previous post.

     

       The first one is that this code (or any code similar to it) won’t cover modules that are obfuscated using the “WITH ENCRYPTION” option. The reason for this is that the module is obfuscated and the module code is not directly available in the catalog views.  For more information see CREATE PROCEDURE in BOL.

     

       The code is using object_name builtin, but it is sensitive to the context (to be more specific, to the current database) and it may return incorrect results if used across databases, I would recommend printing the id and the DB name. Finally, the original code is subject to SQL injection (based on the DB name); I would strongly recommend to change the code to properly escape any data (i.e. use quotename(@db)) and to not concatenate the db name with other text.

     

      I hope this helps.

     

       -Raul Garcia

        SDE/T

        SQL Server Engine

     

    Wednesday, September 24, 2008 4:37 AM
    Moderator