Platform Security

Most DBAs are not responsible for the network or operating system security. Even so, you should be aware of the risks these components have and where your boundary is. This is called a "border" in security parlance, and because SQL Server 2005 is so integrated into the Microsoft operating system, you need to be aware of crossover points.

The first part of good platform security is controlling physical access to your system. This involves what should be commonsense measures such as locating the server cabinet and network hardware in a separate rooms and controlling access to those items. It also includes securing the backup media, wherever it ends up. If you send your backup tapes offsite, check on their security policies, too. Securing the platform requires that you control who has access to the physical components it has.

Securing the Network

Although you may not have to configure routers and other network hardware, you will certainly live within the borders of that security, and sometimes even outside it. Make sure there is a security plan for those devices in place.

There are networking considerations within SQL Server 2005 that you do have control over, including the protocols and ports that SQL Server listens and talks on.

You can configure the ports that SQL Server uses. By default, you need to protect TCP port 1433 and UDP port 1434 for the default instance. Even better, you should change those ports, using the SQL Server Configuration Manager tool.

To make the change, open the SQL Server Configuration Manager tool, expand the SQL Server 2005 Network Configuration item, and then click the Protocols for MSSQLServer object, as shown in Figure 4-1.

Figure 4-1.

[View full size image]

On the right pane of the tool, you will see the protocols listed. You can double-click these protocols to get their properties, which show different settings different based on the protocol. For TCP/IP, you can set the port number, timeouts, and even which IP addresses the server will listen on if you have multiple network cards on the server.

If you are using TCP/IP and want to use the Browser Service so that the port is dynamically set by SQL Server 2005 on startup, access the IP Addresses tab within the properties view and set the TCP Dynamic Ports to 0.

If the only application that uses SQL Server is located on the same system as the database, you can disable all protocols on the server with the exception of Shared Memory. It is not often that you will see this arrangement, and most often not a good idea to use SQL Server as a client to itself, but certain applications such as kiosk systems are often set up this way.

If at all possible, use the system only for SQL Server. Do not share files, printers, or other services from the system, to be truly safe.

You should also place your server behind a firewall and block the following NetBIOS- and SMB-related ports on the firewall:

• UDP/137 NetBIOS name

• UDP/138 NetBIOS datagram

• TCP/139 NetBIOS session

• TCP/139 - SMB

• TCP/445 - SMB

Further along in the chapter, I explain how to encrypt the network traffic during SQL Server communications with the client. This provides a high degree of safety between the client application and the database.

You can also guarantee who the sender is by using a certificate. A certificate is an electronic "signature" that contains data specific to a user or organization. It identifies who the sender of the traffic is. You can have SQL Server 2005 create a certificate for you or you can use a certifying authority to provide the certificate.

You may want to consider another mechanism for highly secure network needs. Certain network cards (called cryptocards) can establish a software key with a partner card and encrypt the data transport with that key, in complete transparency to any application. The advantage of this setup is that you do not have to do anything further to secure the transmissions to and from the server, because the card will only talk to those that are secured in a similar fashion. The primary consideration in this scenario is that all cards, including any used in replication or clustering, need the same level of security. These cards are vendor specific and have their own setup requirements.

Both the software encryption and card security methods help protect against "man-in-the-middle" attacks. In this type of security breach, a system intercepts the packets from the source and replaces its own payloads after the packet headers. The server (or workstation) interprets those packets as coming from the original source and is compromised. Using encryption and certificates prevents this because the two systems have independent ways of verifying the source of the packet.

You should also consider using Active Directory if you are not already, with strong policies. It is not always the DBA's call for what the network uses, but using Active Directory services has benefits in addition to a higher level of security than NTLM authentication.

Securing the Hardware

Although you might not be responsible for overall network security, you may be responsible for the physical hardware where SQL Server is installed.

Unless you have in a high security level requirement, the primary hardware defense is the physical security I mentioned earlier. Although not strictly hardware, you should always update the firmware on your system because many of these updates affect both performance and security. Before you update firmware, take a full backup of the system and create an exit plan. After any firmware update, record the change in your security plan as an audit item.

If you are in a high-security environment, you may have further considerations, such as reinforced walls that block electronic signals all the way up to TEMPEST equipment that blocks almost all electromagnetic wavelengths. Other measures you can take are removing or disabling all bootable media devices, disabling USB ports, and setting strong passwords on the system's BIOS.

Securing the Operating System

For a production installation of SQL Server 2005, always use a secure operating system such as Windows 2000 Server or higher. As you will learn later in this chapter, there is a significant security value in using even more recent operating systems such as Windows 2003 Server or higher, because SQL Server 2005 hooks into the enhanced security subsystems those operating systems provide.

You also want to use the same operating system in the testing and staging environments that you use in production. These systems often house databases containing at least a subset of production data. This brings back the central point of security: data awareness. To properly secure your data, you have to consider all the locations the where the data is accessed.

Always keep the operating system patched to the latest levels and monitor the Microsoft support site for security hot fixes. Of course, make sure that you test the patches on your testing environment before you put the patch into production. This is especially important if you have separate teams managing the operating system from the database software. I have seen DBAs fight a problem for several days only to discover the operating system team applied a patch that affected the system.

File System

As explained in Chapter 2, your data lives on the file system of the server. SQL Server 2005 has the best performance and security when you use the NTFS file system, and NTFS is required for advanced features such as the Encrypted File System (EFS). EFS is a feature of the Windows operating system and is not under the control of SQL Server 2005.

If you have an extremely strong protection scheme but leave the data or backups on drives that are accessible by the wrong people, your other security measures will not help. The bad guys can simply pick up the data files, attach them to another database server, and they have your entire system.

Service Accounts

The important thing to consider in the Windows accounts involved with controlling SQL Server 2005 services is that they should have no more rights and privileges than they absolutely need. This concept is called least privilege. In most cases, a Domain or Enterprise Administrator account is not needed to run SQL Server 2005. The security normally required for the SQL Server 2005 accounts lies somewhere between an "enhanced" user that can run services and a local administrator account. For clustering, replication, and enterprise administration features, you need domain accounts with local administration privileges on the server. If you create a normal set of Windows users and then assign those to the service accounts during the installation or by using the SQL Server Configuration Manager tool, SQL Server 2005 gives them all the rights they need.

Earlier in the book, I mentioned that I use the same accounts to start the various services that SQL Server 2005 uses, such as the accounts used to control the database engine and the SQL Server Agent. I also mentioned that I use a different set of accounts for each instance of SQL Server 2005 that I have installed. There are good security reasons for this practice. Having separate accounts allows you to control what privileges each service has. As you will learn in this chapter, the rights you grant these service accounts have a great deal to do with how secure your server is.

Each service account needs to be documented in your security plan and should be set with strong passwords (of high complexity and more than 14 characters in length). Do not use the service accounts for anything else, and do not allow anyone to log in with the account on the network.

You can change the accounts and startup types of the service accounts for SQL Server 2005 in many places, but the two you will probably use the most are the Services control MMC and the SQL Server Configuration Manager. I have left the Configuration Manager open from the previous example, and in Figure 4-2, I have clicked on the SQL Server 2005 Services item in the left pane.

Figure 4-2.

[View full size image]

If you have the tool open on your system, you will find many of the security settings that I explain in this chapter. Keep that tool open to refer to as we go along.

Securing SQL Server

I start with an overview of the security scheme for SQL Server 2005 with an analogy, and then I will spend the rest of the chapter explaining the details of each component described in the overview.

To help visualize the security scheme within SQL Server 2005, it is helpful to think of the server as a building, with rooms and objects within the rooms.

Endpoints, which are the first connections the server exposes, are similar to the doors of the building. There is usually more than one door to a building, and each can be secured separately. For instance, the public might be allowed into the building via one door but not another. The same holds true for SQL Server endpoints. Endpoints include the standard network connections that you do not control and HTTP endpoints that you can.

To enter any building, you need a key. Having this key gives you access to the building, but not to any particular room. This is similar to a SQL Server Login account. SQL Server logins allow you to connect to the server, but nothing within it. You can leverage the accounts within the Windows operating system or create all new accounts just for SQL Server 2005 to get this entrance key.

Once inside the building, people are given another key to access a particular room. The room is similar to a database, and the second key is similar to a database login. To access multiple rooms, a person needs a key for each one. To allow access to multiple databases, you need to create a database login for each one, too. So just like a building, you only need one SQL Server login, but for each database (like the rooms), you need a database login account as well. On my test system, I have a woodyb Server Login account, and a WoodyB account in many of the databases. These are tied to each other so that once I log in to the server, I do not have to provide further database authentication.

Finally, each room in a building contains objects, such as a chair, a computer, or a pencil. Just because a person has access to a room in the building does not automatically grant that person the right to use any of these objects. Even if they do have the right to use an object, the rights may be limited. In other words, someone might be allowed to sit in the chair, but that does not mean that person can take it.

You can compare the objects in the room to the tables, views, and other objects in a database. Database logins (principals) have to be granted access to the database objects individually, and even then you will need to specify what the principal is allowed to do with them.

Of course, someone might "own" an office in a building and therefore have the right to do anything he or she wants with the objects in that room, even granting others access to the things in the office. In the SQL Server analogy, you can allow SQL Sever 2005 principals to "own" a database, and then the principals can do anything they want with it. They can even grant access to the database to other SQL Server logins and allow them to access objects within their database.

Just like a building, the central concept for any secure computer system is that you should only expose what is absolutely necessary. This holds true for SQL Server, too. When you enable a connection method or feature that you are not using, it is easy to forget to monitor it for a security breach. For instance, in previous versions of SQL Server, an extended stored procedure called xp_cmdshell allowed principals to run operating system commands. Because this command runs under the security account that the Database Engine Service uses, some savvy principals ran a NET GROUP operating system command to add themselves to the Windows Administrators group.

SQL Server 2005 comes with features such as these turned off. You can see the current settings on your server by entering the command sp_configure in a query window.

You should run the sp_configure command periodically and record the output as part of your security audit to ensure the settings have not been compromised.

Functionality

The SQL Server 2005 Database Engine Service, Reporting Services, Integration Services, Analysis Services, and Notification Services all provide features that you should be familiar with from a security standpoint. I have already mentioned xp_cmdshell within the database engine, which is turned off by default.

Another feature to be aware of is the Common Language Runtime (CLR). This feature allows your developers to write managed code stored on the server and use them as database objects.

The developers write and compile a dynamic linked library (DLL) called an assembly in the language of their choice. If they have access to do so, they can enable the assembly directly from Visual Studio. If they are not using Visual Studio or it is not configured directly to your database, they can tell you where it is on the server, and you can include the assembly into the database for them.

This provides a great deal of power but opens several new vulnerabilities. Microsoft helps you mitigate those problems in three ways. The first is that CLR integration is disabled by default. Of course, if you need or want this functionality, you want to enable the feature. You can enable the CLR integration feature with the following query:

EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

You can also use a graphical tool that I show you in a moment. Either way, you need to have the ALTER SETTINGS permission, which is already enabled for the sysadmin and serveradmin server roles that I show you a little later. If you are logged on as a local administrator account on the server, you already have this permission.

After you have enabled the CLR, you can include an assembly into the database. The following code creates a new assembly in the database from a file called c:\temp\test.dll:

CREATE ASSEMBLY helloworld
FROM 'c:\temp\test.dll'
WITH PERMISSION_SET = SAFE

Your developers can then create stored procedures, principal-defined functions, principal-defined types, or triggers that reference this assembly. In the line beginning WITH PERMISSION_SET, you find the second method that you can use to secure the CLR.

You can set three CLR security levels. The first, as I have in this example, is SAFE. This level allows only access within SQL Server and does not allow access to the file system, the network, or the registry. This is the most secure security level. The next level is EXTERNAL_ACCESS, which allows access to the file system, the network, the registry, and system variables. This security level runs within the context of the Database Engine Service account but can also be set in code to impersonate another principal.

The least secure level is UNSAFE, which not only has all of the access from the other levels but can also call unmanaged code, which does not respect the memory space of SQL Server. This security level can even compromise the stability of SQL Server and should be used with extreme caution.

The third way that you can secure the CLR is up to the development staff. They can implement stringent standards to ensure that they write only those functions they need and that those functions do not do dangerous things. You and your developers should work together to ensure that nothing is enabled that is not needed. Because the configuration is basically invisible to the principals of the system, it is up to you to make sure that they have what they need, but no more.

Connections

Various applications make connections to SQL Server 2005. All use the same client and server libraries explained in Chapter 2.

Endpoints

Both the Database Engine and Analysis Services allow connections into SQL Server, as does Notification Services and Reporting Services. Recall from Chapter 2 that network transmissions flow from the client to the server and then back to the client. On the server side, the clients connect to various "endpoints," which allow access into SQL Server 2005. Each endpoint is associated with a protocol.

You can allow or deny the network protocol with the SQL Server Configuration Manager as described earlier, but even if the protocol is denied, SQL Server 2005 still creates an endpoint for it. When a connection attempts to use a denied protocol, the endpoint closes the connection.

Five endpoints are associated with each protocol type. The following chart describes the default endpoints and what each is used for.

You can create new endpoints with the CREATE ENDPOINT statement. You can change who has access to an endpoint with the ALTER ENDPOINT statement. By default, everyone has access to all endpoints with the exception of the Dedicated Admin Connection; only members of the sysadmin fixed server role have access to that.

Here is a script that will deny access to the WoodyB account to the default Named Pipes endpoint:

/* I have the brackets [] here because there are spaces in
the name of the endpoint.*/ 
USE master;
DENY CONNECT
ON ENDPOINT::[TSQL Named Pipes]
TO WoodyB;
GO

You can create a fine-grained security level to a database by only allowing certain accounts and groups connect through an endpoint. In the section where I describe SQL Serveer Logins, I show you a graphical method of managing account access to endpoints.

DMO and SMO

Two other connection methods used by client software are Distributed Management Objects (DMO) and the newer Server Management Objects (SMO). These application programming interfaces (APIs) still use endpoints but allow a different kind of access into SQL Server 2005.

DMO and SMO are primarily used for administrative tasks. In fact, the SQL Server Management Studio uses SMO. You can secure these APIs with the same endpoint, service, and protocol management as the other access methods I have described.

Once again, include any programs written using DMO or SMO in your security audit. In Chapter 5, "Monitoring and Optimization," I explain how you can determine which type of application is accessing your server.

Web and XML

Both the SQL Server 2005 Database Engine and Reporting Services allow access via HTTP. The SQL Server database engine also allows access to the server using SOAP calls.

To enable this type of access to your server, you need to create an endpoint. When you create the HTTP endpoint, you specify the type of requests the server will answer. These choices include SOAP, T-SQL, or Service Broker requests. Any application that supports XML calls over SOAP can send and receive XML data to SQL Server 2005. Unlike Web services from an HTML server, SQL Server 2005 does not allow anonymous connections, so you have less of a security concern than traditional Web servers.

There are quite a few parameters for creating an endpoint, which should be coordinated with your developers, such as specifying whether Web Service Description Language (WSDL) is respected by the server. I do not cover the syntax for creating this type of endpoint here because its configuration depends heavily on what the developers want to allow as a request and how they want to format the return values. We create a few in Chapter 7, "Notification Services and the Service Broker."

Your part will be working with developers to create the endpoints and then tracking those endpoints for your security audit.

For the audit, you can get a list of endpoints that your server is presenting with this query:

USE master
GO
/* Get the system endpoints */
SELECT *
FROM sys.endpoints;
GO
/* Get the http endpoints */
SELECT *
FROM sys.http_endpoints;
GO

Reporting Services works primarily through Web interfaces using HTTP and SOAP, so you need to secure those like any other Web-interfaced application. I cover the configuration of Reporting Services in greater detail in Chapter 10, "Analysis Services."

Database and SQL Mail

SQL Server can send maintenance and other notifications using various kinds of electronic mail accounts. You can also use mail to send queries to SQL Server 2005, and the server can send back the results as a reply.

There are two methods in SQL Server 2005 for using mail within the server. The first, called SQL Mail, is an older programming interface in SQL Server. It requires the server to have Microsoft Outlook installed, and for the account that starts the Database Engine Service to have a mail profile associated with it, because it uses the Microsoft Mail API (MAPI) for its connections. You are also limited with this method to 32-bit editions of SQL Server 2005, and if you want to send and receive attachments, you need to be a member of the sysadmins server role.

SQL Mail is not activated on your server by default. To use it, you must install an e-mail client first and then enable it using the Surface Area Configuration Manager tool, which I explain in a moment.

The principals must be in the same domain as the SQL Server 2005 database engine account to use SQL Mail so that they can authenticate properly. The SQL Mail interface will not allow the accounts to do more than they can from any other client tool. Make sure you are aware of the applications that are using this method and keep up with the service packs for Microsoft Outlook. Because applying a service pack has the possibility of breaking an application, you need to form a testing and exit strategy before you apply it.

With all these limitations and drawbacks, I mention SQL Mail only because you may have a legacy application that still uses this method of communication with SQL Server. You should migrate these applications as soon as possible to the more recent Database Mail functionality.

The Database Mail security implications involve controlling who has access to the mail profiles you create. To add an account to a profile, you use the sysmail_add_Principalprofile_sp stored procedure.

This script adds the WoodyB account to the primary profile I created in Chapter 3, "Maintenance and Automation," called MainProfile:

EXECUTE msdb.dbo.sysmail_add_Principalprofile_sp
 'WoodyB'
,'MainProfile'
GO

You can list the principals that have Database Mail privileges with the sysmail_help_profileaccount_sp stored procedure:

EXECUTE msdb.dbo.sysmail_help_profileaccount_sp
GO

Include this information in your security audit and, as always, do not grant access to mail to any account that does not need it. You should also be aware of programs that use this feature.

Remote Servers

Using the remote query and linked server features in SQL Server 2005, principals on other systems can reach into your server from theirs to transfer data back and forth using a single query. If the principals exist in both systems, you treat their accounts just as you do any local account. You also need to verify the security strategies used in their environment, because including these accounts widens your security boundary.

This is part of the reason for creating a documented security strategy and a security audit. You may have a need to access a remote server from your own, and the administrator there may want to verify your security. Having a plan enables you to provide a level of trust between the two environments.

If the principal does not exist in your system, the developers can set up the credentials in the query. This feature uses an OLE DB or ODBC call just as an ordinary application would. In this situation, you need to set up the same security as you would with any application.

There is an older mechanism for using another SQL Server system from a local server connection, called a remote server. For this to work, you access the properties of the server and then the Connections page. Ensure the Allow remote connections to this server box is on, which is the default. If you do not need this feature, uncheck this box.

Services

Most of the engines and features of SQL Server 2005 are controlled by services. There are services associated with the database engine, Reporting Services, Integration Services, Analysis Services, Notification Services, the SQL Server Agent, the Full Text Search Service, and the SQL Server Browser Service. Each of these is essentially a principal running software on your server.

You can control these services in various ways. The first way is to use the Windows Control Panel > Administrative Tools applet, where you can set the account and password the service will use, as well as the startup type (automatic, manual, or disabled) for the service. You can also use the SQL Server Configuration Manager to control these services. Your security strategy here is not to start any services that you do not need.

SQL Server Surface Area Configuration Manager

The surface area of SQL Server includes the things that expose themselves to the network and those services and functions that are called from the local system. You can control these surfaces using a tool called the SQL Server Surface Area Configuration (SAC) tool. This tool ships with all editions of SQL Server 2005 and changes the settings for a server. It works with multiple instances, clustering, and even virtual servers, and you can use it to copy a configuration from one server to another.

Locate the SAC tool on the Start menu under the Microsoft SQL Server 2005 menu in the Configuration Tools item, shown in Figure 4-3.

Figure 4-3.

[View full size image]

Once inside, you can work with services and connections or features. Selecting Surface Area Configuration for Services and Connections brings up the panel shown in Figure 4-4.

Figure 4-4.

[View full size image]

You can work with the system either by instance or by component across all instances. This allows you to set the remote connections for all instances at once or just on one instance.

Each feature you install on the server can be controlled here from a service or, in some cases, a remote connections standpoint. (Not all features have remote connections.)

Returning to the main panel of the SAC tool, select Surface Area Configuration for Features, as shown in Figure 4-5.

Figure 4-5.

[View full size image]

This panel shows two parts of SQL Server 2005 installed on this test system: the database engine and Analysis Services. Clicking each one of these objects allows you to select check boxes that enable or disable features associated with that function. You can also use the items you find here as entries for your security audit. If you have other features installed, such as Reporting Services, they show up here, too.

There is also a command-line version of this tool, called SAC.EXE. If you took the defaults during the installation of SQL Server 2005, it is located in the C:\Program Files\Microsoft SQL Server\90\Shared directory. There are several switches you can use to control what you would like to export or import. Use the command SAC /? at the operating system command prompt to see them all.

Let's look at a simple example using the SAC.EXE tool on the default instance that will transfer all of the settings from one server to another. After you have configured the SQL Server 2005 default instance the way you want, open a shell on the operating system of the first server and use the following command:

SAC out c:\temp\settings.xml

This creates an XML file in the c:\temp directory called settings.xml, which contains the settings you currently have on the first system. Copy that file to the c:\temp directory on the server where you want to deploy the settings and type the following command in an operating system shell on that system:

SAC in c:\temp\settings.xml

This assumes, of course, that you have a directory called c:\temp on both systems. You can also store this file as an attachment for your security audit.