To ensure that SQL Server is as secure as possible out of the box, a number of features that represent potential security risks are disabled by default, and must be explicitly enabled before they can be used. These features include the following:
• Remote connections
• Dedicated administrator connection
• .NET Framework
• Database Mail
• SQLMail
• Service Broker
• HTTP connectivity
• Database mirroring
• Web Assistant
• xp_cmdshell XP
• Ad Hoc Remote Queries
• OLE Automation XPs
• SMO and DMO XPs
In SQL Server 2005, a graphical tool called the Surface Area Configuration Tool was provided as a way to manage the configuration for these features. In SQL Server 2008, this tool has been removed, and the replacement functionality can be accomplished using Policy-based Management. For the most part, you can enable the database engine features mentioned here using the sp_configure system stored procedure. To manage network related options, such as remote connectivity, you use the SQL Server Configuration Manager.
Remote Connections
Starting with SQL Server 2005, by default, some SQL Server editions don’t accept remote connections; they accept only connections from the local machine. This clearly reduces risks considerably, as hackers will need access to the machine, or access to another enabled service that accepts remote connections (such as an HTTP endpoint). Unfortunately, it also dramatically reduces the usefulness of SQL Server! If your server really doesn’t need to accept remote connections (for example, if it’s only ever accessed from ASP.NET web pages on that machine), it’s a good idea to keep this setting. However, most SQL Server instances will need to be accessed from client machines that are running data-entry applications and the like. Therefore, in most cases, you’ll need to enable remote connections.
To enable remote connections, navigate to the Protocols node of the instance you want to manage in SQL Server Configuration Manager (SQL CM). The right pane of SQL CM shows the available protocols. From here, you can enable, disable, or modify the properties of the protocol, such as which specific port SQL Server should listen to for requests.
If you are planning to accept remote connections, you may need to add the port information to the firewall installed on the server. If the firewall does not have this information, your remote connections will fail.
Dedicated Administrator Connection
The dedicated administrator connection (DAC) can be used to connect to SQL Server when the service itself is refusing connections using the regular connection protocols. The DAC listens on a dedicated port that only members of the sysadmin server role can use. Also, it’s only possible to connect via the DAC from the local machine using the SQLCMD.EXE command-line tool. However, remote connections over the DAC can be enabled through the sp_configure procedure:
EXEC sp_configure 'remote admin connections', 1
GO
reconfigure
GO
The sa account still exists in SQL Server 2008, but Microsoft recommends that you use the sysadmin fixed server role instead, which has the same (unlimited) powers. In addition, SQL Server installation enforces a password for the sa account, so you will not be able to use a blank password (even though having a blank password is really easy to remember).
.NET Framework
Although having the common language runtime (CLR) enabled for a database server brings much new functionality to SQL Server, it also brings potential security loopholes if not administered correctly. There are few limits to the tasks that an unsafe SQL assembly can perform if the appropriate security code isn’t put in place; for example, with poor security, a SQL assembly could have access to the entire file system on the database server. Therefore, database administrators (DBAs) need to be especially vigilant of which actions .NET code is allowed to perform. For this reason, the CLR is disabled by default and needs to be explicitly enabled before SQL assemblies can be executed on that server. CLR support can be enabled by executing the sp_configure stored procedure with the 'clr enabled' option. You also need to run RECONFIGURE to activate this change:
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
Database Mail
SQL Server 2005 introduced the Database Mail replacement for SQLMail. Not only is Database Mail based on Simple Mail Transfer Protocol (SMTP), but it also leverages Service Broker for extremely fast and reliable e-mail service from SQL Server. With this flexibility and power, DBAs should be careful about who they give permissions to send mail, as it is quite easy to turn SQL Server into a spam engine. In addition, Database Mail should be configured to encrypt message contents, as messages are sent in plain text by default. This may require additional setup work for your SMTP server; however, you will mitigate packet-sniffing attacks by encrypting this channel. Database Mail can be enabled through the Configure Database Mail Wizard, which is launched from the context menu of the Management -> Database Mail node in Object Explorer. Note that Database Mail relies on Service Broker, so it will work only if it’s run in a database for which Service Broker has been enabled.
SQLMail
SQLMail is the traditional mail technology built into SQL Server, and it has been replaced by Database Mail. SQLMail poses the same potential security vulnerabilities as Database Mail, but with the added risk that, because it’s based on Messaging Application Programming Interface (MAPI), rather than directly on SMTP, SQLMail can read as well as send mail. This means that you need to make sure not only that any stored procedures that use SQLMail can’t send data from SQL Server to people who aren’t intended to access it, but also that it can’t be misused to access data stored in the e-mail account of the MSSQLServer service. Even worse, because it’s MAPI-based, SQLMail requires Outlook to be installed on the SQL Server machine, with all the attendant risks of viruses and worms transmitted through e-mail. Finally, SQLMail runs as an extended stored procedure in the same address space as the SQL Server process, so any failure could impact the entire server.
SQLMail can be enabled programmatically as follows:
EXEC sp_configure 'SQL Mail XPs', 1
GO
RECONFIGURE
GO
It is strongly recommended that you use Database Mail rather than SQLMail, if possible. SQLMail has been on the deprecation list since SQL Server 2005, so it is very likely the components will be completely gone from the SQL Server binaries within the next few releases.
Service Broker,HTTP Connectivity, and Database Mirroring
Service Broker is SQL Server’s asynchronous messaging technology. If you want to create additional SQL Server services, such as Service Broker or web services, by default, SQL Server won’t accept connections. To enable these, you need to explicitly create an endpoint telling SQL Server to listen or talk on a specific port. Database mirroring is an easy-to-manage alternative to log shipping and failover clustering, and involves creating an exact copy of a database on another SQL Server instance that can be used if the primary database fails. Any updates to the primary database are replicated to the mirror database, so the copy remains exact. Again, the security liability here is that data must be transmitted over the network to the mirror server, and therefore also requires HTTP endpoints to be configured and active.
Web Assistant
Web Assistant is a set of stored procedures that create HTML pages based on a database query. In general, Web Assistant is deprecated, and its functionality is replaced with Reporting Services. However, it is still supported for backward compatibility. To enable Web Assistant, run the following command:
EXEC sp_configure 'Web Assistant Procedures', 1
GO
RECONFIGURE
GO
xp_cmdshell XP
The xp_cmdshell extended procedure (XP) allows operating system commands to be executed from within the database environment. This has obvious dangers from both accidental and malicious misuse, so this procedure is disabled by default. It can be enabled through the sp_configure procedure:
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
In SQL Server 2005, the proxy account used when calling xp_cmdshell changed. In previous versions, calling xp_cmdshell via a SQL authenticated user would leverage the proxy account defined in the SQL Server Agent service. In SQL Server 2005 and later, the proxy credentials used for xp_cmdshell are set by using sp_xp_cmdshell_proxy_account.
Ad Hoc Remote Queries
If Ad Hoc Remote Queries are enabled, the OPENROWSET and OPENDATASOURCE functions can be used to query a remote SQL Server without setting up a linked server. These queries are harder to keep track of than linked or remote servers, as they will be buried in Transact-SQL (T-SQL) code, so Ad Hoc Remote Queries should be enabled only if your stored procedures actually make such queries. Ad Hoc Remote Queries can be enabled through the sp_configure procedure:
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
OLE Automation XPs
By default, XPs aren’t permitted to call custom OLE Automation objects. We hope that in time, .NET assemblies will begin to replace C++ XPs, and DBAs will have more control over what code is running in the database. In the meantime, the DBA has no control whatsoever (except for the assurances of the developer!) over what these objects do, so OLE Automation XPs should be permitted only if necessary. To do this, you can use sp_configure:
EXEC sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE
GO
SMO and DMO XPs
Server Management Objects (SMO), and its predecessor Distributed Management Objects (DMO), provide APIs for developers to perform automated administration tasks on SQL Server. There is naturally some security risk involved in permitting these, as both SMO and DMO can be used in external applications for managing SQL Server, over which the DBA has little or no control. However, SMO and DMO are enabled by default, as two of SQL Server’s management tools, Management Studio and Database Tuning Advisor (DTA), rely on them. You should therefore disable them only if you don’t need these tools, and neither SMO nor DMO are used by any existing applications. They can be disabled using sp_configure:
EXEC sp_configure 'SMO and DMO XPs', 0
GO
RECONFIGURE
GO
Source of Information : Apress Accelerated SQL Server 2008
• Remote connections
• Dedicated administrator connection
• .NET Framework
• Database Mail
• SQLMail
• Service Broker
• HTTP connectivity
• Database mirroring
• Web Assistant
• xp_cmdshell XP
• Ad Hoc Remote Queries
• OLE Automation XPs
• SMO and DMO XPs
In SQL Server 2005, a graphical tool called the Surface Area Configuration Tool was provided as a way to manage the configuration for these features. In SQL Server 2008, this tool has been removed, and the replacement functionality can be accomplished using Policy-based Management. For the most part, you can enable the database engine features mentioned here using the sp_configure system stored procedure. To manage network related options, such as remote connectivity, you use the SQL Server Configuration Manager.
Remote Connections
Starting with SQL Server 2005, by default, some SQL Server editions don’t accept remote connections; they accept only connections from the local machine. This clearly reduces risks considerably, as hackers will need access to the machine, or access to another enabled service that accepts remote connections (such as an HTTP endpoint). Unfortunately, it also dramatically reduces the usefulness of SQL Server! If your server really doesn’t need to accept remote connections (for example, if it’s only ever accessed from ASP.NET web pages on that machine), it’s a good idea to keep this setting. However, most SQL Server instances will need to be accessed from client machines that are running data-entry applications and the like. Therefore, in most cases, you’ll need to enable remote connections.
To enable remote connections, navigate to the Protocols node of the instance you want to manage in SQL Server Configuration Manager (SQL CM). The right pane of SQL CM shows the available protocols. From here, you can enable, disable, or modify the properties of the protocol, such as which specific port SQL Server should listen to for requests.
If you are planning to accept remote connections, you may need to add the port information to the firewall installed on the server. If the firewall does not have this information, your remote connections will fail.
Dedicated Administrator Connection
The dedicated administrator connection (DAC) can be used to connect to SQL Server when the service itself is refusing connections using the regular connection protocols. The DAC listens on a dedicated port that only members of the sysadmin server role can use. Also, it’s only possible to connect via the DAC from the local machine using the SQLCMD.EXE command-line tool. However, remote connections over the DAC can be enabled through the sp_configure procedure:
EXEC sp_configure 'remote admin connections', 1
GO
reconfigure
GO
The sa account still exists in SQL Server 2008, but Microsoft recommends that you use the sysadmin fixed server role instead, which has the same (unlimited) powers. In addition, SQL Server installation enforces a password for the sa account, so you will not be able to use a blank password (even though having a blank password is really easy to remember).
.NET Framework
Although having the common language runtime (CLR) enabled for a database server brings much new functionality to SQL Server, it also brings potential security loopholes if not administered correctly. There are few limits to the tasks that an unsafe SQL assembly can perform if the appropriate security code isn’t put in place; for example, with poor security, a SQL assembly could have access to the entire file system on the database server. Therefore, database administrators (DBAs) need to be especially vigilant of which actions .NET code is allowed to perform. For this reason, the CLR is disabled by default and needs to be explicitly enabled before SQL assemblies can be executed on that server. CLR support can be enabled by executing the sp_configure stored procedure with the 'clr enabled' option. You also need to run RECONFIGURE to activate this change:
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
Database Mail
SQL Server 2005 introduced the Database Mail replacement for SQLMail. Not only is Database Mail based on Simple Mail Transfer Protocol (SMTP), but it also leverages Service Broker for extremely fast and reliable e-mail service from SQL Server. With this flexibility and power, DBAs should be careful about who they give permissions to send mail, as it is quite easy to turn SQL Server into a spam engine. In addition, Database Mail should be configured to encrypt message contents, as messages are sent in plain text by default. This may require additional setup work for your SMTP server; however, you will mitigate packet-sniffing attacks by encrypting this channel. Database Mail can be enabled through the Configure Database Mail Wizard, which is launched from the context menu of the Management -> Database Mail node in Object Explorer. Note that Database Mail relies on Service Broker, so it will work only if it’s run in a database for which Service Broker has been enabled.
SQLMail
SQLMail is the traditional mail technology built into SQL Server, and it has been replaced by Database Mail. SQLMail poses the same potential security vulnerabilities as Database Mail, but with the added risk that, because it’s based on Messaging Application Programming Interface (MAPI), rather than directly on SMTP, SQLMail can read as well as send mail. This means that you need to make sure not only that any stored procedures that use SQLMail can’t send data from SQL Server to people who aren’t intended to access it, but also that it can’t be misused to access data stored in the e-mail account of the MSSQLServer service. Even worse, because it’s MAPI-based, SQLMail requires Outlook to be installed on the SQL Server machine, with all the attendant risks of viruses and worms transmitted through e-mail. Finally, SQLMail runs as an extended stored procedure in the same address space as the SQL Server process, so any failure could impact the entire server.
SQLMail can be enabled programmatically as follows:
EXEC sp_configure 'SQL Mail XPs', 1
GO
RECONFIGURE
GO
It is strongly recommended that you use Database Mail rather than SQLMail, if possible. SQLMail has been on the deprecation list since SQL Server 2005, so it is very likely the components will be completely gone from the SQL Server binaries within the next few releases.
Service Broker,HTTP Connectivity, and Database Mirroring
Service Broker is SQL Server’s asynchronous messaging technology. If you want to create additional SQL Server services, such as Service Broker or web services, by default, SQL Server won’t accept connections. To enable these, you need to explicitly create an endpoint telling SQL Server to listen or talk on a specific port. Database mirroring is an easy-to-manage alternative to log shipping and failover clustering, and involves creating an exact copy of a database on another SQL Server instance that can be used if the primary database fails. Any updates to the primary database are replicated to the mirror database, so the copy remains exact. Again, the security liability here is that data must be transmitted over the network to the mirror server, and therefore also requires HTTP endpoints to be configured and active.
Web Assistant
Web Assistant is a set of stored procedures that create HTML pages based on a database query. In general, Web Assistant is deprecated, and its functionality is replaced with Reporting Services. However, it is still supported for backward compatibility. To enable Web Assistant, run the following command:
EXEC sp_configure 'Web Assistant Procedures', 1
GO
RECONFIGURE
GO
xp_cmdshell XP
The xp_cmdshell extended procedure (XP) allows operating system commands to be executed from within the database environment. This has obvious dangers from both accidental and malicious misuse, so this procedure is disabled by default. It can be enabled through the sp_configure procedure:
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
In SQL Server 2005, the proxy account used when calling xp_cmdshell changed. In previous versions, calling xp_cmdshell via a SQL authenticated user would leverage the proxy account defined in the SQL Server Agent service. In SQL Server 2005 and later, the proxy credentials used for xp_cmdshell are set by using sp_xp_cmdshell_proxy_account.
Ad Hoc Remote Queries
If Ad Hoc Remote Queries are enabled, the OPENROWSET and OPENDATASOURCE functions can be used to query a remote SQL Server without setting up a linked server. These queries are harder to keep track of than linked or remote servers, as they will be buried in Transact-SQL (T-SQL) code, so Ad Hoc Remote Queries should be enabled only if your stored procedures actually make such queries. Ad Hoc Remote Queries can be enabled through the sp_configure procedure:
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
OLE Automation XPs
By default, XPs aren’t permitted to call custom OLE Automation objects. We hope that in time, .NET assemblies will begin to replace C++ XPs, and DBAs will have more control over what code is running in the database. In the meantime, the DBA has no control whatsoever (except for the assurances of the developer!) over what these objects do, so OLE Automation XPs should be permitted only if necessary. To do this, you can use sp_configure:
EXEC sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE
GO
SMO and DMO XPs
Server Management Objects (SMO), and its predecessor Distributed Management Objects (DMO), provide APIs for developers to perform automated administration tasks on SQL Server. There is naturally some security risk involved in permitting these, as both SMO and DMO can be used in external applications for managing SQL Server, over which the DBA has little or no control. However, SMO and DMO are enabled by default, as two of SQL Server’s management tools, Management Studio and Database Tuning Advisor (DTA), rely on them. You should therefore disable them only if you don’t need these tools, and neither SMO nor DMO are used by any existing applications. They can be disabled using sp_configure:
EXEC sp_configure 'SMO and DMO XPs', 0
GO
RECONFIGURE
GO
Source of Information : Apress Accelerated SQL Server 2008
|
|
0 comments
Post a Comment