Configure SQL Server Surface Area - SQL-FineBuild/Common GitHub Wiki

Previous Configure SQL Service Recovery	Manual Configuration	Configure Errorlog Retention Next

FineBuild can configure SQL Server Surface Area to optimise it for security and manageability.

SQL Server surface area configuration is performed by running a series of queries in SQL Server Management Studio.

FineBuild SQL Server Surface Area configuration

The SQL Server Surface Area configuration relates to Process Id 5BA and is controlled by the parameters below:

SQL Version	Parameter	FULL Build	WORKSTATION Build	CLIENT Build
SQL2019	/SetupSQLServer:	Yes	Yes	N/A
SQL2017	/SetupSQLServer:	Yes	Yes	N/A
SQL2016	/SetupSQLServer:	Yes	Yes	N/A
SQL2014	/SetupSQLServer:	Yes	Yes	N/A
SQL2012	/SetupSQLServer:	Yes	Yes	N/A
SQL2008R2	/SetupSQLServer:	Yes	Yes	N/A
SQL2008	/SetupSQLServer:	Yes	Yes	N/A
SQL2005	/SetupSQLServer:	Yes	Yes	N/A

In order to maintain compatibility with older versions of SQL FineBuild, the parameter ConfigSQLServer can also be used.

FineBuild also uses the following parameters to help configure SQL Server Surface Area:

Parameter	Default Value	Description
spConfigureCLREnabled	1	Used to enable use of the CLR
spConfigureRemoteAdminConnections	1	Used to enable Dedicated Administrator Connection
spConfigureRemoteProcTrans	0	Used to force use of MSDTC for Distributed Transactions
spConfigurexpCmdshell	1	Used to enable use of xp_cmdshell by non-sysadmin users
spConfigureOptimizeForAdHocWorkloads	1	Used to enable optimisation of ad-hoc workloads. For more details see https://www.itprotoday.com/sql-server/should-i-optimize-my-sql-server-instances-ad-hoc-workloads

Top

Manual SQL Server Surface Area Configuration

The following steps show what you would have to do for manual SQL Server Surface Area configuration. FineBuild does all of this work for you automatically.

Start SQL Server Management Studio and open a query window.

Allow the display of advanced options by running the following commands:

   sp_configure 'show advanced options', 1
   RECONFIGURE

Common configuration

This configuration is done for all SQL Server editions. If a Named Instance is being configured then complete the rest of this section using that instance, otherwise use the default instance.

Enable the following options using the sp_configure options shown below.

Function	SP_Configure Option
SQL Agent full functionality	'Agent XPs', '1'
CLR status	'clr enabled', 'spConfigureCLREnabled'
Disallow results from triggers	'Disallow results from triggers:', '1'
OLE Automation procedures	'Ole Automation Procedures', '1'
Remote use of the Dedicated Administrator Connection (DAC) feature	'remote admin connections', 'spConfigureRemoteAdminConnections'
Protect server to server transactions by using MSDTC	'remote proc trans', 'spConfigureRemoteProcTrans'
SQL Management procedures	'SMO and DMO XPs', '1'
Use of xp_cmdshell	'xp_cmdshell', 'spConfigurexpCmdshell'

Standard Edition additional configuration

If Standard Edition for SQL 2008 R2 or above has been installed, the following options should also be set:

Function SP_Configure Option

Enable backup compression 'backup compression default', '1'

Business Intelligence Edition additional configuration

If Business Intelligence Edition for SQL 2012 and above has been installed, the following options should also be set:

Function	SP_Configure Option
Enable backup compression	'backup compression default', '1'
Ad-Hoc workload optimisation	'optimize for ad hoc workloads', 'spConfigureOptimizeForAdHocWorkloads'

Enterprise Edition additional configuration

If Enterprise Edition for SQL 2005 and above has been installed, the following options should also be set:

Function	SP_Configure Option
Enable backup compression	'backup compression default', '1'
Enable extended key management	'EKM provider enabled', '1'
Ad-Hoc workload optimisation	'optimize for ad hoc workloads', 'spConfigureOptimizeForAdHocWorkloads'

After all options have been set, run the following command:

  RECONFIGURE WITH OVERRIDE

Previous Configure SQL Service Recovery	Top	Configure Errorlog Retention Next