Optional features are turned off by default |
 |
By Bob Beauchemin, Niels Berglund and Dan Sullivan
28 Feb 2005 | Addison-Wesley |
|
|
 The following excerpt, courtesy of Addison-Wesley, is from Chapter 6 of the book "A First Look at SQL Server 2005 for Developers" written by Bob Beauchemin, Niels Berglund and Dan Sullivan. Click for the complete book excerpt series or purchase the book.
Optional features are turned off by default
The SQL Server product has always been known for being feature-rich. A multitude of new features are added with each release, too many for many DBAs to keep track of. Although it's a treat to see new features (that's what this book is about, after all), in previous releases they usually arrive with the product, installed and enabled by default. And enabled features that you don't know exist can hurt you, by increasing the "surface area" exposed to attackers. These "bad guys" probe for any weakness in existing and new features, which in past releases included goodies such as command-
line program invocation (xp_cmdshell), sending mail messages (xp_sendmail), and invocation of COM classes (sp_OACreate). Most of these features will run as the user that runs the SQL Server service process. Most times, for ease of installation, this is set to LocalSystem, a Windows superuser account. And if you are running in mixed security mode and you've set your "sa" password to null (that is, no password), you're wide open, although it must be pointed out that neither mixed security nor a blank password is the default, and a blank password is strongly discouraged throughout the product and all utilities.
In the SQL Server 2005 release, care has been taken to reduce the "attack surface area," especially with regard to SQL Server passwords and auto-enablement of new features. You'll read later in this chapter about improvements when using SQL Server logins and passwords. With regard to feature enabling, two good examples of the new policy are ENDPOINTs used for HTTP (discussed in Chapter 10) and SQLCLR features (discussed in Chapters 2–5).
HTTP ENDPOINTs (endpoints that allow SQL Server to expose stored procedures as Web services) are not enabled by default. Someone with an extended privilege must explicitly invoke CREATE ENDPOINT; there are no "default endpoints" set up on install. ENDPOINT definition prohibits using any security style but Windows integrated choices (Windows integrated security never sends passwords over the network, even in encrypted form) unless you are using the secure socket layer (SSL). SSL, though not as robust as Windows integrated (NTLM or Kerberos) authentication, does at least encrypt all network traffic for the connection. It's the same encryption you use to send credit card numbers over the Internet when you purchase products on the Web. When an ENDPOINT is defined, it's not ON by default; you must create it as ENABLED or explicitly enable it with an ALTER ENDPOINT command. No Web Services or batch access (available separately) is enabled by default within an ENDPOINT, either.
SQLCLR (the ability to run .NET code in SQL Server 2005) is enabled or
disabled through a server configuration option. You can turn it on with the
following code:
sp_configure 'clr enabled', 1
Although it has been enabled by default through much of the beta process, it will most likely be disabled by default when SQL Server 2005 ships.
Security and Metadata
One of the ways to gain information about a SQL Server database is to
rummage through the system metadata views. In previous versions of SQL Server, you could retrieve metadata information on other users' database objects that you did not own or have access to. SQL Server 2005 remedies this situation.
One of the new permissions added to SQL Server 2005 is the VIEW DEFINITION permission. Not only are the new system metadata views (that start with the identifier sys.) read-only, but you can use the VIEW DEFINITION privilege to permit or prohibit access to metadata. This privilege is exposed on individual database objects; it's also very useful at the schema level. If you do not have VIEW DEFINITION permission, system views and stored procedures will not list object information (for example, through sp_helptext) or metadata.
Error messages have been changed as well, so as not to provide information
that would indicate the existence of objects you don't have access to. For example, attempting to drop a procedure that you don't "know about" produces the following error message: "Cannot drop the procedure 'foo,' because it does not exist or you do not have permission." This makes SQL Server metadata less subject to random browsing.
Click for the next excerpt in this series: A quick review of SQL Server security concepts with enhancements
Click for the book excerpt series or visit here to obtain the complete book.
|
|
|
|
