For better SQL Server security, get Extended Protection
Denny Cherry, Contributor
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, 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
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
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
Dig Deeper
-
People who read this also read...
This was first published in November 2010
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.
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.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation