Microsoft SQL Server 2012 Security Cookbook

By Rudi Bruchez
  • Instant online access to over 7,500+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Securing Your Server and Network

About this book

In 2011, a big corporation suffered a 23-day network outage after a breach of security that allowed the theft of millions of registered accounts on its gaming network. A month later, hackers claimed in a press release to have stolen personal information of 1 million users by a single SQL injection attack. In these days of high-profile hacking, SQL Server 2012 database security has become of prime importance.

"Microsoft SQL Server 2012 Security Cookbook" will show you how to secure your database using cutting-edge methods and protect it from hackers and other security threats. You will learn the latest techniques for data and code encryption, user authentication and authorization, protection against brute force attacks, denial-of-service attacks, and SQL Injection, securing business intelligence, and more.

We will start with securing SQL Server right from the point where you install it. You will learn to secure your server and network with recipes such as managing service SIDs, configuring a firewall for SQL Server access, and encrypting the session by SSL. We will then address internal security : creating logins to connect to SQL Server, and users to gain access to a database. We will also see how to grant privileges to securable objects on the server or inside the database.

After having managed authentication through logins and users, we will assign privileges inside a database using permissions. We will then learn about symmetric keys, asymmetric keys and certificates, which can be used to encrypt data or sign data and modules with a choice of cipher algorithms, as well as creating hash representations of data.

Then we will cover methods to protect your database against brute force attacks, denial-of-service attacks, and SQL Injection. Finally we will learn about auditing and compliance and securing SQL Server Analysis Services (SSAS) and Reporting Services (SSRS).

Publication date:
September 2012
Publisher
Packt
Pages
322
ISBN
9781849685887

 

Chapter 1. Securing Your Server and Network

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

 

Introduction


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.

 

Choosing an account for running SQL Server


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.

How to do it...

The first time you can choose the service accounts is during the installation process. To complete the installation, perform the following steps:

  1. 1. Open the Server Configuration page in the assistant.

  2. 2. When it opens, you will see the Service Accounts tab.

  3. 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. 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. 5. Choose either a built-in or a local/network account.

  6. 6. When you have changed the account, restart the service using the buttons in the Service Status section.

How it works...

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.

There's more...

To allow a Windows account to be used to run a service, you need to give it the "Log on as a service right".

How to give the Log on as a service right to an account

  1. 1. On your local server, open the Administrative tools menu folder and click on Local Security Policy.

  2. 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:

How to do it in Windows Server Core

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. 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. 2. Then, go to the Services and Applications node, where you will find SQL Server Configuration Manager.

Creating a domain account to use as a service account

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).

See also

 

Managing service SIDs


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.

How to do it...

We will use a command-line tool to query the existence of the SID, and create one it if it does not exist:

  1. 1. Open a command shell (cmd.exe).

  2. 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 is mssqlserver.

    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. 3. If SERVICE_SID_TYPE is NONE, 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.

Note

When the SQL Server SID is enabled, all extra permissions that you will want to give to SQL Server on the local machine (such as ACL on directories for backup, or for file import with the BULK INSERT command) will have to be given to the SID, and not to the SQL Server service account.

How it works...

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.

 

Using a managed service account


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.

Getting ready

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.

How to do it...

To create a managed account, you need to do it with PowerShell, and the Active Directory PowerShell Snap-In must be installed:

  1. 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. 2. Open a PowerShell 2 session under an account having the rights to create users on Active Directory. Import the ActiveDirectory module:

    Import-Module ActiveDirectory
    
    
  3. 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.

    Note

    If you get an "Access is denied" error, please refer to the How it works in section of this recipe.

  4. 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. 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
    

    Note

    Do not create managed account names with more than 15 characters; there's a bug acknowledged by Microsoft that would make this step fail if the name is too long.

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.

How it works...

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

There's more...

If you don't use a managed service account anymore, you should remove it.

Removing a managed account

If you don't use a managed account on your computer, uninstall it:

Uninstall-ADServiceAccount -Identity SQL-SRV1

If you don't need a managed account anymore, remove it from your AD:

Remove-ADServiceAccount -Identity SQL-SRV1
 

Using a virtual service account


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.

How to do it...

To run the SQL Server service under a virtual account, follow these steps:

  1. 1. Open SQL Server Configuration Manager and select the SQL Server Services page.

  2. 2. Double-click on the service you want to configure. The Properties dialog box opens automatically on the Log On page.

  3. 3. In Log on as choice, choose a local/network account, type NT Service\MSSQL$<instance name> or NT Service\MSSQLSERVER for the default instance.

  4. 4. Leave the Password and Confirm password fields blank. Click on Apply. It will restart your service.

How it works...

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.

 

Encrypting the session with SSL


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.

Getting ready

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.

How to do it...

  1. 1. Open SQL Server Configuration Manager, and select the SQL Server Network Configuration node.

  2. 2. Right-click on Protocols for <your SQL Server instance>, and select Properties.

  3. 3. On the Flags tab, choose Yes for Force Encryption, if you don't want to allow unencrypted connections:

  4. 4. On the Certificate tab, add your installed certificate.

  5. 5. Click on OK.

  6. 6. You need to restart the SQL Server service for it to take effect.

    Note

    If you don't choose a certificate, SQL Server will automatically create and use a self-signed certificate.

How it works...

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. 1. In the Connect to Database Engine dialog box, click on Options.

  2. 2. Go to the Connection Properties tab, and check Encrypt connection:

  3. 3. Click on Connect.

  4. 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. The encrypt_option column returns 1 if the session is encrypted, and 0 if not.

There's more...

Of course, the certificate must still be valid. You will have to renew your certificate periodically before expiration.

 

Configuring a firewall for SQL Server access


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.

How to do it...

Follow these steps in order to configure Windows Firewall:

  1. 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. 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. 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.

How it works...

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.

There's more...

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.

Find specific ports used by SQL Server

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'); 

Do it by script

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).

 

Disabling SQL Server Browser


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.

How to do it...

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. 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. 2. Go to the Service tab. The Start mode is the only enabled option. Change its value to Disabled.

  3. 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. 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 a 0 in the TCP Dynamic Ports property, then remove it and enter the port you want in the TCP Port property.

  5. 5. Restart SQL Server for the changes to take effect.

  6. 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 as 8200. A full connection string example is as follows:

    Data Source= SQL1,8200;Initial Catalog=AdventureWorks2012;User Id=fred;Password=#Fr3d!;
    

How it works...

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.

There's more...

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.

 

Stopping unused services


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.

How to do it...

To stop an unused service, follow these steps:

  1. 1. On Windows Server 2008, click on the Server Manager icon or open the Computer Manager Microsoft Management Console (MMC) snap-in.

  2. 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. 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.

How it works...

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.

  • DNS Client: This caches DNS names locally.

  • 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.

 

Using Kerberos for authentication


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).

Getting ready

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.

How to do it...

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. 1. On your Windows Server 2008 domain controller's Start menu, go to the Active Directory folder, and click on ADSI Edit.

  2. 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. 3. Select SELF in the Group or user names list, and select the Write public information checkbox:

  4. 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.

How it works...

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.

There's more...

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.

 

Using extended protection to prevent authentication relay attacks


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.

How to do it...

To configure Extended Protection, follow these steps:

  1. 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. 2. If all your client computers support Extended Protection for Authentication, choose the Required for the Extended Protection property; otherwise, choose Allowed.

  3. 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. 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.

How it works...

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.

See also

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.

 

Using transparent database encryption


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.

How to do it...

First, you need to create a server encryption master key:

  1. 1. In a SQL Server Management Studio query windows, type the following commands:

    USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong_Password';
    
  2. 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. 3. Still being in Master, create a server certificate:

    CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';
    
  4. 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. 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. 6. Finally, activate the database encryption:

    ALTER DATABASE MyDatabase
    SET ENCRYPTION ON;
    

How it works...

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.

There's more...

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.

 

Securing linked server access


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.

How to do it...

  1. 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. 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. 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. 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

How it works...

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.

There's more...

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.

 

Configuring endpoint security


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.

How to do it...

To configure endpoint security, follow these steps:

  1. 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. 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. 3. You can view the state of the endpoints with this command:

    SELECT * FROM sys.tcp_endpoints;
    
  4. 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.

How it works...

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.

There's more...

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];
 

Limiting functionalities – xp_cmdshell and OPENROWSET


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.

How to do it...

First, let's see how to check and change the status of these features using a facet:

  1. 1. In SQL Server Management Studio, right-click on the Server node in Object Explorer. Click on Facets.

  2. 2. In the View Facets dialog box, select the Surface Area Configuration facet.

  3. 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. 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. 5.The run_value is 1 if it is enabled. You can disable or enable it by changing the value to 0 (disabled) or 1 (enabled), and issue a RECONFIGURE 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;
    

    Note

    The Surface Area Configuration options can also be set using Invoke-PolicyEvaluation SQL Server PowerShell cmdlet.

How it works...

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.

There's more...

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;

You cannot prevent a sysadmin member from using xp_cmdshell

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.

About the Author

  • Rudi Bruchez

    Rudi Bruchez is an Independent Consultant and Trainer based in Paris, France. He has 15 years of experience with SQL Server. He has worked as a DBA for CNET Channel, a subsidiary of CNET, at the Mediterranean Shipping Company (MSC) headquarters in Geneva and at Promovacances, an online travel company in Paris. Since 2006, he has been providing consulting and audits as well as SQL Server training. As SQL Server is evolving into a more complex solution, he tries to make sure that developers and administrators keep mastering the fundamentals of the relational database and the SQL language. He has co-authored one of the best-selling books about the SQL language in French, which was published in 2008 and is the only French book about SQL Server optimization. He can be contacted at http://www.babaluga.com/.

    Browse publications by this author
Book Title
Access this book, plus 7,500 other titles for FREE
Access now