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.
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:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT ComponentName = sac.component_name , ObjectName = sac.schema_name + N'.' + sac.object_name , IsEnabled = CASE WHEN sac.state = 1 THEN 'X' ELSE '' END FROM sys.system_components_surface_area_configuration sac ORDER BY sac.component_name , sac.schema_name , sac.object_name; |
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:
|
1 2 |
EXEC sys.sp_configure 'show advanced options', 1; RECONFIGURE; |
You can then enabled or disable SMO and DMO XPs with this code:
|
1 2 |
EXEC sys.sp_configure 'SMO and DMO XPs', 1; -- 1 is to enable, 0 to disable RECONFIGURE; |
Agent XPs can be enabled or disabled like this:
|
1 2 |
EXEC sys.sp_configure 'Agent XPs', 1; -- 1 is to enable, 0 to disable RECONFIGURE; |
xp_cmdshell can be enabled or disabled this way:
|
1 2 |
EXEC sys.sp_configure 'xp_cmdshell', 1; -- 1 is to enable, 0 to disable RECONFIGURE; |
Ole Automation Procedures can be enabled or disabled, you guessed it, like this:
|
1 2 |
EXEC sys.sp_configure 'Ole Automation Procedures', 1; -- 1 is to enable, 0 to disable RECONFIGURE; |
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.
[…] Manually enabling Kerberos authentication typically requires creating a Service Principal Name, or SPN, for the SQL Server instance, and rebooting the instance. The script below will automatically generate SETSPN commands for SPNs that are missing from Active Directory for the current SQL Server Instance. For the code to work, you need to have xp_cmdshell enabled. […]