SQL Server's Extended Protection

Security plans can change depening on whether you're looking to implement in an SMB, multi-server, or enterprise environment..

I realized after reviewing the past few posts that I omitted a new piece of functionality introduced in SQL Server 2008 R2. This feature, known as Extended Protection for Authentication (Extended Protection, for short), helps secure connections to a SQL Server 2008 R2 server. This feature is only available when running SQL Server 2008 R2 on Windows 7 or Windows Server 2008 R2 (though there are some service packs that introduce the feature to older OSes).

Extended Protection essentially protects against a very specific type of attack where legitimate client credentials are used to connect to a service or server from an unsecure location. For example, a client can be fooled into connecting to a service that they believe is valid, but actually hosts a malicious site/service/etc. Then the attacker uses the credentials passed to it from the client to initiate a new connection to the real service/server, and thus has access to that resource.

Extended Protection uses service binding and channel binding to help alleviate this problem.

Service binding uses Service Principal Names to help secure the connection; attackers trying to relay the credentials will only be able to submit an invalid SPN from their machine, not a valid SPN from the original client machine.

Channel binding takes it a step further by requiring a channel binding token that is passed back and forth between the legitimate client and the server. Service binding solves only those attacks where the client was lured into voluntarily connecting directly to the attacker; Channel Binding solves that scenario as well as those scenarios where a service was compromised to redirect the authentication AFTER a valid service was connected to.

Unfortunately, Channel Binding incurs quite a bit of performance overhead, because it uses TLS encryption for all traffic, whereas Service Binding only incurs the one-time cost of validating the SPN. So, choose wisely.

All that said, setting up Extended Protection is pretty simple, but it does involve making changes in Windows. There are two registry keys that must be set (if they don't already exist, they can be created):

  1. HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\LSA\
    SuppressExtendedProtection
    – This must be set to “0”.
  2. HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
    \LmCompatibilityLevel
    – This must be set to “3”.

Once done, reboot your computer, and Windows will be configured correctly.

In terms of SQL Server, clients must be using the SQL Server Native Client in SQL Server 2008 R2. If not, Extended Protection will not be used.

To configure Extended Protection in SQL Server 2008 R2, follow these steps:

  1. Open the SQL Server Configuration Manager.
  2. Expand Network Configuration.
  3. Right-click on Protocols for <Your Instance Name>. Click Properties.
  4. Click on the Advanced Tab.

Now you can set the Extended Protection settings as needed; here are your choices:

  • Force Encryption will need to be set to ON for Channel Binding; otherwise Service Binding is used.
  • Extended Protection can be set to OFF, Allowed, and Required. Obviously, the most secure state is Required, but your applications must be ready for this. Allowed means that connections from clients that support Extended Protection will be encrypted, while those that don't support it won't be.
  • Accepted NTLM SPNs: This is for servers that have more than one SPN. You can enter the SPNs (delimited by semi-colons) so that different connections specifying different SPNs will work.

Then, simply restart your SQL Server; and voila! Extended Protection is ON!

Downloads for all of the updates necessary to make this work on machines that aren't running Windows 7 or WIndows Server 2008 R2 can be found here. Test any of these settings changes extensively in your test environment (you have one, right?). This isn't a trivial change in terms of the actual impact on applications; be sure you're ready.

Good luck, and have fun!

About the Author

Joshua Jones is co-author of A Developer's Guide to Data Modeling for SQL Server: Covering SQL Server 2005 and 2008 (Addison-Wesley Professional, 2008) and is a principal with Denver-based Consortio Services LLC.