locked
SQL Server Security Checklist DG0099 RRS feed

  • Question

  • I am working on a database project and need to secure the database according to the Microsoft SQL Server Database Security Checkist. I ran an SRR scan on the database and am going through the results of the scan. One of the findings is DG0099, which tells me I need to limit DBMS access to external local executables.

    I ran the SQL Script that accompanied the finding and it returned a list of around 50 different built in stored procs, which the Public account has access to. The problem is that I don't know what all of these SP's do and how denying the Public account access to them will affect on of our applications that uses our SQL Server for backend storage and processing.

    Some examples of the stored procs returned are :

    sp_AddFunctionalUntiToComponent, sp_batch_params, sp_bindsession, sp_executesql, sp_reserve_http_namespace, and so on.

    Can someone point me to a list of all of the built in SQL Server 2005 stored procs with a description of what they do?

    Thanks,

    Kris

    Monday, August 16, 2010 8:00 AM

Answers

All replies

  • BOL... Search for sp_*
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, August 16, 2010 8:09 AM
  • Have a look on the System Stored Procedures (Transact-SQL)
    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by Tom Li - MSFT Sunday, August 22, 2010 11:56 AM
    Monday, August 16, 2010 8:44 AM
  • Thanks Melton.

    The project I am working on entails a 3rd party application that uses SQL Server 2005 for its back end. I am trying to get a list of built in executables and stored procs that the application might use from the manufacturer and go from there.

    I like your idea of using roles, though. Fast and easy way to grant user permissions to the stored procs and executables.

    Tuesday, August 17, 2010 11:27 AM
  • This is a tad late on a reply, but I was just fighting through this. The verbiage in the check is very circular and took awhile to understand. I'll post the exact text since the STIG is publicly available outside of the PKI environment.

    IASE SQL Server STIG website

    Simply stated resolution: A role should NOT be applied to the extended SPs in question unless an application account requires access. Or a developer account exists in the production environment. If accounts and groups are controlled, there shouldn't be any unauthorized active accounts with dbo privileges. Examining the permissions on any/all of the returned XPs shows that no one is granted access of any kind outside of an sa/dbo (the returned XPs have NULL for privilege assignment).

    Reasoning: By applying a permission to a locked out (except by a sa/dbo account) extended stored procedure the principle of least privileges is violated. Remediation by this method actually CREATES a vulnerability by inventing an access path and applying a privilege where none was required.

    Vulnerability Discussion:
    DBMS’s may spawn additional external processes to execute procedures that are defined in the
    DBMS, but stored in external host files (external procedures). The spawned process used to
    execute the external procedure may operate within a different OS security context than the DBMS
    and provide unauthorized access to the host system.

    This states only that access to these procedures allows access to the external OS outside of the permissions granted to the user within the database. Getting out of the sandbox = bad.

    Documentable Explanation:
    Review the Database and Object Name as generated. An example entry is in the form: master
    sp_batch_params This is interpreted as: The external procedure named SP_BATCH_PARAMS is
    active in the MASTER database. These external procedures are active, but not currently assigned
    permissions to any database accounts (external procedures assigned to accounts are listed in
    Check DG0120). Confirm the external procedures are documented, authorized and accept as
    Documentable in VMS.

    If you don't have a need to mess with these and provide access because of an application, you don't have to. Just document that they are SQL Server defaults in the Information System Security Policy document & VMS, and you're covered.

    Fixes:
    Restrict access of extended stored procedures to SYSADMINs and application owner accounts
    where authorized for use.

    This is an option ONLY if there is a need to access these by a specific user. That's the entire point of this STIG. It goes on to state that if a user is allowed to access some of them, then the ones that they shouldn't access need to be specifically REVOKEd.

    Further in this comes into play with a poorly written application, where a developer codes everything with sa/dbo account permissions and those settings make it to the production environment and the account exists in both environments. Or the developer is coding on the production server with a dbo/sa account (scary!). Either way, an account has access to those extended SPs and it isn't identified or documented because it resides in the dbo group. If there's no auditing on the server either, then it goes on undetected. Add on a SQL inject vulnerability in an application, and it's not good.

    Fixes:
    By default, the public role is granted execute access to many system-supplied extended stored
    procedures. It is recommended these execute privileges to extended stored procedures (the ones
    being retained for system use) be transferred from the public role and re-assigned to a custom alluser
    group.

    In the above last full paragraph, the fix discusses when a publicly available extended stored procedure (not these, as they returned NULL for assigned privileges) is required. This is where the permissions should change. These public EXECUTE privies are returned in DG0120 and can be identified (and have a GRANT/REVOKE statement generated) with the following code:

    USE [master]
    select 'GRANT EXECUTE ON sys.' + o.name + ' TO PUBLIC'
    FROM master.sys.all_objects o 
    JOIN master.sys.database_permissions p ON p.major_id = o.object_id 
    JOIN master.sys.database_principals u ON p.grantee_principal_id = u.principal_id
    WHERE o.type = 'X' 
    ORDER BY o.name, u.name
    
    USE [master]
    select 'REVOKE EXECUTE ON sys.' + o.name + ' TO PUBLIC'
    FROM master.sys.all_objects o 
    JOIN master.sys.database_permissions p ON p.major_id = o.object_id 
    JOIN master.sys.database_principals u ON p.grantee_principal_id = u.principal_id
    WHERE o.type = 'X' 
    ORDER BY o.name, u.name

    Here's how I generated the trace for this, because the developer of the software I don't know what the developer standards are.

    -- Create the Queue
    declare @rc int
    declare @TraceID int
    declare @ maxfilesize bigint
    set @maxfilesize = 10
    -- Set the trace options including file location
    exec @rc = sp_trace_create @TraceID output, 0, N'LocateMe', @maxfilesize, NULL
    if (@rc != 0 ) goto error
    -- Setting execute events to capture
    declare @on bit 
    set @on = 1 
    exec sp_trace_setevent @TraceID, 42, 1, @on -- 
    exec sp_trace_setevent @TraceID, 42, 10, @on -- 
    exec sp_trace_setevent @TraceID, 42, 22, @on -- 
    exec sp_trace_setevent @TraceID, 42, 26, @on -- 
    exec sp_trace_setevent @TraceID, 42, 34, @on -- 
    exec sp_trace_setevent @TraceID, 42, 3, @on -- 
    exec sp_trace_setevent @TraceID, 42, 11, @on -- 
    exec sp_trace_setevent @TraceID, 42, 27, @on -- 
    exec sp_trace_setevent @TraceID, 42, 35, @on -- 
    exec sp_trace_setevent @TraceID, 42, 12, @on -- 
    exec sp_trace_setevent @TraceID, 42, 28, @on -- 
    -- Set the filters for only the sp/xp returned as NULL permissions
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_AddFunctionalUnitToComponent'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_batch_params'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_bindsession'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_change_tracking_waitforchanges'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_control_dbmasterkey_password'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_createorphan'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_cursor'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_cursorclose'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_cursorexecute'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_cursorfetch'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_cursoropen'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_cursoroption'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_cursorprepare'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_cursorprepexec'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_cursorunprepare'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_delete_http_namespace_reservation'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_droporphans'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_enable_sql_debug' 
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_execute'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_executesql'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_fetchLOBfromcookie'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_fulltext_getdata' 
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_fulltext_keymappings'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_fulltext_pendingchanges'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_get_query_template'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_getbindtoken'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_getschemalock'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_http_generate_wsdl_complex'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_http_generate_wsdl_simple'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_MSgetversion'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_prepare'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_prepexec'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_prepexecrpc'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_releaseschemalock'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_replcmds'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_replcounters'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_replddlparser'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_repldone'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_replflush'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_replhelp'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_replsendtoqueue'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_replsetsyncstatus'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_repltrans'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_replwritetovarbin'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_reserve_http_namespace'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_reset_connection'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_resyncexecute'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_resyncexecutesql'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_resyncprepare'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_resyncuniquetable'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_SetOBDCertificate'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_setuserbylogin'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_start_user_instance'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_testlinkedserver'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_trace_create'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_trace_generateevent'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_trace_setevent'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_trace_setfilter'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_trace_setstatus'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_unprepare'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_update_user_instance'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_xml_preparedocument'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_xml_removedocument'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'sp_xp_cmdshell_proxy_account'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_adsirequest'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_availablemedia'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_cmdshell' 
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_create_subdir' 
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_delete_file' 
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_deletemail' 
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_dirtree'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_enum_oledb_providers' 
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_enumerrorlogs'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_enumgroups'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_fileexist'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_findnextmsg' 
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_fixeddrives'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_get_mapi_default_profile'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_get_mapi_profiles' 
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_get_script' 
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_get_tape_devices'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_getnetname'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_instance_regaddmultistring'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_instance_regdeletekey'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_regdeletevalue'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_regenumkeys' 
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_regenumvalues'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_instance_regread'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_instance_regremovemultistring'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_instance_regwrite'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_logevent'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_loginconfig'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_MSADEnabled'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_MSADSIObjReg'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_MSADSIObjRegDB'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_MSADSIReg'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_msver'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_msx_enlist'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_passAgentInfo'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_prop_oledb_provider'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_qv'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_readerrorlog'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_readmail'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_regaddmultistring'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_regdeletekey'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_regdeletevalue'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_regenumkeys'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_regenumvalues'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_regread'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_regremovemultistring'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_regwrite'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_replposteor'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_sendmail'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_servicecontrol'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_sprintf'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_sqlagent_enum_jobs'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_sqlagent_monitor'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_sqlagent_notify'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_sqlagent_param'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_sqlmaint'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_sscanf'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_startmail'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_stopmail'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_subdirs'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_sysmail_activate'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_sysmail_attachment_load'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_sysmail_format_query'
    exec sp_trace_set_filter @TraceID, 34, 0, 6, N'xp_xp_test_mapi_profile'
    exec sp_trace_set_filter @TraceID, 34, 0, 1, NULL
    -- Set the trace to start
    exec xp_trace_setstatus @TraceID, 1
    -- Display TraceID for future reference
    select TraceID = @TraceID
    goto finish
    error: 
    select ErrorCode = @rc
    finish: 
    go



    • Proposed as answer by Pete Kloehn Saturday, September 29, 2012 2:33 AM
    • Edited by Pete Kloehn Wednesday, October 3, 2012 7:49 PM Corrected code errors.
    Saturday, September 29, 2012 2:33 AM