Extended Protection available in SQL Server 2008 R2

By: Tim Cullen | Updated: 2010-09-01 | Comments | Related: > Security

Problem

In this world of ever-increasing and evolving computer exploitation, the company I work for is looking for additional methods to employ so that our applications and data are secure. We use Internet Protocol Security (IPSec) within the trusted domains, but use the Negotiate Security Support Provider for our web applications. What other security methods can we employ with SQL Server?

Solution

Microsoft is always working on fixes and enhancements to further secure the communications between systems. One of these methods ships with SQL Server 2008 R2: Extended Protection when connecting to a SQL Server 2008 R2 instance using NTLM authentication. First, let's talk about what NTLM authentication is and how it works.

NTLM Authentication

NTLM authentication is actually the former name of the protocol; it is currently called Integrated Windows Authentication and is also known as Windows NT Challenge Response. Integrated Windows Authentication has two modes of authentication: interactive and non-interactive. With interactive authentication there is a client (user or system) and a domain controller that houses the user's logon information, whereas with non-interactive authentication there are usually three systems involved: a client, a server, and a domain controller. Also, the credentials are reused when a user that is already logged on requests additional resources.

The following steps are performed during authentication using NTLM:

The client provides the required domain name, username, and password. Prior to sending the password, however, the client machine performs a one-way hash of the password and discards the plain text version (this step only occurs during interactive authentication)
The username is sent in clear text
The server generates a random 16-byte number, called a nonce, and sends it to the client. Nonces are used to avoid authentication relay attacks. This is called the Challenge
The client encrypts the nonce with the password hash and sends it back to the server. This is called the Response
The server sends the username, the challenge, and the response to the domain controller where the Security Accounts Manager (SAM) database (and the user's credential) resides
The domain controller retrieves the hash of the password stored in the SAM database and uses it to encrypt the challenge. If the information is the same as the response received from the client then the client gains access to the requested resources.

Sounds pretty secure, huh? Well, certain hacking techniques, like a Man-in-the-Middle Attack (which is a derivative of a replay attack) can be used to intercept credentials.

How Extended Protection Works

Extended Protection can provide additional safeguards against authentication relay attacks, which come in two flavors. A "luring attack" is where a person is lured into opening an application that can be used for malicious purposes. A "spoofing attack" is where the hacker successfully portrays the trusted source. An example of a spoofing attack is when a "phishing email" is sent to a client purporting to be from their bank when in actuality the link in the email is directed to their collection site for credentials.

Extended Protection works by requiring additional bindings when attempting a connection. The first binding, called Service Binding, requires that the client send a signed Service Principal Name (SPN) of the SQL Server instance to which the client wants to connect. During the authentication process the SQL Server instance validates the presented SPN against its own. Since the "middle man" has no knowledge of the SPN validation and thus authentication fails. The second binding, called Channel Binding, establishes a secure channel between the client and the SQL Server instance. The service then compares the client's channel binding token (CBT) with its own token. Channel b=Binding generates additional resource and network overhead because it uses Transport Layer Security (TLS) for the duration of the session.

How to Enable Extended Protection

The steps required to enable Extended Protection are located in the SQL Server Configuration Manager, which is located in the Program Files\Microsoft SQL Server 2008 R2\Configuration Tools folder:

The first step is to force protocol encryption for the SQL Native Client. You can get to the configuration by right-clicking the SQL Native Client 10.0 Configuration (top left in the picture below), choosing Properties, and changing the Force Protocol Encryption dropdown selection:

The next step is to set both the accepted NTLM Service Principal Names and set the Extended Protection level. Open the protocol properties for the SQL Server 2008 R2 instance by right-clicking the protocols for [Instance Name] and choosing Properties. Then select the Advanced tab:

The SPN's are entered with semi-colons in between and there is a limit of 2048 characters. As for the Extended Protection levels, you can choose from one of the following:

Off: Extended Protection is not enabled
Allowed: Extended Protection is required when a client's operating system supports it. If the client's operating system does not support it then the connection is made without Extended Protection
Required: Extended Protection is required on all connections, so operating systems that do not support it will not be able to connect

for the Extended Protection levels, you can choose from one of the following

Once the values have been modified they will not go into effect until the Database Engine is restarted. Additional steps may be required on other application servers, so check the documentation on Microsoft's TechNet and Developer Network for additional information.

Next Steps

Extended Protection is only available with the SQL Server Native Client in SQL Server 2008 R2
Read information on Integrated Windows Authentication
Read information on Integrated Windows Authentication with Extended Protection
Review information on Credential Relaying Attacks on Integrated Windows Authentication
Read the introduction/support article on Extended Protection for Authentication
Read the Microsoft Security Advisory on Extended Protection for Authentication
Find out how to Connect to the Database Engine Using Extended Protection
Review information on how to register a Service Principal Name
Stay tuned for more tips on http://www.mssqltips.com/

About the author

Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2010-09-01