In SQL Server 2005, access the SQL Server Surface Area Configuration Tool from Start -> All Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration. This tool can also be accessed by typing SQLSAC.EXE from Command Line.
Once the SQL Server Surface Area Configuration Tool loads as shown in the above snippet; two main configurations options are given:-
1. Surface Area Configuration for Services and Connections
2. Surface Area Configuration for Features
Overview of Surface Area Configuration for Services and Connections
Clicking on “Surface Area Configuration for Services and Connections” link within SQL Server 2005 Surface Area Configuration screen will open up a new screen containing the list of all the SQL Server 2005 related services; such as Database Engine, Analysis, Reporting, SQL Server Agent etc as shown below.
Using SQLSAC, database administrators can quickly select any service from the list of available services to start, stop, pause or resume the service. Moreover, the startup types can be changed to Automatic, Manual or Disabled. All these activities can also be performed using SQL Server Configuration Manager Tool; accessed from Start -> All Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager. It is advised to change the SQL Server Account information using SQL Server Configuration Manager.
Using SQLSAC, DBA’s can also enable SQL Server 2005 database engine to accept remote connections. This can be done by expand Database Engine, expand MSSQLSERVER and select Remote Connections. On the right side panel select “Local and remote connections” option and then the appropriate protocol option among the three choices available depending upon requirements. In the snippet below note the “Using both TCP/IP and named pipes” option is selected.
Click OK to save the changes; this will open up “Connection Settings Change Alert” dialog box asking to restart the Database Engine Service for the changes to take effect. By default SQL Server 2005 Express, Evaluation and Developer Editions doesn’t allow remote client connections. This feature needs to be manually turned on to allow remote connections.
Follow the same approach to enable SQL Server 2005 Analysis Services to accept remote connections as shown in the below snippet.
Using SQL Server Surface Area Configuration tool the DBA can manage features on both local and remote SQL Servers. In order to configure and enable features on a remote SQL Server, provide the remote SQL Server name by clicking the “change computer” link as shown in the snippet below.
Overview of Surface Area Configuration for Features
Once clicked the “Surface Area Configuration for Features” link within SQL Server 2005 Surface Area Configuration screen, will open a new screen which has the list of SQL Server 2005 Database Engine, Analysis Services & Reporting Services features. Such features include CLR Integration, DAC, Database Email, Service Broker, xp_cmdshell, Linked Objects etc which are turned off by default within SQL Server 2005. Features can be enableed in SQL Server 2005 using the sp_configure system stored procedure. In SQL Server 2008 SQL Server, Surface Area Configuration tool is not available, hence the need to use sp_configure system stored procedure to enable required features. Following is an exploration of each feature under the Database Engine.
Database Engine Features
Ad Hoc Remote Queries: – The OPENROWSET and OPENDATASOURCE functions support ad hoc connections to remote data sources without using linked or remote servers. Enable this feature either by using SQLSAC or by executing the below TSQL code.
USE master
GO
sp_configure ‘show advanced options’, 1
RECONFIGURE WITH OVERRIDE
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1
RECONFIGURE WITH OVERRIDE
GO
CLR Integration: – By enabling Common Language Runtime (CLR) stored procedures, triggers, user-defined types (UDT’s) & user-defined functions (UDF’s) can be written using any .Net framework language. Enable this feature by using SQLSAC or by executing the below TSQL code.
sp_configure ‘clr enabled’, 1
RECONFIGURE WITH OVERRIDE
GO
DAC: – By enabling Dedicated Administrator Connection (DAC) a database administrator can connect to an instance of SQL Server 2005, when the Database Engine is not responding to regular connections. Enable this feature by using SQLSAC or by executing the below TSQL code. To learn more about DAC, refer to the previous article titled “Dedicated Administrator Connection in SQL Server”
sp_configure ‘remote admin connections’, ‘1’
GO
RECONFIGURE WITH OVERRIDE
GO
Database Mail: – By enabling Database Mail feature email messages can be sent from the database engine using SMTP. Enable this feature by using SQLSAC or by executing the below TSQL code.
sp_configure ‘Database Mail XPs’, 1
RECONFIGURE WITH OVERRIDE
GO
Native XML Web Services: – By enabling Native XML Web Services feature database access can be provided over HTTP using Simple Object Access Protocol (SOAP) messages. Only enable HTTP endpoints if applications are using them to communicate to SQL Server 2005 Database.
OLE Automation: – By enabling OLE automation extended stored procedures TSQL batches, stored procedures, triggers etc are allowed to reference custom OLE Automation objects. Enable this feature by using SQLSAC or by executing the below TSQL code.
sp_configure ‘Ole Automation Procedures’, 1
RECONFIGURE WITH OVERRIDE
GO
Service Broker: – enabling Service Broker provides queuing and reliable messaging for the database engine. Service Broker basically uses and endpoints for communicating between SQL Server instances. To do this enable the TCP/IP port on the server.
SQL Mail: – feature permits support of legacy applications that were used to send and receive email messages from the SQL Server Database Engine. This is a feature is deprecated in SQL Server 2005 and higher versions and the alternative to this feature is Database Mail. To enable this feature by using SQLSAC or by executing the below TSQL code.
sp_configure ‘SQL Mail XPs’, 1
RECONFIGURE WITH OVERRIDE
GO
Web Assistance: – Web Assistance stored procedures generate HTML files from SQL Server databases; this feature is also deprecated in SQL Server 2005 and higher versions. Enable this feature by using SQLSAC or by executing the below TSQL code.
sp_configure ‘Web Assistant Procedures’, 1
RECONFIGURE WITH OVERRIDE
GO
xp_cmdshell: – By enabling xp_cmdshell extended stored procedure permits running operating system commands from the SQL Server Database Engine. Enable this feature by using SQLSAC or by executing the below TSQL code.
sp_configure ‘xp_cmdshell’, 1
RECONFIGURE WITH OVERRIDE
GO
Analysis Services Features
Ad hoc data mining queries: – Once ad hoc data mining queries are enabled; the passing of OLEDB provider name and connection strings when using Data Mining Extensions (DMX) OPENROWSET functions can be enabled or disabled.
Anonymous Connections: – enabling Anonymous connections feature allows unauthenticated users to establish connections with Microsoft SQL Server 2005 Analysis Services.
Linked Objects: – enabling Linked Objects feature will provide the ability to link dimensions and measure groups between Instances. However, it’s required to enable this feature at both analysis services instances.
User–defined Functions: – By enabling this feature analysis services will allow users to load assemblies containing user-defined functions (UDF).
Reporting Services Features
Scheduled Events and Report Delivery: – enable this feature to schedule report snapshot, report delivery and report cache expirations.
Web Services and HTTP Access: -enable this feature to report server web service to receive SOAP and direct URL access requests.
Windows Integrated Security: – enabling this feature allows report data sources to use Windows Integrated Security to connect to all the external data sources.
Conclusion
Using SQL Server Surface Area Configuration tool DBA’s or database developers can quickly turn on features which are by default turned off within the product. However, it is advised to leave the default settings turned off unless they are needed in the environment.
]]>