In this chapter we will cover the following:
Choosing an account for running SQL Server
Managing service SIDs
Using a managed service account
Using a virtual service account
Encrypting the session with SSL
Configuring a firewall for SQL Server access
Disabling SQL Server Browser
Stopping unused services
Using Kerberos for authentication
Using extended protection to prevent authentication relay attacks
Using transparent database encryption
Securing linked server access
Configuring endpoint security
Limiting functionalities –
xp_cmdshell
,OPENROWSET
SQL Server 2012 is the new major release of Microsoft's enterprise-class Relational Database Management System (RDBMS). It allows you to store and manage what is most critical in your company: your data. If something in your business is stolen or lost—machine or software—it could have a big impact, but probably wouldn't be catastrophic. However, if your data disappears, it could very well get you out of business. As a Database Administrator (DBA), you need to be very serious about security, and SQL Server has a great number of features and options to protect your databases. This book is designed to address each of them practically.
The first step to secure SQL Server is, of course, when you install it. Even if most of the choices you make during the installation process can be changed later in the server properties, here we will see some options that are better taken care of when you first configure your server. For example, choosing the correct Windows account to run the SQL Server services should be done right the first time, to avoid restarting your service later. We will also discuss new security offerings of the Microsoft Windows Server 2008 R2 operation system, such as managed accounts and virtual service accounts. This book is written under the assumption that you have installed SQL Server on the Microsoft Windows Server 2008 R2 operating system. In other versions, the location of the options we will see might slightly differ.
SQL Server is a Windows service, a process started by the Windows operating system running under the privileges of a user or a system account. Choosing the right account is important for security, because clients accessing SQL Server with a database connection could gain access to the underlying Windows OS under some circumstances, or if a security hole should be found in the SQL Server code.
The first time you can choose the service accounts is during the installation process. To complete the installation, perform the following steps:
1. Open the Server Configuration page in the assistant.
2. When it opens, you will see the Service Accounts tab.
3. If your SQL Server instance is already installed, you can access the service account properties using SQL Server Configuration Manager, found in the Configuration Tools menu under Microsoft SQL Server 2012.
4. In SQL Server Configuration Manager, select the SQL Server Services page, and double-click on the service you want to configure. The Properties dialog box opens automatically on the Log On page.
5. Choose either a built-in or a local/network account.
6. When you have changed the account, restart the service using the buttons in the Service Status section.
The SQL Server service inherits the rights of the Windows account in regards of its possibilities to access the underlying system.
SQL Server doesn't need to have administrative privileges on the machine; it only needs to have rights on the directories where it is storing its data, error log files, backups, and a few system permissions.
If you've created a dedicated Windows account, then the SQL Server setup will grant the permissions needed. If you change the service account after installation, you need to do it with SQL Server Configuration Manager, not with Windows Service Control Manager, because the latter doesn't set the required permissions for the account.
On Windows Server 2008 R2, the account chosen by default during the installation is the virtual account (see the Using a virtual service account recipe later in this chapter).
When you choose a built-in account, you don't need to provide a password, as it is predefined and managed by the operating system, more precisely by the Service Control Manager (SCM)—a process that manages services. You have two options:
Local system: This is a local Windows system account that has administrative rights on the computer. It can be seen on the network as the machine name (
<DOMAIN>\<MACHINE>
), so you could grant access to network resources to the machine account using Active Directory.Network service: This account has much more limited rights on the local machine, and can access network resources in the same way as the local system.
You can also choose a Windows or Domain account previously created by entering its full name (<DOMAIN>\<account>
) and its password. Make sure it does not have a password expiration policy, to avoid the service being blocked when the password has expired. It also needs to have the
Log on as a service right. For details, see the There's more section.
It is better to choose a real windows account instead of a built-in account (and now, a managed account is even better) in order to get more control over the rights you assign to SQL Server, because built-in accounts are shared between services. An attacker connected to SQL Server with administrative permissions could run the xp_cmdshell
extended stored procedure and compromise other services as well.
To allow a Windows account to be used to run a service, you need to give it the "Log on as a service right".
1. On your local server, open the Administrative tools menu folder and click on Local Security Policy.
2. In the Local Policies node, select User Rights Assignment. In the policies list, go to Log on as a service. Double-click on it, and add the account using the Add User or Group... button. Click on OK:
If your SQL server runs on Windows Server Core Edition, you have no GUI to change the service account after installation, or to configure many of the options described in the following recipes; you need to do it remotely.
1. On another machine with the SQL Server client tools installed, open Computer Management (compmgmt.msc), and right-click on the root Computer Management (Local) node. Select Connect to another computer…, and enter the server address.
2. Then, go to the Services and Applications node, where you will find SQL Server Configuration Manager.
You can add a user on any machine where the Active Directory Users and Computers tool is installed or on your Active Directory server by using Active Directory Administrative Center. When you create the account, uncheck the User must change password at next logon option, and check the Password never expires option. This last option disables password expiration for the account. If you want to allow password expiration for the service accounts, use Windows Server 2008 and managed service accounts (refer to the Using a managed service account recipe).

For more information, refer to this page of the SQL Server documentation: Configure Windows Service Accounts and Permissions (http://msdn.microsoft.com/en-us/library/ms143504.aspx).
A service like SQL Server runs under the security context of a Windows account. If several services run under the same account, they will be able to access other resources, such as the Access Control List (ACL) on files and folders, which is obviously not a good sign. With Windows Server 2008, Microsoft introduced the concept of service SID, a per-service Security Identifier. By defining a service SID, you create an identity for a specific service that can be used inside the Windows security model, like you would do with normal user accounts. But it allows you to define per-service rights even if they run under the same user or built-in account.
The per-service SID is enabled during the installation process on Windows Server 2008, and is used to grant rights for the service.
We will use a command-line tool to query the existence of the SID, and create one it if it does not exist:
1. Open a command shell (
cmd.exe)
.2. Type the following command:
sc qsidtype mssql$sql2012
Note
Downloading the example code
You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.
Here,
mssql$sql2012
is the name of the SQL server service, the service name for the SQL 2012 named instance. The name of the service of a default instance ismssqlserver
.Your result should look similar to the following:
[SC] QueryServiceConfig2 SUCCESS SERVICE_NAME: mssqlserver SERVICE_SID_TYPE: UNRESTRICTED
The
SERVICE_SID_TYPE
can have three values:NONE: The service has no SID
UNRESTRICTED: The service has a SID
RESTRICTED: The service has a SID and a write-restriction token
3. If
SERVICE_SID_TYPE
isNONE
, you can create a SID by entering the following command:sc sidtype mssql$sql2012 UNRESTRICTED
If you are using User Account Control (UAC)—the functionality bugging you every time you perform an administrative task—then you need to run the command shell as the administrator.
The SQL Server service SID is derived from the service and instance name. It is either NT SERVICE\MSSQLSERVER
for a default instance, or NT SERVICE\MSSQL$<INSTANCENAME>
.
The
sc.exe
command is used to communicate with the service control manager. The
sc qsidtype
command queries the current state of the SID, and sc sidtype
allows you to change it.
Choose the NONE
option if you want to remove the SID. UNRESTRICTED
creates an account. Don't use RESTRICTED
for SQL Server, as some resources will be blocked to the service and SQL Server will not start.
The managed service account is new in Windows Server 2008 R2, and allows for easier and better management of Active Directory accounts used for running a service.
Before managed service accounts, we had to create the same type of domain account for the services that we created for users, but we had to deactivate the password policies of these accounts to avoid the administrative overhead of changing the service account password on a regular basis, and restart SQL Server in the process. As a result, we didn't benefit from the increased security provided by changing the passwords.
The managed account offers the best of both solutions. It is an Active Directory account that is tied to a specific computer. The password is managed automatically by Active Directory and will be changed regularly without stopping the service, and the Service Principal Name (SPN) management is simplified.
An SPN is the name by which a client uniquely identifies and authenticates a service. We will cover it in the Using Kerberos for authentication recipe, later in this chapter.
To use managed service accounts, SQL Server needs to run on a Windows Server 2008 R2 computer (or Windows 7), and you also need to apply this hot fix, which corrects a bug appearing when the account password is changed: http://support.microsoft.com/kb/2494158.
To create a managed account, you need to do it with PowerShell, and the Active Directory PowerShell Snap-In must be installed:
1. In Server Manager, right-click on the Features node, and select Add Features. Go to Remote Server Administration Tools | Role Administration tools | AD DS and AD LDS Tools, and check Active Directory module for Windows PowerShell, if it is not already installed:
2. Open a PowerShell 2 session under an account having the rights to create users on Active Directory. Import the
ActiveDirectory
module:3. Then create the managed account:
New-ADServiceAccount -Name SQL-SRV1 -Enabled $true
Here,
SQL-SRV1
is the name of the account to be created.4. Now, associate the account with your SQL Server computer:
Add-ADComputerServiceAccount -Identity SQL1 -ServiceAccount SQL-SRV1
Here,
SQL1
is the name of your SQL Server.5. The managed account must now be installed on the server running SQL Server. There, open PowerShell, import the
ActiveDirectory
module, and type the following command:Install-ADServiceAccount -Identity SQL-SRV1
You can now use the account for your service, as described in the Choose an account for running SQL Server recipe. The name of the account must be followed by a dollar sign (DOMAIN\SQL-SRV1$
). The Password and Confirm Password textboxes must be empty.
The managed service account is tied to a single computer, and can only be used for services. You cannot log on with it. It cannot be used in a MSCS SQL Server cluster, where the service account must be used on several cluster nodes. But, unlike local built-in accounts, its name can be seen across the network and used to give permissions on the network shares and resources.
When you create a managed account on your Active Directory, you don't specify a password; it will be created and managed automatically. It will be refreshed according to the password policy (the default is 30 days), without disturbing the SQL Server service.
After creation, you can see your account in the Active Directory Users and Computers tool, in the Managed Service Accounts node of your domain, but no action can be taken from there, and you need to do everything with PowerShell.
Note
You should see the Managed Service Accounts node. If not, go to the View menu, and activate Advanced Features.
When creating the account with New-ADServiceAccount
, you can specify more options. An example of a more complete command is as follows:
New-ADServiceAccount -name SQL-SRV1 -AccountPassword (ConvertTo-SecureString -AsPlainText "MyPassword" -Force) -Enabled $true -Path "CN=Managed Service Accounts,DC=SQLCOOKBOOK,DC=COM" -ServicePrincipalNames "MSSQLSVC/SQLCOOKBOOK-SQL1.SQLCOOKBOOK.COM:1433" -Credential $PSCredential
Note
Access is denied error
You could get an "Access is denied" error from the New-ADServiceAccount
command. This is because of the User Account Control (UAC) policies of your machine. You can log in as the DOMAIN
\Administrator
account (if the User Account Control: Admin Approval Mode for the Built-in Administrator Account local policy is disabled, as it is by default), or temporarily disable Admin Approval Mode.
Run secpol.msc
, go to Security Settings Local Policies | Security Options | User Account Control: Run all administrators in Admin Approval Mode, and disable it. You'll have to restart the computer.
You can also create a PSCredential
object and provide it to PowerShell cmdlet
:
$username = "DOMAIN\Administrator" $password = "MyPassword" | ConvertTo-SecureString -asPlainText -Force $credential = New-Object System.Management.Automation.PSCredential($username,$password) New-ADServiceAccount -credential $credential -Name SQL-SRV1 -enabled $true
You can find complete information and troubleshooting tips at the following URLs:
Service Account Step-by-step guide: http://technet.microsoft.com/en-us/library/dd548356%28WS.10%29.aspx
The virtual service account is a new feature introduced in Windows Server 2008 R2. It is a local account that requires no password management, and can access the network with a computer identity, like the NetworkService account. It is, however, a better choice, because the NetworkService built-in account can be shared between services, making it more difficult to audit one particular service and to isolate one service from the others. The virtual account gives you the security benefits of NetworkService with a distinct account per service. You cannot create or delete a virtual account manually. As soon as a service is installed on a Windows Server 2008 R2 machine, it is available and has the same name as the service, for example NT SERVICE\MSSQL$SQL2012
for an instance named SQL2012
. It can be chosen like a normal user on local ACL rights, and can also be part of a local group.
To run the SQL Server service under a virtual account, follow these steps:
1. Open SQL Server Configuration Manager and select the SQL Server Services page.
2. Double-click on the service you want to configure. The Properties dialog box opens automatically on the Log On page.
3. In Log on as choice, choose a local/network account,
type NT Service\MSSQL$<instance name>
orNT Service\MSSQLSERVER
for the default instance.4. Leave the Password and Confirm password fields blank. Click on Apply. It will restart your service.
Virtual accounts can be seen as local managed accounts. They require no administration; you don't create them and you assign no password to them.
Virtual accounts cannot be used for SQL Server Failover Cluster Instance, because the virtual account would not have the same SID on each node of the cluster. In this case, use domain managed accounts. Since the virtual account is seen as the computer account on the network, if you want to grant network access to your service, use a managed service account instead.
Between the client machine and SQL Server, the SQL query and resultset data are sent with network packets that are human-readable using a packet sniffer, such as
Wireshark (http://www.wireshark.org/). As an example, the following screenshot shows a Wireshark session analyzing TDS packets. You can recognize some database content in the lower part of the window that is the result of a SELECT
query issued against the HumanResources.Employee
table in the AdventureWorks2012
database.

Note
Tabular Data Stream (TDS) is the protocol used by SQL Server to form packets to be sent through the network.
If you want to protect this information from network sniffing attempts, you need to encrypt communication between the client and the server using SSL.
If you want to secure the communication with SSL, you need to purchase an SSL certificate from a Certificate Authority (CA), such as VeriSign, Comodo, or DigiCert. While it is also possible to use a self-signed certificate, it is not recommended because a self-signed certificate is not validated by a trusted third party.
For the certificate to be seen by SQL Server, it must be installed using the same account running the SQL Server service. Or, if the SQL Server service is run by a Windows system account, a managed, or a virtual account, then you must install the certificate under an account having administrator privileges on the server.
1. Open SQL Server Configuration Manager, and select the SQL Server Network Configuration node.
2. Right-click on Protocols for <your SQL Server instance>, and select Properties.
3. On the Flags tab, choose Yes for Force Encryption, if you don't want to allow unencrypted connections:
4. On the Certificate tab, add your installed certificate.
5. Click on OK.
6. You need to restart the SQL Server service for it to take effect.
If you have chosen force encryption, the client will automatically connect through SSL. If not, you can specify it in the connection string in your client code. The following code provides an example:
Driver={SQL Server Native Client 11.0};Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;Encrypt=yes;
You can also do it when connecting with SQL Server Management Studio:
1. In the Connect to Database Engine dialog box, click on Options.
2. Go to the Connection Properties tab, and check Encrypt connection:
3. Click on Connect.
4. You can check from inside SQL Server whether the connections are encrypted or not, by using the following command:
SELECT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID;
This query requests the dynamic management view
sys.dm_exec_connections
for the current session. Theencrypt_option
column returns1
if the session is encrypted, and0
if not.
SQL Server responds on specific TCP and UDP ports, which need to be open on an enterprise firewall if you wish to access SQL Server from outside a LAN and on Windows Firewall, or any other local firewall installed on the computer running SQL Server. Windows Firewall is activated, by default, on Windows Server 2008.
In this recipe, we will learn to configure Windows Firewall on Windows Server 2008 R2. Adapt the process to your enterprise firewall, if needed.
Follow these steps in order to configure Windows Firewall:
1. In the Start menu, open Control Panel. Select System and Security, click on Windows Firewall, and then on Advanced Settings. The Windows Firewall configuration Microsoft Management Console (MMC) snap-in opens.
2. Go to Inbound Rules, and click on New rule... in the Action panel on the right. In the type of rule, select Port. Choose the TCP and Specific local ports. Enter the following ports separated by commas:
If you are using only SQL Server engine default instance, enter
1433
If you are using Analysis Services engine default instance, enter
2383
If you are using Service Broker, enter
4022
Note
You can find ports used by the different components of SQL Server in the SQL server documentation at http://msdn.microsoft.com/en-us/library/cc646023%28v=SQL.110%29.aspx.
3. Click on Next. Choose Allow the connection. Click on Next. In Profile, keep only Domain checked if you are in a domain. The other profiles, Private and Public, make more sense with workstations or laptops than servers. If your server is connected to a network marked as public by the administrator, such as a direct connection to the Internet, and you want SQL Server to be visible from the outside, then select Public. Click on Next and give a name to the rule, for example,
SQL Server
.
The recipe describes how to open the port for the default instance of SQL Server, which is TCP 1433. Named instances use a dynamic port that might change each time the SQL Server service is restarted. This port is communicated to the client by the SQL Server Browser service listening on UDP 1434
. Dynamic ports are not suitable for a firewall configuration, because choosing dynamic ports forces you to open a range of ports. The best way to ensure a proper firewall protection is to define a fixed TCP port for your named instance that allows you to stop the SQL Server Browser and close the UDP 1434
port in your firewall configuration. For more on this and how to set a TCP port for SQL Server, refer to the next recipe, Disabling SQL Server Browser.
Note
In Windows Server 2008, the default dynamic port range has changed. See http://support.microsoft.com/kb/929851 for reference.
To limit access to specific users or computers, configure the inbound rule to allow only a secure connection. To do so, select Allow the connection if it is secure in the Action page, when you create the rule. You can also do it later by selecting it in the rule properties in the General tab:

Then, in the rule properties, go to the Users tab or to the Computers tab to add authorizations to users or computers, respectively.
SQL Server uses endpoints to open communication channels on the network. An endpoint can be used for T-SQL communication, Service Broker, or Database Mirroring. If you use Service Broker or Database Mirroring, you might have to open other TCP ports. You can see which ports need to be opened by querying the metadata of the endpoints:
SELECT name, protocol_desc, port, state_desc FROM sys.tcp_endpoints WHERE type_desc IN ('SERVICE_BROKER', 'DATABASE_MIRRORING');
The netsh.exe
program allows you to manage Windows Firewall using the command line. With it you can configure your Firewall and include commands in a script or a batch file. The netsh
executable requires elevated privileges, so you need to run your command shell session as the administrator. Windows Firewall with Advanced Settings, the version of Windows Firewall found on Windows Server 2008, uses the advfirewall netsh
helper. The following is an example of a command opening the TCP 1433
port:
netsh advfirewall firewall add rule name = "SQL Server" dir = in protocol = tcp action = allow localport = 1433,2383 profile = DOMAIN
You can find the description of this command in the Knowledge Base article 947709 (http://support.mic rosoft.com/kb/947709).
The SQL Server Browser service starts automatically when you install SQL Server in a cluster, or as a named instance. Its job is to communicate the presence of an SQL Server instance on the machine, and to send to the client the TCP port on which a named instance is listening. To hide the presence of an instance of SQL Server, you can stop it or configure it so that it does not respond to broadcast requests.
If you updated an installation of SQL Server, or installed it in a cluster or as a named instance, the SQL Server Browser service is started automatically. You can check whether the service is running or not, and disable it by following these steps:
1. Open SQL Server Configuration Manager, select the SQL Server Services tab, and double-click on the SQL Server Browser service. On the Log On tab, click on Stop to stop the service.
2. Go to the Service tab. The Start mode is the only enabled option. Change its value to Disabled.
3. If you have a named instance, you need to set a fixed TCP port for it to be accessible from the client machines. Still in SQL Server Configuration Manager, go to the SQL Server Network Configuration node, Protocols for MSSQLSERVER (or the name of your instance). Right-click on TCP/IP and go to the IP Addresses tab.
4. You will see several sections, one per IP address defined on your server, including the loopback adapter (
127.0.0.1
). Choose the IP address of the interface from which the clients access SQL Server. If there is a0
in the TCP Dynamic Ports property, then remove it and enter the port you want in the TCP Port property.5. Restart SQL Server for the changes to take effect.
6. To indicate which port is to be accessed on the client machine, you need to specify it in the connection string by naming the server as follows:
<server_name>,<port>
(server name and port name separated by a comma). For example,SQL1,8200
—if you set the TCP port as8200
. A full connection string example is as follows:Data Source= SQL1,8200;Initial Catalog=AdventureWorks2012;User Id=fred;Password=#Fr3d!;
The instance of SQL Server by default listens on the TCP 1433
port.
When you install a SQL Server named instance, the port is dynamically assigned when the service starts. To access this port and start a TCP session, the client sends a request to the SQL Server Browser listening on UDP 1434
, which responds with the port attributed to SQL Server, so the TCP session can take place.
Also, to clients broadcasting a request on the network for searching the available instances of SQL Server, SQL Server Browser responds with the information of which SQL Server instances are running, and on which port they are listening. If you have only one default instance installed, then the SQL Server Browser is not needed, and is disabled by default during the SQL Server installation. If you have a named instance, then SQL Server Browser is started automatically. In that case, you can define a fixed port, and disable the SQL Server Browser for your server to be more discreet.
If you want to hide your instance while keeping the dynamic port feature, you can ask the SQL Server Browser not to advertise the presence of the SQL server instances on the machine.
In SQL Server Configuration Manager, open the SQL Server Network Configuration node, and right-click on Protocols for <your instance>. Open the Properties window, go to the Flags page, and change the Hide Instance property to Yes:

This prevents the SQL Server Browser from showing the instance when the client computers browse the network. You will still be able to connect with client application such as SSMS, but you will need to enter the address manually in connection dialog box.
If you installed the full SQL Server package, you might have unnecessary running components on your server, which represent more security risks and diminish the overall server performance.
There will also be some other Windows services running on your server that you can safely disable, especially on a server dedicated to SQL Server. We could say that SQL Server is relatively self-contained. It uses the underlying operating system only to access the physical resources. On a dedicated server, Windows services can be limited to the minimum.
To stop an unused service, follow these steps:
1. On Windows Server 2008, click on the Server Manager icon or open the Computer Manager Microsoft Management Console (MMC) snap-in.
2. Go to Configuration | Services. Click twice on the Status column header to order the view by status in descending order, and see the started services first.
3. For any service that you want to disable, double-click on it to open the Properties page. Select disabled in the startup type listbox, and click on Stop under Service Status.
Here is a list of services started automatically by Windows that you could consider stopping:
DHCP Client: On most servers, IP addresses are fixed and DHCP is not needed.
Network Location Awareness: This collects and stores the configuration information for the network and notifies the programs when this information is modified. You can't disable it unless you use the Windows 2008 Advanced Firewall.
Print Spooler: This loads the files to the memory for printing later. It can be disabled if you don't print from the server.
Windows Error Reporting Service: This allows errors to be reported when programs stop working or responding, and allows existing solutions to be delivered. It can safely be disabled.
Windows Firewall: You can disable this service, if you are using a network firewall.
Shell Hardware Detection: This activates AutoPlay for removable devices. On Windows Server 2008 R2, this service stops when nobody is logged in, to minimize risks. You can disable it.
Disable any SQL Server unused service if the components were installed with SQL Server: Analysis Services, Reporting Services, and Integration Services. You can also uninstall them by using the SQL Server Installation program.
In the Active Directory world, there are two authentication mechanisms: NTLM and Kerberos. The legacy NT LAN Manager (NTLM) is a challenge-response authentication protocol based on old cryptographic methods and Microsoft advises not to use it anymore.
Note
In the MSDN Windows Protocol documentation section, the following statement is made:
"Implementers should be aware that NTLM does not support any recent cryptographic methods, such as AES or SHA-256. It uses cyclic redundancy check (CRC) or message digest algorithms (RFC1321) for integrity, and uses RC4 for encryption. Deriving a key from a password is as specified in RFC1320 and FIPS46-2. Therefore, applications are generally advised not to use NTLM."
For more information on this topic, please refer to http://msdn.microsoft.com/ en-us/library/cc236715(v=PROT.10).aspx.
Kerberos is a free software protocol, first developed at the MIT, introduced in Windows 2000. It provides secure identification of both the client and the server through an exchange of secured tickets.
Instead of using a password hash, Kerberos manages authentication through shared-secret encryption keys. Instead of sharing the password hash information, the client and the authentication server share a symmetric key. The authentication server is called the Key Distributor Center (KDC), and runs as a service on a domain controller. At logon, the client requests a Ticket Grant Ticket (TGT) from the KDC. The KDC creates the TGT that contains the client's identity, and sends it back to the client with a session key that the client will use to encrypt further communication. The TGT has a short lifespan, typically 8 to 10 hours. At that time, the client has not been granted access to any resource. It then needs to request a service ticket to the KDC. This is a ticket allowing access to a resource. As the client has a TGT, it does not have to authenticate again to the KDC. The service ticket must contain the Service Principal Name (SPN) of the target resource (in our case, a SQL Server instance), hence the importance of the SPN, as we will see in this recipe. If the KDC accepts the request, it sends back the service ticket, which will then be used by the client to request access to the server. Kerberos is based on centralized key management (a trusted third-party), and has the advantage of being a well-tested and scrutinized mechanism based on open standards.
You can get detailed information about the Kerberos authentication scheme in the Technet article How the Kerberos Version 5 Authentication Protocol Works (http://technet.microsoft.com/en-us/library/cc772815.aspx).
For Kerberos to be used, you must be in an Active Directory infrastructure, and the server SPN must be registered with the Active Directory. The client and server computers must be part of the same domain or domains that trust each other.
If the conditions are met, Kerberos should be already used by default. To check that the SQL Server user sessions use Kerberos, issue the following T-SQL command:
SELECT auth_scheme, net_transport, client_net_address FROM sys.dm_exec_connections;
If a connection from the same domain or a trusted domain uses the NTLM authentication scheme, you need to investigate why it cannot use Kerberos.
When SQL Server starts, it tries to automatically register its SPN with Active Directory. If the SQL Server service account doesn't have the right to do so, the SPN is not created and Kerberos authentication is not possible.
To check if the SPN if registered, use the following command in a cmd or PowerShell shell:
setspn.exe -L DOMAIN\<SQL service Account>
For example, if the service account is the SQL-SRV1
managed account that we created in the Use a managed service account recipe:
setspn.exe -L DOMAIN\SQL-SRV1
Then, you should see a result similar to the following:
MSSQLSvc/SQL1.domain.com:<port>
Here, SQL1
is the name of the SQL Server computer.
If you do not see this result, then it means that the SPN was not automatically registered. It could be because the service account does not have rights to "write public information" on itself in Active Directory.
Note
If the SQL Service account is a domain user that you created for SQL Server, it might very well be the case, as domain users don't have this right by default.
Another way to find out if the SPN was automatically registered is to check the SQL Server error log (it can be found in SSMS Object Explorer in the Management/SQL Server Logs node). You should see an error at startup stating that The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service.
To register the SPN:
1. On your Windows Server 2008 domain controller's Start menu, go to the
Active Directory
folder, and click on ADSI Edit.2. Connect to Default Naming Context. Locate the service account and right-click on it to open the Properties window. Go to the Security page.
3. Select SELF in the Group or user names list, and select the Write public information checkbox:
4. Restart the SQL Server service.
Note
Be careful with a SQL Server cluster installation
In an MSDN blog entry, a member of the Microsoft Customer Service and Support (CSS) team for SQL Server does not recommend setting this permission to the SQL Server service domain account if it is used in a SQL Server cluster, because of issues with the AD replication. The warning can be found at http://blogs.msdn.com/b/psssql/archive/2010/03/09/what-spn-do-i-use-and-how-does-it-get-there.aspx.
The SPN is a name uniquely identifying an instance of a service. Without a proper SPN, Kerberos cannot authenticate a service and provide the service ticket to allow the client to request the server. So, without a SPN, the only way for the client to authenticate to SQL Server is by using NTLM. The SPN must be registered with the Active Directory, which has the role of the KDC. The SPN follows a fixed format: <service>/<host>:<port/name>
. The hostname is always, if possible, the Fully Qualified Domain Name (FQDN), not only the NETBIOS name. If the SQL Server service runs under an account that has permissions to register the SPN in the Active Directory, then the SQL Server instance will automatically register the SPN on the Active Directory when it is started, and it will unregister it when it is stopped. This is also the case when the service account is the built-in LocalSystem
or the NetworkService
local account. These accounts are shown as the machine name at the AD and have the rights to register the SPN.
If you cannot or do not want to give your service account permission to write public information on the AD, or if for any other reason your SPN does not get registered automatically, you still have the option to create it manually with the setSPN.exe
command:
setspn.exe -A MSSQLSvc/SQL1.domain.com:<port> DOMAIN\SQL-SRV1
Here, SQL1
is the SQL Server computer name and DOMAIN\SQL-SRV1
is the SQL Server service account.
If you register SPNs manually for SQL Server, do it for both the FQDN and the NetBIOS name (the name of the machine without the domain name), in case the DNS resolution would fail on the client.
For a complete Kerberos troubleshooting guide, refer to the article at http://blogs.technet.com/b/askds/archive/2008/05/14/troubleshooting-kerberos-authentication-problems-name-resolution-issues.aspx.
As we have seen in the previous recipe, authentication consists of an exchange between a client and a server to assess the identity of each other, and finally accept or refuse the connection. The client knows how to connect to the server, because it has its address and a way to prove its identity, and the server knows that the client is legitimate, because it receives a genuine token or password. This exchange of information is designed to be able to survive multiple hops, meaning that it can be forwarded by machines between the client and the server. But, what if some attacker is able to intercept the communication between the client and the server, and use it to masquerade himself as the client to connect to SQL Server? This is called an authentication relay attack. There are two ways to do it. The first one is called a luring attack . The client is lured to connect to a resource he believes to be a real server or resource, but has actually been set up by an attacker. The attacker can then reuse the Windows authentication information stolen from the client authentication attempt to connect to Windows servers, such as SQL Server.
The second way is called a spoofing attack, also known as a Man-in-the-Middle attack. An attacker intercepting communication between a client and SQL Server, by the way of DNS redirection or IP routing, relays the authentication request from the client to the server. When the server replies with a credential request, the attacker forwards it back to the client, and so on, until authentication is successful. Once authenticated, it can issue its own requests to the server. The attacker acts like a translator stealing secrets from both parts.
In 2009, Microsoft released the Security Advisory 973811, announcing the availability of a feature designed to prevent this kind of attack, named Extended Protection for Authentication, and providing two mechanisms: service binding and channel binding . Service binding requires that the client adds to the authentication request a signed SPN, identifying the SQL Server it wants to connect to. If an attacker tries to use the credentials he stole from the connection attempt to another resource, he won't be able to provide this signed SPN to SQL Server, and the connection will fail. This protection has a very light performance footprint, because it takes place only at the authentication phase.
Channel binding offers greater protection and prevents spoofing, but at the cost of more overhead. It establishes a secure channel using Transport Layer Security (TLS), the successor of SSL, where the authenticity of the client is ensured by a Channel Binding Token (CBT) and encryption. So neither of the packets could be tampered with or read, nor could the attacker claim to be the originator of the request.
To configure Extended Protection, follow these steps:
1. Open SQL Server Configuration Manager, and go to the SQL Server network configuration node. Right-click on Protocols for <your instance>. Open the Properties window, and go to the Advanced page.
2. If all your client computers support Extended Protection for Authentication, choose the Required for the Extended Protection property; otherwise, choose Allowed.
3. If the SQL Server service is known by several SPNs (that is, the server has several names), add them in the Accepted NTLM SPNs box, separated by semicolons. For more information about the SPN, see the Use Kerberos for authentication recipe.
4. If you want to enable Channel Binding protection, you also need to force encryption on all connections. Go to the Flags page, and set Force Encryption to Yes. If encryption is not forced, only Service Binding will be available.
When you enable Extended Protection in SQL Server Configuration Manager, you can choose to allow Extended Protection for clients that support it, or to force every connection to use it. Windows 7 and Windows Server 2008 R2 already have Extended Protection built-in. To allow Extended Protection on other clients, you have to install the security fix present in KB968389 (http://support.microso ft.com/kb/968389). The client must connect with the Native Client library (SQLNCLI) for Extended Protection to be available.
For more details about Extended Protection, refer to the documentation entry at http://msdn.microsoft.com/en-us/libr ary/ff487261.aspx, and this TechNet blog entry at http://blogs.technet.com/b/srd/archive/2009/12/08/extended-protection-for-authentication.aspx.
An attacker with read permissions on the directory where SQL Server stores its data files (.mdf
) would have no difficulty to gain access to all databases, simply by copying the .mdf
and attaching it to another SQL Server where he has sysadmin privileges. The first protection against this threat is of course to manage tight NTFS permissions on the SQL server data directory. To further secure a database, you can also use Transparent Database Encryption (TDE) that encrypts all its data files (a database can be composed of one or more data files). An attacker trying to attach the file on another server would have no means to decrypt it, because he would lack the key to decrypt it. The encryption key is stored in the master system database. This feature is only available in the Developer, Enterprise, and Data Center editions of SQL Server.
First, you need to create a server encryption master key:
1. In a SQL Server Management Studio query windows, type the following commands:
USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong_Password';
2. Back up the master key immediately, and keep it in a safe and secured place. If you fail to do so, you will lose the master system database, and have no way to recover your encrypted database content.
BACKUP MASTER KEY TO FILE = '\\path\SQL1_master.key' ENCRYPTION BY PASSWORD = 'Very Strong p4ssw0rd';
The password must meet the Windows policy requirements, and the SQL Service account must have writing permissions on the directory where you save the key.
3. Still being in Master, create a server certificate:
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';
4. Save it into a backup file:
BACKUP CERTIFICATE TDECert TO FILE = '\\path\SQL1_TDECert.cer' WITH PRIVATE KEY ( FILE = '\\path\SQL1_TDECert.pvk', ENCRYPTION BY PASSWORD = 'Another Very Strong p4ssw0rd' );
5. Go to your database and create a database encryption key:
USE MyDatabase; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE TDECert;
6. Finally, activate the database encryption:
ALTER DATABASE MyDatabase SET ENCRYPTION ON;
TDE encrypts your data and log files on the disk automatically and transparently, without requiring any other change to your database. It also encrypts any database or log backup.
Implementation is very simple. Being in master, you first need to create a master encryption key and a certificate or an asymmetric key. Then, in your database, you create an encryption key and set the database encryption option to On, and you are done.
The encryption key can use several encryption algorithms, as follows:
Algorithm |
Description |
---|---|
AES_128 |
Advanced Encryption Standard (AES) with a 128 bit key. |
AES_192 |
AES with a 192 bit key. |
AES_256 |
AES with a 256 bit key. |
TRIPLE_DES_3KEY |
Triple Data Encryption Algorithm (Triple DEA) block cipher, applying the Data Encryption Standard (DES) cipher algorithm three times to each data block. The key is 192 bits long. |
AES is an algorithm adopted as a Federal U.S. government standard in 2002 and approved by the NSA. It is a stronger algorithm than Triple-DES. When you choose the algorithm, you need to balance between security and performances. AES_128 is gradually becoming more vulnerable as new attacks are discovered (see the Wikipedia page on AES for a list of attempts to crack it: http://en.wikipedia.org/wiki/Advanced_Encryption_Standard), but it is still considered reasonably safe. If your database contains classified information, you should go for a bigger key, which is harder to crack. But the bigger the key, the higher the performance impact will be. This being said, the performance impact of TDE is relatively low. For more information, you can look at the article at http://www.databasejournal.com/features/mssql/article.php/3815501/Performance-Testing-SQL-2008146s-Transparent-Data-Encryption.htm. The author ran tests on his database and published the results. You can download the test script to make an assessment for yourself on your server.
If you want to restore an encrypted database backup on another server, you must first restore the certificate used to encrypt the database encryption key to the other server:
USE master; CREATE CERTIFICATE TDECert FROM FILE = '\\path\SQL1_TDECert.cer' WITH PRIVATE KEY ( FILE = '\\path\SQL1_TDECert.pvk', DECRYPTION BY PASSWORD = 'Another Very Strong p4ssw0rd' );
After that, you can restore the database and log backups transparently.
The Linked Server feature allows you to define inside SQL Server, a connection to another server that can be referenced in your T-SQL code to access distant data sources. With it, you can create distributed queries or exchange data by means of INSERT … SELECT
queries. It is like storing a connection string in a client application, but this client application is your SQL Server. A linked server can reference a distant SQL Server, or any other data source having an OLEDB provider installed on your SQL Server machine. The following code provides an example of a distributed query using a Linked Server named CentralServer
that hosts a repository for the HR department of the company. We are joining it with our local Employee
table to retrieve an important person's information:
SELECT * FROM HumanResources.Employee e JOIN CentralServer.HRRepository.Person.Person p ON e.BusinessEntityID = p.BusinessEntityID;
When you create a linked server, there are several security considerations you should be aware of. In this recipe, we will go through them step by step.
1. In SQL Server Management Studio, open the Server Objects node in Object Explorer, and right-click on Linked Servers. Click on New Linked Server… in the pop-up menu.
2. Choose a name and a data provider for the linked server. If your linked server is also an SQL Server, enter the name of the other instance and choose SQL Server as Server Type.
3. In the Security page, map any local login that needs to be granted access to the linked server. Enter the local login, the remote login, and the password. If the local and remote logins have the same name and password, then you can just activate the Impersonate checkbox, so SQL Server does not need to store the password again in the linked server configuration.
4. Then, you can select how to handle logins not defined in the mapping list in the choices listed in the following table:
Not be made
The access is limited to logins explicitly defined in the mapping list
Be made without using a security context
The Linked Server will apply the guest user permissions on the database accessed through the Linked Server
Be made using the login's current security context
Applies impersonation (such as the Impersonate checkbox) for all non-mapped logins
Be made using this security context
Allows all non-mapped logins to connect using the specified distant SQL Server login
As a linked server is like a connection string stored in a SAL Server instance, acting as a client to another data provider, you need to specify how authentication will be made in the Linked Server Security page with regards to the connected user of the local SQL Server. From a security standpoint, the best course of action is, of course, to limit access to defined mappings, and choose NOT to be made for the rest.
If you need to allow multiple hops—for example, a client computer accessing SQL Server and running a query which goes through a linked server—or if you want to use impersonation and to pass a security context in the option Be made using the login's current security context for Windows authentication logins, the configuration must meet the requirements for delegation, which are as follows:
The Windows account must have access permissions to the linked server. In your Active Directory, the user's property Account is sensitive and cannot be delegated must not be selected.
Both the servers must have their SPN registered in the domain, and the SQL Server service account must be trusted for delegation in the Active Directory.
A SQL Server endpoint is a door opened from or to SQL Server. Everything that can go from your SQL Server to the network, or from the network to SQL Server, goes through an endpoint.
Endpoints can be system or user-defined. The system endpoints allow the usual T-SQL connections to SQL Server to send queries.
The endpoints are defined for a specific protocol, which were either HTTP or TCP in the previous versions. In SQL Server 2012, the HTTP endpoints previously used for the native web services feature are removed. Now you only have TCP endpoints.
You can create a user-defined endpoint for three purposes: TCP requests, service broker, or database mirroring. Here we will talk about the first one, which is useful to set up a dedicated and secured connection to SQL Server for administrative purposes or distant access.
To configure endpoint security, follow these steps:
1. In a SQL query window, type the following T-SQL command:
CREATE ENDPOINT myTSQLEndpoint STATE = started AS TCP ( LISTENER_PORT = 8080, LISTENER_IP = (127.0.0.1) ) FOR TSQL ();
2. You will receive a message saying that it will cause the revocation of any Public connect permissions on the TSQL default TCP endpoint. This effectively means that all logins will lose their permission to connect to SQL Server through the default T-SQL endpoint. If you still want to allow connection permissions to the default TCP endpoint, issue the following command:
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public];
3. You can view the state of the endpoints with this command:
SELECT * FROM sys.tcp_endpoints;
4. You can start or stop the endpoints with the
ALTER ENDPOINT
command. For example, you can stop the default TCP endpoint as follows:ALTER ENDPOINT [TSQL Default TCP] STATE = STOPPED;
The state we just [email protected] will remain even after a service restart.
When SQL Server is installed, a system endpoint is created for each network protocol used in SQL Server. The permission to access these endpoints is given to the Public server role. Every login declared in SQL Server is a member of this role, and permissions on the Public server role can be changed, unlike other fixed server roles. You can grant, revoke, or deny permissions to connect to an endpoint to all the logins through the Public role, or to specific logins by revoking CONNECT
permissions to the Public role, and by granting specific privileges as follows:
REVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]; GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [a_specific_login];
If you want to allow connections to SQL Server from only a specific client IP address, you can stop the default endpoint, or deny access to it, and create a user-defined T-SQL endpoint, with a client IP address and a TCP port.
Stopping default endpoints has the same effect as disabling them in SQL Server Configuration Manager.
In SQL Server 2012, you can create user-defined server roles. We will detail this functionality later. This interests us for now, because a server role could be used to grant CONNECT
permissions on an endpoint to a group of logins.
The following code creates a user-defined server role, adds a login as a member, and grants the CONNECT
privilege on the default TCP endpoint to the role:
USE [master]; CREATE SERVER ROLE [TCPRole]; ALTER SERVER ROLE [TCPRole] ADD MEMBER [my_login]; GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO [TCPRole];
Some features are disabled, by default, on a fresh SQL Server installation, because they might represent a security threat. In SQL Server 2005, the state of these features could be changed in a dedicated tool installed with SQL Server, and named to the SQL Server Surface Area Configuration. This tool was removed in SQL Server 2008 because there was no need for a separate interface for that. Indeed, in SQL Server 2012, all the sensitive options can be manipulated in a facet named Surface Area Configuration. In this recipe, we will show how to enable them or disable them using the facet or the old-fashioned sp_configure
options, and we will give some example of usage that will show why they should be kept disabled.
First, let's see how to check and change the status of these features using a facet:
1. In SQL Server Management Studio, right-click on the Server node in Object Explorer. Click on Facets.
2. In the View Facets dialog box, select the Surface Area Configuration facet.
3. Check that the AdHocRemoteQueriesEnabled, OleAutomationEnabled and XPCmdShellEnabled facet properties are set to False. Change their state if needed.
The list of system objects that are impacted by each facet property can be retrieved by querying the
sys.system_components_surface_area_configuration
catalog view, as shown in the following example:SELECT * FROM sys.system_components_surface_area_configuration WHERE component_name IN ( 'Ole Automation Procedures', 'xp_cmdshell' );
Note
Using policy-based management
You can also use Policy Based Management (PBM) to check and modify these options on one or many instances of SQL Server. We will cover PBM in Chapter 7, Auditing.
4. To check and modify the status of these options with the T-SQL code, use the following statements:
EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'Ad Hoc Distributed Queries'; EXEC sp_configure 'Ole Automation Procedures'; EXEC sp_configure 'xp_cmdshell';
5.The
run_value
is1
if it is enabled. You can disable or enable it by changing the value to0
(disabled) or1
(enabled), and issue aRECONFIGURE
command to apply the change:EXEC sp_configure 'Ad Hoc Distributed Queries', 0; EXEC sp_configure 'Ole Automation Procedures', 0; EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;
Ad hoc distributed queries allow the use of connection strings to other data sources inside a T-SQL statement. You can see it as a one-time linked server. It uses the OPENROWSET
or OPENDATASOURCE
keywords to access distant databases through OLEDB. The following is an example:
SELECT a.* FROM OPENROWSET('SQLNCLI', 'Server=SERVER2;Trusted_Connection=yes;', 'SELECT * FROM AdventureWorks.Person.Contact') AS a;
The rights applied depend on the authentication type. In the case of a SQL Server login, the SQL Server service account is used. In the case of a Windows Authentication login, the rights of the Windows account are applied.
OLE automation procedures are system-stored procedures that allow the T-SQL code to use OLE automation objects, and then run the code outside of the SQL Server context. Procedures such as sp_OACreate
are used to instantiate an object and manipulate it. The following example demonstrates how to delete a folder using OLE automation procedures. This will succeed, provided the SQL Server service account has the rights to do it:
EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'Role Automation Procedures', 1; RECONFIGURE; GO DECLARE @FSO int, @OLEResult int; EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FSO OUTPUT; EXECUTE @OLEResult = sp_OAMethod @FSO, 'DeleteFolder', NULL, 'c:\sqldata'; SELECT @OLEResult; EXECUTE @OLEResult = sp_OADestroy @FSO;
Only members of the sysadmin
server role can use these procedures. As any sysadmin
member can re-enable the option with sp_configure
, there is in fact no real way to disable them for good.
The xp_cmdshell
extended stored procedure allows access to the underlying operating system from SQL code. You can issue shell commands, as shown in the following example:
exec xp_cmdshell 'DIR c\*.*';
It has the same security permissions as the SQL Server service account. So it is crucial to limit access to this procedure, and to ensure limited privileges of the service account.
To allow access to
xp_cmdshell
for non-sysadmin logins, you could encapsulate it in a stored procedure that uses the EXECUTE AS
elevation. If you want to allow them to issue arbitrary commands, you need to define a proxy account. xp_cmdshell
will then run under the rights of this account for non-sysadmin logins. The following code snippet creates the ##xp_cmdshell_proxy_account##
credential:
EXEC sp_xp_cmdshell_proxy_account 'DOMAIN\user','user password';
You can issue the following statement to query it:
SELECT * FROM sys.credentials WHERE name = '##xp_cmdshell_proxy_account##';
You can use the following command to remove it:
EXEC sp_xp_cmdshell_proxy_account NULL;
Even if xp_cmdshell
is disabled, someone connected with a login that is in the sysadmin server role can re-enable and use it. It is crucial that the service account running SQL Server does not have elevated rights on the computer and on the domain. The following is an example that demonstrates how easy it is to gain access to the domain if the service account has administrative rights on Active Directory:
EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'xp_cmdshell', 1 RECONFIGURE EXEC xp_cmdshell 'dsadd.exe user "CN=me, CN=Users, DC=domain, DC=com"' EXEC xp_cmdshell 'dsmod.exe group "CN=domain admins, CN=Users, DC=domain, DC=com" -addmbr "CN=me, CN=Users, DC=domain, DC=com"'
This code re-enables xp_cmdshell
, and uses commands to create a user on the Active Directory and to add it in the
Domain Admins group.