Surface Area Configuration Audit

The Surface Area Setup

Microsoft has built an incredible number of excellent features into SQL Server over the years, vastly increasing the surface area of possible vulnerabilities. Some of these features can be both a boon and, simultaneously, a security nightmare.

kitten surface area attack!

SQL Server is not quite as much fun as two kittens fighting!

A number of features are not enabled by default in order to limit the attack surface of the product for out-of-the-box installations. As a result, many SQL Server DBAs will enable some subset of these features, since they can make life much easier and some features can be hard to live without! For instance, xp_cmdshell provides a mechanism for running command-line utilities from T-SQL, which can be very useful for a multitude of tasks such as cleaning up old backups, getting filesystem properties, etc.

The Details

If you’ve just been assigned to manage a SQL Server instance, one of the first things you should do is familiarize yourself with exactly which potential security holes are enabled on that instance. Fortunately, there is a pretty easy way to identify the list of system components that are currently enabled. Since SQL Server 2008, Microsoft has provided the sys.system_components_surface_area_configuration1 system DMV for just that purpose.

Run that DMV using this code:

The output will look similar to this:

╔═══════════════════════════╦══════════════════════════════════════╦═══════════╗
║       ComponentName       ║              ObjectName              ║ IsEnabled ║
╠═══════════════════════════╬══════════════════════════════════════╬═══════════╣
║ Agent XPs                 ║ dbo.sp_add_maintenance_plan_job      ║ X         ║
║ Agent XPs                 ║ dbo.sp_downloaded_row_limiter        ║ X         ║
║ Agent XPs                 ║ dbo.sp_enlist_tsx                    ║ X         ║
║ Agent XPs                 ║ dbo.sp_generate_server_description   ║ X         ║
║ Agent XPs                 ║ dbo.sp_get_composite_job_info        ║ X         ║
║ Agent XPs                 ║ dbo.sp_get_schedule_description      ║ X         ║
║ Agent XPs                 ║ dbo.sp_get_sqlagent_properties       ║ X         ║
║ Agent XPs                 ║ dbo.sp_is_sqlagent_starting          ║ X         ║
║ Agent XPs                 ║ dbo.sp_jobhistory_row_limiter        ║ X         ║
║ Agent XPs                 ║ dbo.sp_msx_defect                    ║ X         ║
║ Agent XPs                 ║ dbo.sp_msx_enlist                    ║ X         ║
║ Agent XPs                 ║ dbo.sp_msx_get_account               ║ X         ║
║ Agent XPs                 ║ dbo.sp_msx_set_account               ║ X         ║
║ Agent XPs                 ║ dbo.sp_post_msx_operation            ║ X         ║
║ Agent XPs                 ║ dbo.sp_set_local_time                ║ X         ║
║ Agent XPs                 ║ dbo.sp_set_sqlagent_properties       ║ X         ║
║ Agent XPs                 ║ dbo.sp_sqlagent_get_startup_info     ║ X         ║
║ Agent XPs                 ║ dbo.sp_sqlagent_has_server_access    ║ X         ║
║ Agent XPs                 ║ dbo.sp_sqlagent_notify               ║ X         ║
║ Agent XPs                 ║ dbo.sp_update_operator               ║ X         ║
║ Agent XPs                 ║ dbo.sp_verify_alert                  ║ X         ║
║ Agent XPs                 ║ dbo.sp_verify_job                    ║ X         ║
║ Agent XPs                 ║ dbo.sp_verify_subsystem              ║ X         ║
║ Agent XPs                 ║ sys.sp_PostAgentInfo                 ║ X         ║
║ Agent XPs                 ║ sys.xp_getnetname                    ║ X         ║
║ Agent XPs                 ║ sys.xp_instance_regdeletevalue       ║ X         ║
║ Agent XPs                 ║ sys.xp_instance_regread              ║ X         ║
║ Agent XPs                 ║ sys.xp_instance_regwrite             ║ X         ║
║ Agent XPs                 ║ sys.xp_msver                         ║ X         ║
║ Agent XPs                 ║ sys.xp_msx_enlist                    ║ X         ║
║ Agent XPs                 ║ sys.xp_passAgentInfo                 ║ X         ║
║ Agent XPs                 ║ sys.xp_regread                       ║ X         ║
║ Agent XPs                 ║ sys.xp_regwrite                      ║ X         ║
║ Agent XPs                 ║ sys.xp_sqlagent_enum_jobs            ║ X         ║
║ Agent XPs                 ║ sys.xp_sqlagent_is_starting          ║ X         ║
║ Agent XPs                 ║ sys.xp_sqlagent_monitor              ║ X         ║
║ Agent XPs                 ║ sys.xp_sqlagent_notify               ║ X         ║
║ Agent XPs                 ║ sys.xp_sqlagent_param                ║ X         ║
║ Database Mail XPs         ║ dbo.sp_send_dbmail                   ║ X         ║
║ Database Mail XPs         ║ dbo.sp_validate_user                 ║ X         ║
║ Database Mail XPs         ║ dbo.sysmail_help_status_sp           ║ X         ║
║ Database Mail XPs         ║ dbo.sysmail_start_sp                 ║ X         ║
║ Database Mail XPs         ║ dbo.sysmail_stop_sp                  ║ X         ║
║ Database Mail XPs         ║ sys.xp_sysmail_activate              ║ X         ║
║ Database Mail XPs         ║ sys.xp_sysmail_attachment_load       ║ X         ║
║ Database Mail XPs         ║ sys.xp_sysmail_format_query          ║ X         ║
║ Ole Automation Procedures ║ sys.sp_OACreate                      ║           ║
║ Ole Automation Procedures ║ sys.sp_OADestroy                     ║           ║
║ Ole Automation Procedures ║ sys.sp_OAGetErrorInfo                ║           ║
║ Ole Automation Procedures ║ sys.sp_OAGetProperty                 ║           ║
║ Ole Automation Procedures ║ sys.sp_OAMethod                      ║           ║
║ Ole Automation Procedures ║ sys.sp_OASetProperty                 ║           ║
║ Ole Automation Procedures ║ sys.sp_OAStop                        ║           ║
║ SMO and DMO XPs           ║ sys.sp_getProcessorUsage             ║ X         ║
║ SMO and DMO XPs           ║ sys.sp_getVolumeFreeSpace            ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_availablemedia                ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_dirtree                       ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_enum_oledb_providers          ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_enumerrorlogs                 ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_enumgroups                    ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_fileexist                     ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_fixeddrives                   ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_get_tape_devices              ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_getnetname                    ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_instance_regaddmultistring    ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_instance_regdeletevalue       ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_instance_regenumvalues        ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_instance_regread              ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_instance_regremovemultistring ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_instance_regwrite             ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_logininfo                     ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_msver                         ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_prop_oledb_provider           ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_readerrorlog                  ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_regaddmultistring             ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_regdeletekey                  ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_regdeletevalue                ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_regread                       ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_regremovemultistring          ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_regwrite                      ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_servicecontrol                ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_sqlagent_monitor              ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_sqlagent_notify               ║ X         ║
║ SMO and DMO XPs           ║ sys.xp_subdirs                       ║ X         ║
║ xp_cmdshell               ║ sys.xp_cmdshell                      ║ X         ║
╚═══════════════════════════╩══════════════════════════════════════╩═══════════╝

sys.sp_configure is used to enable and disable the items listed above. First thing to do is enable sys.sp_configure to show “advanced” options, like this:

You can then enabled or disable SMO and DMO XPs with this code:

Agent XPs can be enabled or disabled like this:

xp_cmdshell can be enabled or disabled this way:

Ole Automation Procedures can be enabled or disabled, you guessed it, like this:

See Also…

Microsoft also provides an excellent tool for looking at vulnerabilities on a database level with the SQL Vulnerability Assessment tool. Be aware, it may advise you to disable the Remote Admin Connection, which is something I’m mostly against, as discussed in this post.

This post is part of our series on SQL Server Security.


1 – https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-system-components-surface-area-configuration-transact-sql?view=sql-server-2017

Ads by Google, Paying the Rent: