Tip

For better SQL Server security, get Extended Protection

Extended Protection is a new feature in the Windows operating system as well as the SQL Server database engine. It was designed to enhance security features of the Integrated Windows Authentication process, which SQL Server employs when using domain credentials to authenticate against the database engine, bolstering SQL Server security

On the SQL Server side, Extended Protection was introduced in the SQL Server 2008 R2 release. On the Windows side, it was introduced in the Windows 2008 R2 release; however,

    Requires Free Membership to View

patches are available for Windows 2008, 2003, Vista and XP. To use Extended Protection, the client connecting to the SQL Server 2008 R2 database engine must be using the SQL Server 2008 R2 Native Client. Older down-level clients do not support Extended Protection.

The Extended Protection system provides SQL Server security by guarding against two types of attacks, and it does that using one of two methods -- service binding or channel binding.

Service binding is used to prevent a luring attack. This is a man-in-the-middle attack in which connections are redirected, usually via DNS spoofing, to a compromised server that captures the authentication information, which is passed on by the client. Service binding protects against this by requiring that the client software send the service principle name (SPN) to which the client is attempting to connect. Part of the response from the server back to the client validates that the client is connecting to the server hosting the SPN and not a middle machine. If the validation comes back successfully, then the connection is allowed; otherwise, it is terminated.

Channel binding protects against two attack vectors -- luring attacks and spoofing attacks. Channel binding uses Transport Layer Security (TLS) encryption to encrypt all the traffic between the client and the server. After the TLS-protected connection is made, the client requests the channel binding token (CBT) from the server. The client verifies that the CBT it receives from the server matches the server to which the client is attempting to connect.

Because of the differences in the implementation between service binding and channel binding, channel binding puts a much higher CPU load requirement on the client and the server. This increased CPU load is due to the TLS protection, which encrypts all network traffic between the clients and the server.

Extended Protection is available for all editions of the Microsoft SQL Server 2008 R2 engine. Extended Protection is easy to configure, as it is done via the Microsoft SQL Server 2008 R2 Configuration Manager.

In Microsoft SQL Server 2008 R2 Configuration Manager, select “SQL Server Services” in the menu on the left. On the right, double-click on the SQL Server 2008 R2 instance you wish to configure. Select “Advanced” in the pop-up menu, and choose the protection level you want to enable. The setting options are “Off,” “Allowed” and “Required.”  Setting to “Off” will disable Extended Protection. The “Allowed” setting will accept connections from the client, whether or not they are covered by Extended Protection. “Required” will reject connections not covered by Extended Protection. If you enable Extended Protection, service binding will kick in by default. If you want to use channel binding, enable Extended Protection as well as SSL connections for the SQL Server instance. You cannot use channel binding if SSL encryption for the SQL Server connection is not enabled. And that means service binding is the only available option.

As Extended Protection requires that the SPNs to which the client is connecting are known by the server, you may need to specify additional SPNs for the server so that domain-name-server aliases or other network names are correctly registered as SPNs. This is done on the same screen as which you configure Extended Protection; fill out the “Accepted NTLM SPNs” field with a semicolon separated list of SPNs available on the network. For example, if you wanted the SQL Server to accept the SPNs for sql1.contoso.local and sql2.contoso.local, the value would be “MSSQLSvc/sql1.contoso.local;MSSQLSvc/ sql1.contoso.local”.

After changing the Extended Protection or SPN settings for a Microsoft SQL Server instance, the instance needs to be restarted.

SPNs are unique service names within a specific Windows Active Directory domain.  An SPN refers to a specific service within the Active Directory domain; however, multiple SPNs can all reference a single service. SPNs are registered to a single service, but they are also tied to the account that runs that service. If the service that runs the service is changed, then the SPN must be recreated to reference the new service account.

Microsoft SQL Server will automatically create an SPN within the domain every time the service is started. But this SPN is automatically created for the physical name of the server only; any additional names the server is known by will need to have SPNs manually created to point to the Windows account and the SQL Server service.  These SPNs are created using the setspn command line application.

The syntax to create an SPN is <service class>/<host>:<port>/<service name>. When creating an SPN the port and service name are optional values, and the service class and host values are required. The full command would look something like “setspn -A MSSQLSvc/mydatabase.contoso.local:1433/CONTOSO\sqlserver”.

Extended Protection and SPNs are pretty advanced concepts, but they offer advanced SQL Server security, letting users know that the sever to which they are connecting is the  server they think it is.

ABOUT THE AUTHOR
Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's over 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He currently holds several Microsoft certifications related to SQL Server and is a Microsoft MVP. Check out his blog at SQL Server with Mr. Denny.

This was first published in November 2010

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.