locked
Securing extended procedures RRS feed

  • Question

  • Hi,

    I’m trying to secure my SQL Server 2005 infrastructure, and I’m seeing that some sites are recommending that certain extended procedures be restricted to sysadmin only.

     

    http://www.sqlsecurity.com/FAQs/SQLSecurityChecklist/tabid/57/Default.aspx
    This site recommended securing the following extended procedures:

    Extended Procedurs:
    sp_sdidebug      xp_availablemedia    xp_cmdshell
    xp_deletemail    xp_dirtree           xp_dropwebtask
    xp_dsninfo       xp_enumdsn           xp_enumerrorlogs
    xp_enumgroups    xp_enumqueuedtasks   xp_eventlog
    xp_findnextmsg   xp_fixeddrives       xp_getfiledetails
    xp_getnetname    xp_grantlogin        xp_logevent
    xp_loginconfig   xp_logininfo         xp_makewebtask
    xp_msver         xp_perfend           xp_perfmonitor
    xp_perfsample    xp_perfstart         xp_readerrorlog
    xp_readmail      xp_regread           xp_revokelogin
    xp_runweb  

     

     


    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3184075&SiteID=1
    This thread recommended (implicitly) securing the following extended procedures:

    Extended Procedures:
    sp_OACreate        sp_OADestroy       sp_OAGetErrorInfo   sp_OAGetProperty
    sp_OAMethod        sp_OASetProperty   sp_OAStop           sp_sdidebug
    xp_availablemedia  xp_cmdshell        xp_deletemail       xp_dirtree
    xp_dropwebtask     xp_dsninfo         xp_enumdsn          xp_enumerrorlogs
    xp_enumgroups      xp_enumqueuedtasks xp_eventlog         xp_findnextmsg
    xp_fixeddrives     xp_getfiledetails  xp_getnetname       xp_grantlogin
    xp_logevent        xp_loginconfig     xp_logininfo        xp_regread
    xp_perfend         xp_perfmonitor     xp_perfsample       xp_perfstart
    xp_readerrorlog    xp_readmail        xp_revokelogin      xp_runwebtask
    xp_schedulersignal xp_sendmail        xp_servicecontrol   xp_snmp_getstate
    xp_snmp_raisetrap  xp_sprintf         xp_sqlinventory     xp_sqlregister
    xp_sqltrace        xp_sscanf          xp_startmail        xp_stopmail
    xp_subdirs         xp_unc_to_drive    xp_dirtree 

     


    Looking at these lists, I can see they might have missed other extended procedures like xp_regwrite, xp_regdeletekey, and xp_regdeletevalue.

     

    My questions are: Is there any way I can find an exhaustive list as to what extended procedures should be restricted?  Is there a website/Microsoft resource that can help me identify what to restrict?
     
    Any other information you can point me to to secure our infrastructure would be appreciated.

     

    Wednesday, May 7, 2008 4:31 PM

Answers

All replies

  • In SQL Server 2005 we turned the majority of these XPs "off by default" and gave adminstrative control to the dba via the

     

    SQL Server Surface area Configuration tool. 

     

    check http://msdn.microsoft.com/en-us/library/ms161956.aspx for more details.

     

    HTH,

    -Steven Gott

    SDE/T

    SQL Server

    Wednesday, May 7, 2008 5:40 PM
  • Steven,

     

    Thanks for replying, but I'm not certain if the SAC tool goes beyond the xp_cmdshell, the mail xp's, and replication xp's.  I did find that the sp_OA* are controlled by the SAC by way of the OLE Automation.  However, it doesn't appear to give the capability of configuring most of the extended procedures I had listed in my opening thread.

     

    Furthermore, I'm trying to determine if the some of the security websites I've been visiting (where I got the list of extended procedures) are really identifying security issues above and beyond the SAC tool.  Just looking at the one MSDN thread/hyperlink, it seems to believe that there are issues with the listed extended procedures.  With these other extended procedures, are we living in a glass house and don't know it?

     

    Wednesday, May 7, 2008 8:40 PM
  •   As Steven already mentioned, by default the level of permissions required to execute these XPs should be very high (depending on the XP, but in many cases requiring sysadmin).

     

      Most of the XPs are defined as part of components and unless at least one of the components that reference any given XP is enabled in SAC the XP will not execute; for example "xp_deletemail", if "SQL Mail" component is not enabled, it should fail with an error similar to this one:

    Msg 15281, Level 16, State 1, Procedure xp_deletemail, Line 1

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

     

     It is important to remark that a lot of these XPs are used by SMO & DMO, and they are enabled by default.

     

      It is also important to remark that all the XPs that may be dangerous should by default be limited to be executed by a privileged user (i.e. sysadmin) or they should have internal checks to make sure the caller is authorized. If you have concerns about any specific XP, please let us know.

     

     Thanks,

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Thursday, May 8, 2008 8:13 PM
  • I understand what you're saying, but in the following MSDN thread:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3184075&SiteID=1

     

    There is some code:

    SQL

    use master

    go

    select sysusers.name, sysobjects.name from sysobjects, sysusers, sysprotects where

    sysobjects.id=sysprotects.id and sysprotects.uid = sysusers.uid and sysprotects.protecttype=205 and

    (sysobjects.name =  'sp_OACreate'OR sysobjects.name =  'sp_OADestroy' OR sysobjects.name =  'sp_OAGetErrorInfo' OR

    sysobjects.name =  'sp_OAGetProperty' OR sysobjects.name =  'sp_OAMethod' OR sysobjects.name =  'sp_OASetProperty' OR

    sysobjects.name =  'sp_OAStop' OR sysobjects.name =  'sp_sdidebug' OR sysobjects.name =  'xp_availablemedia' OR

    sysobjects.name =  'xp_cmdshell' OR sysobjects.name =  'xp_deletemail' OR sysobjects.name =  'xp_dirtree' OR

    sysobjects.name =  'xp_dropwebtask' OR sysobjects.name =  'xp_dsninfo' OR sysobjects.name =  'xp_enumdsn' OR

    sysobjects.name =  'xp_enumerrorlogs' OR sysobjects.name =  'xp_enumgroups' OR sysobjects.name =  'xp_enumqueuedtasks' OR

    sysobjects.name =  'xp_eventlog' OR sysobjects.name =  'xp_findnextmsg' OR sysobjects.name =  'xp_fixeddrives' OR

    sysobjects.name =  'xp_getfiledetails' OR sysobjects.name =  'xp_getnetname' OR sysobjects.name =  'xp_grantlogin' OR

    sysobjects.name =  'xp_logevent' OR sysobjects.name =  'xp_loginconfig' OR sysobjects.name =  'xp_logininfo' OR

    sysobjects.name =  'xp_regread' OR sysobjects.name =  'xp_perfend' OR sysobjects.name =  'xp_perfmonitor' OR

    sysobjects.name =  'xp_perfsample' OR sysobjects.name =  'xp_perfstart' OR sysobjects.name =  'xp_readerrorlog' OR

    sysobjects.name =  'xp_readmail' OR sysobjects.name =  'xp_revokelogin' OR sysobjects.name =  'xp_runwebtask' OR

    sysobjects.name =  'xp_schedulersignal' OR sysobjects.name =  'xp_sendmail' OR sysobjects.name =  'xp_servicecontrol' OR

    sysobjects.name =  'xp_snmp_getstate' OR sysobjects.name =  'xp_snmp_raisetrap' OR sysobjects.name =  'xp_sprintf' OR

    sysobjects.name =  'xp_sqlinventory' OR sysobjects.name =  'xp_sqlregister' OR sysobjects.name =  'xp_sqltrace' OR

    sysobjects.name =  'xp_sscanf' OR sysobjects.name =  'xp_startmail' OR sysobjects.name =  'xp_stopmail' OR

    sysobjects.name =  'xp_subdirs' OR sysobjects.name =  'xp_unc_to_drive' OR sysobjects.name =  'xp_dirtree'

    )

    go

     

     

    This code will shows the following in a fresh installation (no DB's added and disabling of services, XP, OLE, etc. via SAC and Config Mgr):

    Results:

    public xp_getnetname
    public xp_dirtree
    public xp_fixeddrives
    public xp_sscanf
    public xp_revokelogin
    public xp_grantlogin
    public xp_sprintf
    public xp_regread

     

     

    Question: Shouldn't these be restricted and not available to public?  Isn't this in itself a security issue?

    My take is that these xp shouldn't be granted to public.

     

    Logging in on an account that just has log in rights & public, I can run xp_getnetname, but xp_dirtree and xp_fixeddrives bring back zeros.  I haven't had a chance to run other test. 

     

    Question: If I revoke these XP from public, will this cause any issues?

    SOX auditors can be real bas%@&#, and they see these threads and will need answers.

     

    Thursday, May 8, 2008 9:03 PM
  • Raul,

    Had you had a chance to look at my reply to yours?

    gpl :-)

     

    Monday, May 12, 2008 7:23 PM