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

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:

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

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

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

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.

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:

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.

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

$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

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

Uninstall-ADServiceAccount -Identity SQL-SRV1

Remove-ADServiceAccount -Identity SQL-SRV1

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

http://blogs.technet.com/b/askds/archive/2009/09/10/managed-service-accounts-understanding-implementing-best-practices-and-troubleshooting.aspx

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

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.

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.

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:

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

Follow these steps in order to configure Windows Firewall:

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.

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.

SELECT name, protocol_desc, port, state_desc
FROM sys.tcp_endpoints
WHERE type_desc IN ('SERVICE_BROKER', 'DATABASE_MIRRORING'); 

netsh advfirewall firewall add rule name = "SQL Server" dir = in protocol = tcp action = allow localport = 1433,2383 profile = DOMAIN

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:

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.

To stop an unused service, follow these steps:

Here is a list of services started automatically by Windows that you could consider stopping:

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.

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.

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:

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.

To configure Extended Protection, follow these steps:

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.

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

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:

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.

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:

To configure endpoint security, follow these steps:

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

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

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;

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"'