Windows Authentication mode is the default and recommended authentication mode. It leverages local accounts, Active Directory user accounts, and groups when granting access to SQL Server. In this mode, you, as the database administrator, are given the opportunity to grant domain or local server users access to the database server without creating and managing a separate SQL Server account.

When Windows Authentication mode is used, Active Directory user accounts are subject to enterprise-wide policies enforced by the Active Directory domain such as complex passwords, password history, account lockouts, minimum password length, maximum password length, and the Kerberos protocol. If the server is not partaking in a domain, then the local security policies will govern the account’s password and lockout behavior.

SQL Server and Windows Authentication mode, which is regularly referred to as mixed mode authentication, uses either Active Directory user accounts or SQL Server accounts when validating access to SQL Server. Like SQL Server 2005, SQL Server 2008 continues to support a means to enforce password and lockout policies for SQL Server login accounts when using SQL Server authentication. These SQL Server policies include enforcing password complexity, password expiration, and account lockouts. As a reminder, this functionality, which was introduced with SQL Server 2005, was not available in SQL Server 2000, and this was a major security concern for most organizations and database administrators. Essentially, this security concern played a role in helping define Windows authentication as the recommended practice for managing authentication in the past. Today, SQL Server and Windows Authentication mode (Mixed Mode) may be able to successfully compete with Windows Authentication mode.

When you are aware of the authentication methods, the next step is choosing one to manage SQL Server security. Although SQL Server 2008 now can enforce policies, Windows Authentication mode is still the recommended alternative for controlling access to SQL Server because this mode carries added advantages; Active Directory provides an additional level of protection with the Kerberos protocol. As a result, the authentication mechanism is more mature and robust; therefore, administration can be reduced by leveraging Active Directory groups for role-based access to SQL Server. In addition, it is now possible to utilize Kerberos with all network protocols associated with SQL Server 2008. Network protocols include TCP, Named Pipes, Shared Memory, and Virtual Interface Adapter (VIA).

Nonetheless, this mode is not practical for everything out there. Mixed authentication is still required if there is a need to support legacy applications, application requires it or clients coming in from platforms other than Windows and a need for separation of duties exists.

It is common to find organizations where the SQL Server and Windows teams do not trust one another. Therefore, a clear separation of duties is required because SQL Server accounts are not managed via Active Directory.

Using Windows authentication is a more secure choice. However, if mixed mode authentication is required, you must make sure to leverage complex passwords and the SQL Server 2008 password and lockout policies to further bolster security.

To select or change the server authentication mode, follow these steps:

1.   In SQL Server Management, right-click on a desired SQL Server and then click Properties.

2.   On the Security page, as shown in Figure 8.1, select the desired server authentication mode under Server Authentication and then click OK.

FIGURE 8.1 Configuring SQL Server 2008 authentication modes.

3.   In the SQL Server Management Studio dialog box, click OK to acknowledge the need to restart SQL Server.

4.   In Object Explorer, right-click on a desired server and then click Restart. If the SQL Server Agent is running, it requires a restart also.

If SQL Server Authentication mode is used, a strong SA password should also be used. By default, the SA account has full administrative privileges over a SQL Server installation; therefore, in the event this account is compromised, the intruder will have full access to SQL Server and all databases.

In the past, it was common to find production SQL Server installations with a weak or blank SA password, which naturally increased the risk of security vulnerabilities and compromises. Microsoft introduced the idea of checking for blank SA passwords during the installation of Service Pack 4 on SQL Server 2000. Database administrators were further informed of the security vulnerabilities associated with maintaining a blank password; however, they were not forced to enter a password, which again left the account and server in a vulnerable state.

This situation is no longer an issue since SQL Server 2005.

If you use SQL Server authentication, you must enter a strong SA password; otherwise, you cannot continue with the SQL Server installation. A strong password for SQL Server must contain at least six characters and satisfy at least three of the following four criteria:

In addition, a strong password cannot use typical or commonplace words that everyone in the IT field is accustomed to, such as Password, Admin, Administrator, SA, or Sysadmin, and cannot use either the name of the user logged on to the computer or the computer name. These are all considered weak passwords.

Not allowing a weak or blank password reinforces the fact that Microsoft is serious about its ongoing Trustworthy Computing Initiative. In the past few years, Microsoft has invested significant time and resources in enhancing the security of each of its products, including SQL Server 2008.

To change or assign a strong SA password, do the following:

1.   In Object Explorer, first expand the Security folder and then the Logon folder. Right-click on the SA account and then click Properties.

2.   On the General page in the Login Properties dialog box, as shown in Figure 8.2, enter a new complex SA password, confirm it, and then click OK.

FIGURE 8.2 The SQL Server Login Properties dialog box for the SA account.

3.   Restart Microsoft SQL Server Services, including SQL Server Agent.

When attackers want to compromise a SQL Server, they don’t want to access the system as common users; they want complete control of the server so that they can gain access to all the data within it. Because most hackers already know the SA account exists, this makes hacking one step easier because this account would grant them complete control of the SQL Server if compromised. Similar to the way you use a Windows Administrator account, it is a best practice to rename and disable the SA account in SQL Server 2008 when running in mixed authentication mode. This technique increases security one step further because most hackers are familiar with the SA account and the rights associated with it.

The following syntax first disables the SA account and then renames it to something not easily identified. This example uses the name Ross-Mistry:

Also, it is a best practice to rename the account to something that is not related to an administrator, SA, or service, or is easily identifiable so that it’s not so obvious that this account was previously SA.

After you’ve installed SQL Server 2008, you should run the SQL Server Configuration Manager to harden the SQL Server implementation.

It is beneficial to maintain a slim and efficient installation of SQL Server 2008 by minimizing its footprint. This can be achieved by reducing the SQL Server system’s surface area by only installing necessary components and disabling unwanted services and features. These hardening techniques make SQL Server less susceptible to hackers and malicious attacks.

The Surface Area Configuration tool (SAC), which was included in SQL Server 2005, has been replaced with the Policy Based Management framework; therefore, Policy Based Management should be utilized in order to manage the SQL Server surface area.

The SQL Server Configuration Manager tool is a tool that you can use when hardening a SQL Server implementation. This tool should be used to configure and lock down unwanted services and features associated with a SQL Server implementation. Elements that can be configured include services, network configurations, native client configurations, client protocols, and aliases installed on a server.

To launch this tool, choose Start, All Programs, Microsoft SQL Server 2008, Configuration Tools, SQL Server Configuration Manager. The SQL Server Configuration Manager window is shown in Figure 8.3. The following nodes appear in the tool:

FIGURE 8.3 Managing services and connections, and disabling unnecessary SQL services.

Hardening SQL Server Ports with SQL Configuration Manager

A default installation of SQL Server 2008 uses TCP port 1433 for client requests and communications. These ports are well known in the industry, which makes them a common target for hackers. Therefore, it is a best practice to change the default ports associated with the SQL Server installation to put off hackers from port-scanning the default ports of the SQL Server installation. Unfortunately, SQL Server requires an open port for network communications. Therefore, this procedure prolongs the inevitable, as the used port will eventually be found.

Note

SQL Server 2008 does not automatically listen on port UDP 1434. Similar to SQL Server 2005, the task has been turned over to SQL Server Browser Services, which listens and resolves client connection requests made to the server. It also provides name and port resolution to clients when multiple instances are installed.

Follow these steps to change the default port using SQL Server Manager Configuration tools:

1.   Choose Start, All Programs, Microsoft SQL Server 2008, Configuration Tools, SQL Server Configuration Manager.

2.   Expand the SQL Server 2008 Network Configuration node and select Protocols for the SQL Server instance to be configured.

3.   In the right pane, right-click the protocol name TCP/IP and choose Properties.

4.   In the TCP/IP Properties dialog box, select the IP Addresses tab.

5.   There is a corresponding entry for every IP address assigned to the server. Clear the values for both the TCP Dynamic Ports and TCP Port for each IP address except for the IP addresses under IPAll.

6.   In the IPAll section for each instance, enter a new port that you want SQL Server 2008 to listen on, as shown in Figure 8.4.

FIGURE 8.4 Changing the default SQL Server ports.

7.   Click Apply and restart the SQL Server Services.

Note

The TCP Port for the default instance is automatically set to 1433 and the TCP Dynamic Ports setting is blank. The second and subsequent instances by default do not have the TCP Port set and have TCP Dynamic Ports set to 0. Using Dynamic Ports requires the use of the SQL Server Browser Service in order to direct incoming connections to the current port for that instance. This however makes it more difficult to setup firewalls etc as the port can change each restart.

Hiding a SQL Server Instance from Broadcasting Information

It is possible for SQL Server clients to browse the current infrastructure and retrieve a list of running SQL Server instances. The SQL Server Browser service enumerates SQL Server information on the network. When the SQL Server is found, the client obtains the server name and can connect to it if it has the appropriate credentials. This can present a large security threat to organizations because sensitive production data can be compromised. Note, that the SQL Server Browser service is required when running multiple instances on a single server. As indicated earlier, the Browser Service directs incoming connections to specific ports associated to an instance.

Organizations don’t need to worry—there is help for this type of situation. The SQL Server Configuration Manager tool can be used to hide an instance of SQL Server. This is typically a best practice for mission-critical production database servers that host sensitive data because there is no need to broadcast this information. Clients and applications still can connect to SQL Server if needed; however, they need to know the SQL Server name, protocol, and which port the instance is using to connect.

To hide a SQL Server instance with SQL Server Configuration Manager, follow these steps:

1.   Choose Start, All Programs, Microsoft SQL Server 2008, Configuration Tools, SQL Server Configuration Manager.

2.   Expand the SQL Server 2008 Network Configuration node and select Protocols for the SQL Server instance to be configured.

3.   Right-click Protocols for [ServerInstance Name] and then choose Properties.

4.   In the Hide Instance box on the Protocols for [ServerInstance Name] Properties page, shown in Figure 8.5, select Yes.

FIGURE 8.5 Hiding a SQL Server instance.

5.   Click OK and restart the services for the change to take effect.

The most impressive hardening tool and useful addition to the Windows Server operating system has to be the Security Configuration Wizard (SCW). SCW was first introduced as an add-in with Windows Server 2003 Service Pack 1; however, it is now included out of the box with Windows Server 2008. SCW is an attack-surface reduction tool that allows you to completely lock down a server, except for the particular services that it requires to perform specific duties. The role-based security policies are predefined and assist you by configuring services, network security, auditing, Registry settings, and more. This way, a WINS server responds only to WINS requests, a DNS server has only DNS enabled, and a SQL Server responds only to SQL requests. Windows Server 2008 continues to deliver this type of long-sought-after functionality.

SCW allows you to build custom role-based templates that can be exported to additional servers, thus streamlining the security process when setting up multiple systems. In addition, current security templates can be imported into SCW so that existing intelligence can be maintained. The SCW included with Windows Server 2008 includes new improved features and functionality such as more server role configurations and security settings out of the box, the possibility to disable unneeded services based on the server role, the capability to establish restrictive audit policies, advanced firewall configurations, and the power to transform a SCW policy into a Group Policy object (GPO) and link it to an Organizational Unit (OU) for centralized SQL Server infrastructure management when using Active Directory Domain Services (AD DS).

The advantages to using the SCW service on SQL Server are immediately identifiable. SQL Server, because it houses sensitive data and is often indirectly exposed to the Internet by web service applications, is vulnerable to attack and therefore should have all unnecessary services and ports shut down. A properly configured firewall normally drops this type of activity, and although the preceding section focused on minimizing surface attacks, it is always a good idea to put in an additional layer of security for good measure.

The Security Configuration Wizard can be run to lock down SQL Server based on a SQL Server role-based template, therefore, only the bare necessities required will be operational. This includes SQL access, web and ASP-related web access, and any other access methods required for the server. In addition, network security, port configuration, and Registry settings can be configured. Each SQL Server implementation differs, so it is important to run the wizard on a prototype to determine what settings are right for each individual SQL Server.

To launch the Security Configuration Wizard, choose Start, All Programs, Administrative Tools, Security Configuration Wizard. Use the wizard to create a role-based SQL Server security policy that locks down unnecessary services, network security, ports, Registry settings, and audit policies.

Like Windows Server 2008, Microsoft SQL Server 2008 also requires the latest service packs and updates to reduce known security vulnerabilities. Microsoft offers an intuitive, free downloadable tool called the Microsoft Baseline Security Analyzer (MBSA). This tool identifies common security vulnerabilities on SQL Servers by identifying incorrect configurations and missing security patches for SQL Server, Windows Server 2008, and Internet Information Services (IIS).

Not only MBSA can scan a single SQL Server, but it can also scan multiple instances of SQL Server. The MBSA SQL Server scan works by detecting and displaying SQL Server vulnerabilities, including the following: members of the sysadmin role, weak or blank SQL Server local accounts and SA passwords, SQL Server Authentication mode, SQL Server on a domain controller, and missing service packs and critical fixes.

Before installing MBSA, you should become acquainted with some specific Microsoft system requirements for the installation. Being familiar with the following list will help you on your way to a successful installation:

Scanning for Security Vulnerabilities with MBSA

MBSA can scan a single computer or a range of computers, or all computers in a domain based on an IP address, range of IP addresses, or computer name. The security scanner can identify known security vulnerabilities on several Microsoft technologies such as Windows, IIS, or SQL Server.

To scan SQL Server for known SQL or Windows vulnerabilities, weak passwords, and security updates, follow these steps:

1.   Choose Start, All Programs, Microsoft Baseline Security Analyzer.

2.   Click on Scan a Computer to pick the system to scan. You can scan more than one computer here by entering either a valid IP address range or domain name.

3.   In the next window, which is Which Computer Do You Want to Scan?, enter the computer name or IP address of the desired SQL Server. Select all options you want, as shown in Figure 8.6, and click Start Scan.

FIGURE 8.6 MBSA computer scan and options screen.

Viewing MBSA Security Reports

A separate security report is generated for the desired SQL Server on completion of the computer scan. This report is generated regardless of whether a local or remote scan is conducted. In addition, scanned reports are stored for future viewing on the same computer where the Microsoft Baseline Security Analyzer is installed.

The information yielded in the MBSA security reports is quite intuitive and addresses each vulnerability detected. For example, if MBSA detects a missing SQL Server service pack, Windows patch, or hot fix, it displays the vulnerability in the Security Update Scan section and provides the location that focuses on the fix.

In the security report example shown in Figure 8.7, note that each section scanned has a score associated with it. An end user or administrator can easily browse each section identifying known security vulnerabilities, verifying what was scanned, checking the results, and analyzing how to correct anomalies that MBSA detected.

FIGURE 8.7 Viewing a Microsoft Baseline Security Analyzer vulnerability report.

Another tool that is typically a database administrator’s best friend is the much-awaited SQL Server 2008 Best Practice Analyzer (BPA) tool. The BPA gathers data from Microsoft Windows and SQL Server configuration settings. The BPA is a database management tool that uses a predefined list of SQL Server 2008 recommendations and best practices to determine whether there are potential issues in the database environment. The BPA also covers security hardening best practices.

It is a best practice to configure a service account based on the principle of least privilege. According to the principle of least privilege, SQL Server service accounts should be granted the least number of rights and permissions to conduct a specific task. Based on this recommendation, you should not grant a service account unnecessary elevated privileges such as domain administrator, enterprise administrator, or local administrator privileges. This enhances the protection of data and functionality from faults. Also, you should recognize that these highly elevated privileges are really not required. In fact, gone are the days when the SQL Server service accounts required domain administrator or local administrator privileges.

For isolation purposes, a separate account should be created for each SQL Server instance and component being installed. Therefore, if the service account is compromised, only the one instance or component associated with the service account is breached. For example, suppose a bank is running 100 SQL Server instances and each instance maintains financial information. If one service account is used for all these instances, all 100 instances would be compromised in the event of a service account breach. This type of situation could be disastrous for a bank, especially with today’s laws and regulatory requirements.

The need to create and manage more than one service account definitely increases administration and can be monotonous; however, it is a best practice to isolate each instance or component. One other notable benefit of isolation is witnessed with the amount of control organizations achieve through it. Organizations can grant specific permissions to one service account without elevating permissions to another service account that does not need elevated permissions.

The following types of service accounts are available to choose from:

The question that always surfaces regarding service accounts is, “Which service account should be used with implementing SQL Server 2008?” The answer depends on your intended use of the service account and the relationship it will have to the server and network.

Services that run as the local service account access network resources with no credentials. As a result, this account should not be used if you want the services to interact with other network resources.

If you are looking for a service account that grants limited privileges like the local service account but also runs services that can interrelate with other services on the network infrastructure, you should consider using a network service account. This account uses the credentials of the computer account to gain access to the network. It is not recommended that you use this account for either the SQL Server service or the SQL Server Agent service account.

Consideration should also be given to the domain user account if its services will interact with other services on the network infrastructure. If you also want to perform certain activities including replication, remote procedure calls, or network drive backups, a domain user account is preferred over a network service account because only this account allows server-to-server activity. One point to keep in mind when using a domain account is that it must be authenticated on a domain controller.

The local system account is not recommended for use for the SQL Server service or SQL Server Agent services. The reason is that it is a best practice to configure a service so that it runs effectively with the least number of privileges granted. The local system account is a highly privileged account, which means it should be used very carefully. In addition, it probably has privileges that neither SQL Server Agent services nor SQL Server services actually require.

Typically, server administrators use the Services Snap-in component included with Windows Server 2008 Server Manager or the Administrative tools to make changes to Windows Services. There are serious negative ramifications if SQL Server service accounts are changed using this tool. SQL Server service accounts require special Registry settings, NTFS file system permissions, and Windows user rights to be set, which the Windows tool does not address, thus causing a SQL Server outage. Fortunately, these additional permission requirements can be updated automatically if you use SQL Server native configuration tools such as the SQL Server Configuration Manager or SQL Server Surface Area Configuration. Therefore, it is a best practice to use the native SQL Server configuration tools when making changes to SQL Server service accounts; changes should not be made using the Windows Server 2008 Services tool.

Follow these steps to change the user account, including credentials for a SQL Server service such as the SQL Server Agent, using the SQL Server Configuration Manager:

1.   Choose Start, All Programs, Microsoft SQL Server 2008, Configuration Tools, SQL Server Configuration Manager.

2.   Select the SQL Server Services node.

3.   In the right pane, double-click on the SQL Server Agent Service.

4.   In the SQL Server Agent box, enter a new service account name and password.

5.   Confirm the password by retyping it, as shown in Figure 8.8, and click Apply.

FIGURE 8.8 Changing the service account credentials.

6.   Accept the message to restart the services and click OK.

The SQL Server Agent now uses the new service account credentials for authentication. In addition, Registry settings, NTFS permissions, and Windows rights are updated automatically.

In addition to the patches that are installed as part of a SQL Server 2008 service pack, security updates and patches are constantly being released by Microsoft. It is advantageous to install these updates made available for SQL Server and the operating system. These patches can be manually downloaded and installed, or they can be automatically applied by using Microsoft Update.

It is a best practice to install critical fixes for both SQL Server and the operating system when they are released. In addition, major service packs and security rollups should be installed in a timely manner. All patches should be tested in a prototype lab before being installed in production, and it is recommended that you conduct a full backup of the system prior to the installation of the patches.

To configure security login auditing for both failed and successful logins, follow these steps:

1.   In SQL Server Management Studio, right-click on a desired SQL Server and then click Properties.

2.   On the Security page, as shown in Figure 8.9, under Login Auditing, select the desired auditing criteria option button, such as Both Failed and Successful Logins, and then click OK.

FIGURE 8.9 Configuring security auditing to both failed and successful logins.

3.   Restart the SQL Server Database Engine and SQL Server Agent to make the auditing changes effective.

SQL Server systems are strong candidates to fall subject to the rules and regulations governed by regulatory requirements. SQL Server is typically a repository for organizations’ and customers’ data that tends to be both mission-critical and sensitive if it falls into the wrong hands.

One of the latest compliance rules some organizations are subject to includes the ability to successfully log events in a central repository and produce reports of all activity being captured. Microsoft understands the push toward better logging and auditing capabilities and has introduced new features and functionality in SQL Server 2008 that facilitate stronger auditing for compliance reasons.

By introducing two new objects, Audit object and Audit Specification object, organizations can now log every SQL Server action to the Windows Application Log, Windows Security Log, or to a file. In addition, when using Windows Server 2008 or System Center 2007 Operations Manager, it is possible to create a central repository for all events collected across many SQL Server systems within the enterprise.

In the past, many database administrators in the industry were concerned about the BUILTINAdministrators Windows group having sysadmin privileges by default over a SQL Server instance. Some people believe that this situation was one of the biggest out-of-the-box security flaws for SQL Server. The reason is that all local Windows administrators, including domain administrators, are given full control over SQL Server because they are part of the BUILTINAdministrators Windows group. This led to a best practice to remove the BUILTINAdministrators group to address this situation. Doing this hardens the SQL Server installation.

Microsoft realized that this was a major security flaw, and with SQL Server 2008 BETA versions the local Windows Group BUILTINAdministrator is no longer included in the SQL Server sysadmin server role. When the official RTM version released, Microsoft removed the group outright. Therefore, it is up to you to decide whether there is still a need to add this group, if desired.

If the group existed from previous versions of SQL Server or using BETA code, the following Transact-SQL (TSQL) syntax removes the BUILTINAdministrators Windows Group from a SQL Server instance. If you decide to run this syntax, you should execute it on each SQL Server instance installed in the organization:

Now that the default SQL Server ports have been changed according to the instructions in the previous section, the next step is to enable a firewall that will filter out unwanted traffic and allow connections only to the SQL Server designated from within the organization’s infrastructure. The Windows firewall included with Windows Server 2008 should be sufficient. However, if more advanced firewall features are sought, a full-fledged hardware-based firewall or software-based firewall should be used, such as Internet Security and Acceleration (ISA) Server 2006.

The following table summarizes the default ports utilized by common SQL Server components.

Using the Integrated Windows Server 2008 Firewall with Advanced Security

Windows Server 2008 includes a vastly improved integrated firewall that is turned on by default in all installations of the product. The firewall, administered from a Microsoft Management Console (MMC) snap-in shown in Figure 8.10 gives unprecedented control and security to a server. It can be accessed by choosing Start, All Programs, Administrative Tools, Windows Firewall with Advanced Security.

FIGURE 8.10 Using the integrated Windows Firewall with Advanced Security.

The firewall is fully integrated with the Server Manager utility and the Server Roles Wizard. Both the Server Manager utility and the Server Roles Wizard are new management tools included with Windows Server 2008. For example, if an DBA runs the Server Roles Wizard and chooses to make the server a file server or a domain controller, only then are those ports and protocols that are required for file server or domain controller access opened on the server. Unfortunately, this is not the case with SQL Server, and firewall rules must be created and configured manually.

Note

It is instinctive for most DBAs or Windows administrators to disable software firewalls on servers, as they have caused problems with functionality in the past. This is not recommended in Windows Server 2008, however, as the product is tightly integrated with its firewall, and the firewall provides for a much greater degree of security than previous versions of Windows Server provided.

Creating Rules for SQL Server 2008 on the Windows Server 2008 Firewall

In certain cases, when a third-party application is not integrated with Server Manager, or when needing to open specific individual ports, it may become necessary to create firewall rules for individual services to run properly. This is the case when working with SQL Server 2008. Both inbound rules addressing traffic to the server, and outbound rules, addressing how the server can communicate out, can be created. Rules can be created based on the following factors:

   Program— A rule can be created that allows a specific program executable access. For example, you can specify that the C:Program FilesCustom Programmyprogram.exe file has full outbound access when running. The Windows Firewall program will then allow any type of connections made by that program full access. This can be useful in scenarios when a specific application server uses multiple varied ports, but the overall security that the firewall provides is still desired.

   Port— Entering a traditional UDP or TCP port into the Add Rules Wizard is supported. This covers traditional scenarios such as “We need to open port 1433 on the SQL Server system.”

   Predefined— Windows Server also has built-in, predefined rules, such as those that allow AD DS, DFS, BITS, HTTP, and many more. The advantage to using a predefined rule is that Microsoft has done all the legwork in advance, and it becomes much easier to allow a specific service.

   Custom— The creation of custom rule types not covered in the other categories is also supported.

When configuring the Windows firewall, you can either create an exception for SQL Server based on the instance’s port number or by adding the path to the SQL Server program. The default instance of SQL Server uses port 1433; however, ports are assigned dynamically when running more than one instance. Therefore, it is a best practice to utilize the path of the program.

Follow these steps to create a SQL Server exception on the Windows firewall by adding the path of the SQL Server program:

1.   Open the Windows Firewall MMC Console (Start, All Programs, Administrative Tools, Windows Firewall with Advanced Security).

Note

A message box will be displayed if the Windows Firewall/Internet Connection Sharing (ICS) service is not running. Click Yes to activate the service and the firewall. In addition, another warning will appear indicating that your computer is not protected and you must turn on Windows Firewall if the firewall setting is configured to Off.

2.   Click on the Inbound Rules node in the node pane.

3.   In the Action Pane, click the link labeled New Rule.

4.   In the Rule Type dialog box, shown in Figure 8.11, select to create a rule based on Add Program and click Next to continue.

FIGURE 8.11 Setting Windows firewall options.

5.   Select the SQL Server instance from the program list. If the program is not available in the list, click Browse to search for it and provide the path for the appropriate SQL Server instance, for example, D:Program FilesMicrosoft SQL ServerMSSQL10.Instance01MSSQLBinnsqlservr.exe, as illustrated in Figure 8.12. Then click Open.

FIGURE 8.12 Setting the Windows firewall exception screen.

Note

Microsoft SQL Server provides an instance ID for every SQL Server instance installed on a server. Typically, the ID is incremented by 1 when more than one instance is installed on a server. Use the SQL Server Configuration Manager tool to obtain the instance ID and installation path of a SQL Server instance. To find it, double-click the server name, and the Advanced tab displays the instance ID and installation path to the SQL Server instance.

6.   In the next window, on the Action tab, specify the action that should be taken when a connection matches the condition of the rule. The options are Allow the Connection, Allow the Connection If It Is Secure, and Block the Connection. Select Allow the Connection as displayed in Figure 8.13 and then click Next.

FIGURE 8.13 Specifying the connection action.

7.   On the Profile page, shown in Figure 8.14, specify the desired profile box. This allows a DBA to specify that a rule only applies when connected to specific networks. Click Next to continue.

FIGURE 8.14 Specifying the profiles to which the rule applies.

8.   Enter a descriptive name for the rule and click Finish.

9.   Repeat these steps for every SQL Server instance or SQL component that requires an exception, such as Analysis Services, Integration Services, and so on.

10.   Test the connection to the SQL Server from a desired client to validate both that the firewall is enabled and the appropriate exceptions were created.

Note

Create an exception for the SQL Server Browser service if there is a need to broadcast SQL Server information to clients over the network. Otherwise, SQL Server clients must know the names and ports of the clients when connecting.

Using the Integrated Windows Firewall is no longer just a good idea—it’s a vital part of the security of the product. The addition of the ability to define rules based on factors such as scope, profile, IPSec status, and the like further positions the Server OS as one with high levels of integrated security.