Chapter 13
Server Rights
Abstract
This chapter talks about the permissions that various people should have on the production SQL Servers, as well as talking about the risks of running command line services.
Keywords
permissions
OS rights
windows services
Information in this chapter
• OS rights needed by the SQL server service
• OS rights needed by the DBA
• OS rights needed to install service packs
• OS rights needed to access SSIS remotely
• Console apps must die
• Default sysadmin rights
• Vendor’s and the sysadmin fixed server role
This chapter talks about the permissions that various people should have on the production SQL Servers, and about the risks of running command line services.
When working with Microsoft Azure SQL Database, as of the writing of this book, in summer 2014 the only section of this chapter that applies to you is the last, “Console Apps Must Die.” The remaining sections do not apply to Microsoft’s Microsoft Azure SQL Database as Microsoft handles the installation of the database engine and the installations of the Microsoft Azure SQL Database service packs and hotfixes. The SSIS pieces do not currently apply as Microsoft Azure SQL Database does not offer SSIS as part of the Microsoft Azure SQL Database product. Now all that being said, if you are using Microsoft Azure SQL Database if a VM role, or on Amazon’s EC2 service you have a normal standalone SQL instance so it needs to be secured just like any normal on premise SQL Server instance.
SQL Server Service Account Configuration
When you install Microsoft SQL Server one of the screens of the installer asks you to supply the accounts which the various SQL Server services should be running under. On this screen you can select the local system account, the network service account, a domain account, or a windows account for each of the SQL Server services. When installing SQL Server a decision must be made if a local account will be used for the services, if a single Windows account will be used for all the services or if a single separate Windows account will be used for each of the services.
One Account for All Services
The easiest approach from an administrative standpoint is to use a single domain account to run all SQL Services on every Microsoft SQL Server within the enterprise. However, from a security standpoint this technique is the weakest of the three techniques. This technique is easy to use because only a single domain account is created and that same account is used for each SQL Server in the enterprise. The downside, from a security perspective, to this technique is that if any SQL Server in the enterprise is compromised the attacker then has access to every other SQL Server in the enterprise. The reason that this is the case is that the account which the SQL Server runs under is typically a member of the sysadmin fixed server role which gives that account rights to everything within the database. The account is a member of the sysadmin fixed server role so that SQL Agent jobs can run and have full access to the database engine for running jobs such as index maintenance, database backups, and other actions which would require access to all the objects within the database engine. An additional maintenance overhead with this technique is that when a database administrator leaves the company and the SQL Server’s password must be changed all the SQL Server instances need to be restarted at once. From an ease of maintenance prospective if the SQL Server’s within the enterprise use the lock pages in memory setting this setting only needs to be granted to a single domain account and can be easily enough pushed out via GPO to all the SQL Servers in the enterprise, however, there are several options available which are discussed later in this chapter.
SQL Server’s AlwaysOn Availability Groups
SQL Server 2012 introduces the feature called “AlwaysOn Availability Groups” which is the new high availability feature, which was further enhanced in SQL Server 2014. When deploying AlwaysOn Availability Groups within a Windows Cluster all the SQL Server core engine services which will be hosting the AlwaysOn database must be running under the same Windows domain account, especially if you intend on using the AlwaysOn Availability Group Listener.
The reason that the accounts that are running all the SQL Server services need to be a domain account is that the Service Principal Name (SPN) needs to match for all the servers in the cluster, which requires that the service account be the same. If they are not the same when the Availability Group is moved to run on another machine users who attempt to connect to the database using Windows Authentication will receive Kerberos errors as Active Directory would not be able to correctly handle their authentication ticket.
One Account Per Sever
The next easiest approach from an administrative standpoint is to use a different domain account for each server which has Microsoft SQL Server installed, but the same account for running each of the installed SQL Server services. This technique requires that a new Active Directory account be created for each SQL Server which is installed in the enterprise. This is a secure technique given that the SQL Servers do not have access to each other’s data by default, unless specific rights are granted to one SQL Server to access another SQL Server’s data. The additional administrative effort comes from needing to manage all the various usernames and passwords in some sort of repository. With the one account for all servers approach discussed earlier in this chapter there is only a single account to be maintained, which can be easily enough given to new database administrators as needed. With this approach there are dozens if not hundreds of accounts which need to be managed and deleted when SQL Servers are removed from production.
With regard for password changing when using this technique things are both simpler and more complex all at once. The technique makes password changes harder because there are dozens or hundreds of domain account which now need to be changed, potentially all at once when a database administrator leaves the company. However, the process is simpler as not all the SQL Services in the enterprise need to be restarted at once. The passwords can be changed in smaller batches so that only a few servers and applications need to be restarted at a time greatly reducing the impact of the changes and greatly increasing the chance that the password will be changed.
One Account for Each Service
The approach which has the most administrative overhead yet is the most secure is having a single domain account for each of the services which the SQL Server service installs. This has the administrative overhead of requiring that anywhere from three to ten or so domain accounts be created each time a new SQL Server is installed within the enterprise. However, because each account only has the very minimum rights that are needed for that account to function if any account is compromised the amount of damage which the attacker can do with the compromised credentials is kept to a minimum.
Like with the one account per server this technique has administrative perks and downsides. The downsides become pretty obvious in a large enterprise which has hundreds or thousands of servers running Microsoft SQL Server, there are hundreds, thousands or even tens of thousands of domain accounts that would need to be changed. The upside is exactly like when using one account for each SQL Server in that they can be changed in patches. When you have this may accounts just for running SQL Server it is possible that it may take months or years to changes all the account passwords when they are changed as even with hundreds or thousands of SQL Servers at a company there may only be a small team of 10–20 people who manage the SQL Servers who would need to handle the password changes.
Using Local Service Accounts for Running SQL Server Services
Another option which is used much more frequently than it should be is to use the local system accounts to run the SQL Server services. While on newer versions of Windows such as Windows 2008 and higher this is less of a problem, on older versions of Windows this is an unacceptable security risk. There are two local system accounts which the SQL Server installer will let you pick during the installation process. One of the Local Service account and the other is the Network Service account. On older versions of Windows Server (Windows Server 2003 and earlier) these accounts were effectively members of the local administrators group on the server which gave the SQL Service more rights than it needed.
This becomes less of an issue on the newer versions of the Microsoft Windows operating system, specifically Windows 2008 and newer, because when services are run under these accounts they are not actually run under these accounts. New pseudo account are created called “NT SERVICE\MSSQLSERVER” or “NT SERVICE\SQLSERVERAGENT,” basically the domain name is “NT SERVICE” followed by the name of the service for the account name. This allows each service to function within its own security context and not have access to the resource of another service. It also allows for the granting of Windows security rights at a much more granular level such as the logon as a service right shown in Figure 13.1.
Figure 13.1 Showing various “NT SERVICE” being granted individual rights under Windows 2008 R2.
Granting additional rights to these service specific “NT SERVICE” accounts requires knowning the specific account which you want to grant rights to. This is due to the fact that these are special system accounts, which do not technically exist so you cannot search for them in the normal account search dialog boxes in Windows Local Security Policy editor or SQL Server Management Studio. As you cannot search for these accounts you must type the names in manually when granting them rights.
The upside to using these service specific accounts is that there are no passwords to change as you do not have access to these passwords. Another upside to these services is that if the service which the account is running becomes compromised the attacker would not have access outside of that service, unless that service account has been granted specific rights.
Like anything that has an upside there is a downside or two as well. These downsides include not being able to change the password of the account if it was to become compromised, and the inability to easily grant rights across servers. When running the SQL Server services under the network service account, the SQL Server can access rights outside of itself. And this works great, until you understand how the domain authentication process works when accessing these remote resources.
For an example lets say that we have a SQL Server called SQL1.contoso.local and a file server called files.contoso.local. The SQL Server service and the SQL Server Agent service are both configured to run under the local network service on their machine. There is a job which runs a T-SQL batch which includes the BULK INSERT statement which is used to load up a text file from the file server. In order to grant the SQL Server the right to access the network share and read the file on the file server we have to grant the computer account for SQL1.contoso.local rights to the network share. This is done by granting the Active Directory account CONTOSO\SQL1$ rights to the network share. So far so good. But we now find out that any process which is running under the network service account on SQL1.contoso.local now has rights to the network share and can read the file (or write to the file depending on how the rights to the network share are configured). This suddenly becomes a problem as any user who is logged into SQL1.contoso.local now also has the same rights to that network share that the SQL Service has. From a security perspective this is a pretty bad idea.
In addition to the NT Service accounts which have been discussed there are also Network Service accounts which are displayed as Network Service\MSSQLSERVER. These Network Service accounts work much the NT Service accounts except with the exception that the Network Service accounts have the ability to access network resources while the NT Service accounts only have the ability to access resources on the local server.
Changing the Service Account
Setting the service account can be done during installation or it can be changed after installation by using the SQL Server Configuration Manager. To make the change in the SQL Server Configuration Manager open the SQL Server Configuration Manager from the Windows Start Menu. Open it opens select “SQL Server Services” from the menu on the left then double click on the specific SQL Server service from the menu as shown in Figure 13.2.
Figure 13.2 SQL Server Configuration Manager.
From the SQL Server Service properties page which opens select the “Log On” tab. To use the Local System Account, the Local Service Account or the Network Service account select the “Built-in account” radio button and select the needed option from the dropdown menu as shown in Figure 13.3. To use a local or domain account, select the “This account” radio button and specify a Windows account and the password for this account.
Figure 13.3 SQL Server service properties dialog.
When using a local system account, network service account or local service account (a description of these three accounts can be found in Table 13.1) the account will be shown in the Account Name field as shown in the disabled Account Name field in Figure 13.3. This can look incorrect when you first open the service properties, but this is normal as it displays the service specific name instead of displaying a generic option from the dropdown menus. There is no need to change the radio button to “Built In” from “This Account” if a network service, local service or local system account is specified.
Table 13.1
Local Accounts
| Account Name | Description |
| Local system | The SQL Server Service runs under the account of the computer. The SQL Server Service only has access to resources on the local server. |
| Network service | The SQL Server Service runs under the account of the computer. The SQL Server Service has access to network resources, but under the context of the computer account not under its own account. |
| Local service | The SQL Server Service runs under the a service specific account called NT Service\MSSQLSERVER. |
After changing the start up service account to and from any of the available options the SQL Service must be restarted in order for the changes to take effect. To make the changes take effect click the Apply button, then “Yes” on the dialog box which appears which informs you that the SQL Service needs to be restarted as shown in Figure 13.4. Clicking “No” on the dialog box shown in Figure 13.4 will prevent the changes to the service configuration from being saved.
Figure 13.4 Confirm account change dialog box.
Credentials
Credentials are a funky addition to the SQL Server database engine. First introduced in SQL Server 2005 credentials were very misunderstood and infrequently used. Credentials are another special kind of login that can be created. They are used specifically along with SQL Server Agent proxy accounts, discussed later in this chapter. A credential in effect allows the SQL Service to impersonate another domain account when running processes outside of the SQL Agent process. The biggest difference between a traditional Windows account being granted rights to SQL Server via a Windows Login and a credential is that when creating a Windows login only the login is stored within SQL Server. When a credential is created both the Windows accounts username and password must be stored within the SQL Server database so that the SQL Server can use this information to start processes.
Creating a credential is quite easy. Within the object explorer panel connect to the SQL Server which the credential should be created on. Navigate to Security > Credentials then right click on Credentials and select the “New Credential” menu item from the context menu. On the window which opens name the credential and specify the username and password of the account which the SQL Server should use as shown in Figure 13.5.
Figure 13.5 Showing the filled out new credential screen.
The creation of credentials can also be done via T-SQL as well as the SQL Server Management Studio User Interface. Credentials are created by using the CREATE CREDENTIAL statement which was introduced in SQL Server 2005 when credentials where first introduced as shown in Example 13.1.
Example 13.1
Using the CREATE CREDENTIAL statement to create a new credential with T-SQL.
After a credential has been created it cannot be used for much by itself. You cannot use a credential to log into the SQL Server and you cannot use the EXECUTE AS statement with a credential. If you needed to use an account as a credential as well as give it the ability to log into the SQL Service database engine then a login which uses the same domain account as the credential would need to be added as a login to the SQL Server instance just as any domain account would be.
There are two system catalog views which are used with credentials. These are the sys.credentials and sys.principal_credentials system catalog views. The sys.credentials system catalog view contains the information about each credential such as its ID number, name, domain account, when it was last created and when it was last modified. The sys.principal_credentials system catalog view shows mappings between principals and credentials. When tradition logins are created (this does not apply to conainted users) they can be mapped to a specific credential. This allows the user to access the Windows OS via T-SQL and have the access at the file system via the credential. This is especially handy if you have staff who log into the SQL Server using a SQL Login and need to bulk insert data, or run other statements which might access the Windows operating system directly. While a credential can be mapped to many logins, a login can only be mapped to a single credential.
To map a credential to a login edit the login in question. Check the box which says “Map to Credential” and select the correct credential from the dropdown to the right of the checkbox as shown in Figure 13.6. After selecting the credential click the Add button to the right of the dropdown, also visable in Figure 13.6. The credential will be moved to the list shown below the dropdown menu, shown blank in Figure 13.6.
Figure 13.6 Adding a credential to a login using SQL Server Mangement Studio.
Adding a credential using T-SQL is done by using the ALTER LOGIN and specifying the login and the credential to be mapped together as shown in Example 13.2.
Example 13.2
Adding a credential to a login by using T-SQL.
If the password for the Windows account which the credential is using is changed the credential must be updated. If the password for the credential is not updated then any users or SQL Agent jobs (via a proxy account discussed later in this chapter) which attempt to use the credential will receive an error message.
SQL Server Agent Proxy Accounts
SQL Server Agent proxy accounts are tied directly to SQL Server Credentials, which were talked about earlier in this chapter to the point that SQL Server Agent proxy accounts cannot be used at all without having one or more credentials created on the server.
Proxy accounts are used to configure specific job steps within SQL Agent jobs to run under different Windows accounts, either local or domain, which are different than the account which the job owner is set to. There are a variety of reasons to do this, the most common is that there are network resources which need to be accessed by the job step which the account which the SQL Server Agent account does not have rights to and those rights cannot or would not be granted for one reason or another.
To create SQL Server Agent Proxy accounts connect to the SQL Instance you wish to add the proxy accounts to in object explorer and navigate to “SQL Server Agent” > “Proxy Accounts,” and right click on “Proxy Accounts” and select “New Proxy…” from the context menu which appears. Within the “New Proxy Account” window which opens name the proxy which you are creating and select the credential which will be used. On this screen you must also specify the SQL Server Agent subsystems which the proxy account can be used for. The list of items shown in the subsystem list (shown in Figure 13.7) should look pretty familiar as this is the same list of job step types which are available in SQL Server Agent.
Figure 13.7 Showing the creation of a proxy account for SQL Server integration services using SQL Server Management Studio.
Creating a proxy account can be done in T-SQL by using a few different stored procedures from the msdb system database. The first is sp_add_proxy which is used to add the proxy and map it to the credential. The second is the sp_grant_proxy_to_subsystem stored procedure which is used to grant the proxy account rights to a specific SQL Agent subsystem. The use of these procedures is shown in Example 13.3. The code in Example 13.3 references a @subsystem_id parameter which can be found by querying the dbo.syssubsystems system catalog view in the msdb database.
Example 13.3
Creating a proxy account using T-SQL.
In the case of the example shown in Figure 13.7 and Example 13.3 the proxy account which is created is only available for use by members of the sysadmin fixed server role. If users who are not members of the sysadmin fixed server role will be creating jobs, or if jobs will be created with their login as the own rights to these credentials needs to be granted to the logins for those users. These rights can be granted in SQL Server Management Studio by clicking on the Principals page when creating a new principal (or by editing the principal). On this page, shown in Figure 13.8 simply click the Add button and select from the shown list of logins the logins which need the rights to use the proxy. Rights to a proxy can also be granted to fixed or user defined server roles or msdb database roles from this screen as well.
Figure 13.8 Granting the SQL Login “anotherLogin” rights to a SQL Server Agent proxy.
Logins can be given access to rights to proxy accounts by using T-SQL as well as SQL Server Management Studio. This is done by using the sp_grant_login_to_proxy system stored procedure found within the msdb database as shown in Example 13.4.
Example 13.4
Granting the SQL Login “anotherLogin” rights to the “SharePoint” SQL Server Agent proxy account.
Anyone who is given rights to the proxy can use the proxy for any of the subsystems which that proxy has access to. If different users should be using the same credential for different subsystems then different proxies should be created against the same credential.
OS Rights Needed by the SQL Server Service
The Microsoft SQL Server service needs specific rights in order to function correctly. Older versions of Microsoft SQL Server required extremely high-level permissions at the Windows operating system (OS) to function, usually requiring being a member of the local administrators group. Starting in Microsoft SQL Server 2000, the Windows account that runs the SQL Server no longer needs to be a member of the local administrators group. The SQL Service needs just a few permissions granted to it. When installing Microsoft SQL Server 2000 or newer, the installer will automatically grant the Windows account, which will run the Microsoft SQL Server service, the correct rights, if it does not already have these rights.
Windows System Rights
The rights that are required to be granted to run the SQL are as follows.
• Log on as a service
• Replace a process-level token
• Adjust memory quotas for a process
If it becomes necessary to change the Windows account that the SQL Server runs under, then the new account that will be running the service will need to be granted these rights. If the “SQL Server Configuration Manager” is used to change the Windows service account, when using Microsoft SQL Server 2005 or newer these changes are made automatically for you. When using Microsoft SQL Server 2000, the permissions changes need to be made manually. If when using Microsoft SQL Server 2005 or higher the change is made using some method other than the “SQL Server Configuration Manager,” then these changes would also need to be made manually. The easiest way to make these changes is to add the new account to the local Windows group, which was created during installation. This group is named “SQLServerMSSQLUser$ServerName$InstanceName,” where the ServerName and InstanceName values are the name of the server and the SQL Server instance, respectively. When the instance is the default instance, the InstanceName value will be MSSQLSERVER instead of default.
When changing the Windows account that the Microsoft SQL Server Agent is running under, similar changes need to be made to the local group “SQLServerSQLAgentUser$ServerName$InstanceName,” again where the ServerName and InstanceName values are the name of the server and the SQL Server instance, respectively. This local group has the same security rights as the groups starting with SQLServerMSSQLUser. In addition, this group grants the Windows user that will be running the Microsoft SQL Server Agent service the “Replace a process token level” and the “Bypass traverse checking” rights. These additional rights are needed in order for the various impersonations the SQL Server Agent will do to function.
When changing the Windows account that the SQL Server Integration Services (SSIS) runs under, similar changes need to be made to the local group “SQLServerDTSUser$ServerName” where the ServerName value is the name of the server.
It is highly recommended that the SQL Server not be run under the local system account as the local system account grants the SQL Server more rights to the operating system than are actually needed. By granting the SQL Server more rights than it needs, either by giving the Windows account more rights than it needs or by having the services run under the local service account, an attacker can obtain local Administrator rights on the server.
The SQL Server should never, ever be run under a domain account that is a member of the “Domain Admins” group. The “Domain Admins” group is a domain group that is created in all windows domains, and gives all members of the group administrative rights to all computers within the Windows Domain. There are few, if any, valid reasons to run the SQL Server under a domain admin account. When an SQL Server is run under a domain admin account if attackers were to get into the SQL Server database and get access to the operating system via the SQL Server database, they would then have domain administrator rights and they would have the ability to create or delete accounts for users or computers, as well as make changes to any server within the company.
SQL Server’s NTFS Permissions
When first installing SQL Server, the installer will modify the permissions on the folders where the database files are installed in an attempt to limit access to the database files. When the Microsoft SQL Server service is not running under an account that has local administrator rights, additional NTFS permissions need to be accounted for. As one example, the SQL Server service will not be able to create databases at the root of a hard drive or mount a point when the Windows operating system is running Windows 2008 or higher. This is because the NTFS and UAC permissions do not allow users who do not have administrative access to create files at the root level of the hard drive.
Another feature, which many find to be an annoyance, is as follows: When using Microsoft SQL Server 2005 or higher and a database is detached, the NTFS permissions for all the database files that make up the database are changed, removing the NTFS permissions for all users except for that of the user issued the database detach command. In Microsoft SQL Server 2008 and higher, the Windows account that is running the SQL Server database will also be left as having NTFS permissions on the files that make up the detached database. On SQL Server 2008 and higher, the database can be reattached right away, as needed. On SQL Server 2005, before the database can be attached, the NTFS permissions must be changed to allow the SQL Server engine to have rights to the database files. This must be done for all the database files that make up the Microsoft SQL Server database.
Managed Service Accounts
Managed Service Accounts (MSAs) are a combination between domain accounts and virtual accounts such as the “Network Service\MSSQLSERVER” accounts which are discussed in this chapter. Managed Service Accounts are user accounts created within the Active Directory domain for a specific Windows service on a single Windows server. The benefit to the Managed Service Accounts over a traditional domain user account is that Managed Service Accounts never need to have their passwords changes as the Windows server which runs the service which uses the account will automatically change the password for the account every 30 days. Because the password change is automated and controlled by the Windows operating system itself the passwords are able to be changed without the need to take an outage of the SQL Server service. Managed Service Accounts are available starting in Windows Server 2008 R2 and SQL Server 2008 R2.
Managed Service Accounts are created within Active Directory just like any other user account, with the exception of having a dollar sign ($) placed after the username. While a normal domain user might look like “DOMAIN\UserName” a Managed Service Account will look like “DOMAIN\UserName$.” When the account is created the password field should be left blank as the Windows Operating System will set the password manually when you configure the service to use the account.
Due to the fact that the Windows Operating System will be what creates the password and changes the password, Managed Service Accounts are not able to be used on clustered instances of Microsoft SQL Server as of Windows Server 2012 R2 and SQL Server 2014 as there is no way for the Windows servers to exchange the password with other members of the Windows cluster. When using AlwaysOn Availability Groups, Managed Service Accounts should also not be used as you should be using the same domain account for each instance which is working as a database replica within the AlwaysOn Availability Group configuration (more information regarding AlwaysOn Availability Groups and their security requirements is available earlier in this chapter).
Microsoft SQL Server is configured to use a Managed Service Account just like any other account by using the SQL Server configuration manager shown in Figures 13.2 and 13.3. Managed Service Accounts make for a great way to configure domain user accounts for standalone SQL Server instances as the password is changed automatically by the server every time the password expires without any downtime to the SQL Server service.
OS Rights Needed by the DBA
A database administrator (DBA) does not need many rights to the SQL Servers operating system. This is because the DBA should not be managing the operating system that runs the Microsoft SQL Server Service. The management and patching of the base operating system should be handled by the members of the systems administration team and not by the DBA team.
The DBA may, however, from time to time need rights to the Windows OS, which will allow viewing performance metrics. If the company has a performance monitoring solution such as System Center Operations Manager (SCOM), the DBAs can grant the System Center Operations Manager rights to view the captured performance data. However, at times the DBAs may need the ability to collect real-time performance data directly from the performance monitor. The right to use a performance monitor remotely can be granted by adding the DBAs to the “Profile system performance” local system right. This local system right grants users that have the right the ability to connect to the system remotely and gather performance monitor data.
Some of these performance metrics can be accessed via the “sys.dm_os_performance_counters” dynamic management view from within the SQL Server instance. This allows the DBA to access the performance monitor data and to log it into a table or simply view the record set by querying the needed information from the dynamic management view without having any special operating system level permissions. The only performance monitor counters which are available within the sys.dm_os_performance_counters dynamic management view are the SQL Server specific performance counters. The number of counters which are available within this dynamic management view varies between versions and hardware configurations. For example, the number of buffer pool counters which are returned will depend on the number of NUMA nodes on the server.
User Access Controls
User Access Controls (UAC) are implemented in Windows to prevent applications from making system changes without permissions of the person running the application. This includes in many cases Microsoft’s own software which is running.
A perfect example of this can be found when access is granted to the SQL Server database instance via the local Administrators group on the Windows OS, and when an application is run on the server’s console by an administrator which needs access to the SQL Server database instance such as SQL Server Management Studio. If UAC is enabled on the server and SQL Server Management Studio is started normally then SQL Server Management Studio will get an error message stating that the user is not able to connect to the database instance. This is because of how UAC works. The way UAC blocks this access is that by default any user who is logged into the servers console and who is a member of the local administrators group will not have the local administrators group in their security token which is passed to the applications to see what group the user is a member of. Effectively this makes the applications think that the user is not a member of the local administrators group.
In order to bypass UAC the application must be launched with Administrative permissions. This is done by right clicking on the shortcut in the start menu for the application (or the actual application itself) and selecting the “Run as Administrator” option. If the user has administrative rights to the Windows OS then the local administrators group will be left within the security token and the SQL Server Instance will see that the user is a member of administrators group and the user will then have access to the SQL Server Instance.
UAC has a variety of options available which are discussed in more detail in Chapter 6.
Dual Accounts
When the DBA needs access to the server’s console, it is recommended that a second account be set up and used to access the server’s console. This is recommended for a couple of reasons. First, this allows the systems administrators to selectively allow the DBA to have access to the SQL Server’s console as needed. Second, it will prevent the DBA’s roaming profile from being loaded onto the server’s system drive. We want to keep user profiles off of the SQL Server’s hard drive so that the amount of extra data sitting on the server’s OS drive is minimal, and to keep the risk of any viruses on the server to the minimal. This does add a layer of complexity to the system as the DBA now needs to keep track of two usernames and passwords and the DBA will need to ensure that this admin account has the needed rights to both the database and the server so that the needed operations can be performed.
OS Rights Needed to Install Service Packs
While DBAs do not typically need rights to the Windows operating system, there are times when they do. Specifically, this is the case when a Microsoft SQL Server Service Pack or Microsoft SQL Server Hotfix needs to be installed on the Microsoft SQL Server Instance. The system administrators should not be installing these service packs and hotfixes against the Microsoft SQL Server instance as the systems administrator should not have rights within the Microsoft SQL Server instance in order to complete the service pack or hotfix instance.
When the Microsoft SQL Server service pack or hotfix needs to be installed, the DBA will need to be granted administrative rights to the Microsoft SQL Server’s operating system to complete the service pack and hotfix installation. This level of permissions is needed so that the service pack or hotfix can update the Microsoft SQL Server binaries within the “C:\Program Files\Microsoft SQL Server\” folder (or wherever the binaries are installed to).
OS Rights Needed to Access SSIS Remotely
By default, only members of the local administrators group have the ability to connect to the SQL Server Integration Services (SSIS). This can prevent developers from accessing the SSIS in the development environment, as well as preventing the SQL Server service from connecting to the SSIS.
Fortunately, correcting these rights is a fairly easy process to complete without granting the users rights to the SSIS without making the user a member of the local administrators group. The needed change involves granting the user or users additional rights to the MsDtsServer DCOM object (or the MsDtsServer object that has a number such as 100 after it) within the Windows operating system. This change is easily done in a few easy steps:
1. Click Start.
2. Click Administrative Tools.
3. Click Component Services.
4. When the Component Services application opens, open the “Component Services” tree menu.
5. Open the Computers tree menu.
6. Open the “My Computer” tree menu.
7. Select the “DCOM Config” tree menu.
8. Locate the MsDtsServer object in the window on the right. When using SQL Server 2005, the object will be called MsDtsServer. When using SQL Server 2008 (either the R1 or R2 release), the object will be called MsDtsServer100. When using SQL Server 2012 the object will be called MsDtsServer110. When using SQL Server 2014 the object is called “Microsoft SQL Server Integration Services 12.0.”
9. Right click on the correct object and select properties from the context menu that opens.
10. Select the security tab on the properties page that opens.
11. Under the “Launch and Activations Permissions” section, select the Customize radio button and select the Edit button.
12. Select or add the Windows user or Windows group (using groups is recommended to keep management simple) that needs the rights to connect and grant them the “Remote Launch” and “Remote Activation” rights or the “Local Launch” and “Local Activation” rights (or all four rights), depending on what rights are needed as shown in Figure 13.9.
13. Click OK to close this screen.
14. Under the “Access Permissions” section, select the Customize radio button and select the Edit button.
15. Select or add the Windows user or Windows group (using groups is recommended to keep management simple) that needs the rights to connect and grant them the “Remote Access” and/or “Local Access” permissions as shown in Figure 13.10.
16. Click OK on each screen and close the Component Services.
Figure 13.9 Showing granting addition launch and activation permissions to all members of the local Users group.
Figure 13.10 Granting remote access to the members of the local Users Group for the SSIS service.
Console Apps Must Die
One of the biggest nightmares from both a security and an operational perspective is to require that the server be logged in either on the console or on a remote session by a user so that console-based applications can be run.
From a security perspective, this is a problem because if a virus or worm were to be installed on the server, then either or both could easily be launched under that user’s account. This also requires that the person who needs to be logged into the server to run the applications have access to log into the server. This person is usually the developer who wrote the application, which means that the developer needs to have access to the production server.
From an operational perspective this creates problems because the server cannot be rebooted without the user, who needs to be running the applications and to be available to log into the server and restart the applications. When it comes to patching, this is a major problem since the system cannot be kept up to date with the needed security patches.
This problem can be fixed by ensuring that the application is written to be used as a Windows service. There is little difference between writing a Console application and a Windows service. The biggest difference is that the Windows service does not have a console output to write debugging information like the Console Application does. This change, however, is minor compared to the security and operational issues this solves.
Applications that cannot be converted to a Windows service application can usually be configured to run as a Windows service by using the svrany.exe and instsrv.exe applications, which is provided as part of the Windows Resource Kit. Using these applications any application can be configured to run as a Windows service. The syntax for these applications is shown in Example 13.5.
After creating a custom Windows service, you can verify that the service was created correctly by looking in the Windows registry under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MyServiceName. If the key exists, then the Windows service was created correctly. After verifying that the key was created, a couple of changes will need to be created under the folder. The first key that needs to be created is a key named “Parameters” with the class left blank. If the application requires no parameters, then leave the value blank; otherwise set the value to the parameters that the application needs. The second key which needs to be created is one named “Application,” which is of the class of REG_SZ. The value of the Application string should be the full path to the application as well as the application name and extension such as “c:\windows\system32\calc.exe.”
Once these changes have been made, the registry editor can be closed and the service can be started. If the service needs to be run under a domain account, it can be set up using the Services application from within the Administrative Tools folder in the Control panel. The service should not be configured to run under the developer’s account; it should instead be configured to run under a new domain account that is set up for the sole purpose of running these Windows services.
Fixed Server Roles
Fixed Server Roles, which you may have seen mentioned though out the book so far, have been in SQL Server since it was first introduced, they are in fact a holdover from SQL Server’s roots as a fork of the Sybase database engine. Fixed Server Roles are pre-configured roles within the database engine that grant privileges, typically high-level privileges. The most famous, and most abused of these fixed server roles, is the sysadmin fixed server role which gives any user whose login is a member of the sysadmin fixed server role complete control of the SQL Server instance without any ability to revoke or deny any rights from members of this role. There are a total of 9 fixed server roles including the sysadmin fixed server role and the public fixed server role which grants a minimum level of permissions so that the user has the ability to log into the database engine.
The bulkadmin fixed server role is the newest of the fixed server roles after being added in SQL Server 2005. This fixed server role grants its members the ability to bulk insert data using BCP, SSIS or the BULK INSERT statement without granting them any additional rights. Prior to SQL Server 2005 in order to bulk insert data into the SQL Server using any method required being a member of the sysadmin fixed server role. The bulkadmin fixed server role is the only fixed server role which Microsoft has added to the SQL Server Database Engine since the database engine was changed from the Sybase code base in SQL Server 4.2.
The dbcreator fixed server role gives its members the rights to create databases and restore database on the database instance.
The diskadmin fixed server role gives its members the rights to manage the disks within SQL Server specifically it allows them to add files to existing databases on the instance.
The processadmin fixed server role gives its members the rights to view processes and kill processes for any users on the SQL Server instance. This fixed server role can be a handy role to give to help desk personal if they need the ability to kill users processes so that nightly batch processing can continue.
The securityadmin fixed server role gives its members the ability to create and drop logins for the instance, create users within databases, change fixed and user defined database role membership and to grant rights within any database within the instance. The securityadmin does not have the permissions to modify the members of the fixed server roles.
The serveradmin fixed server role gives its members the ability to change most system wide settings and to shut down the SQL Server instance by using the SHUTDOWN command.
The setupadmin fixed server role gives its members the ability to create and drop linked servers.
User Defined Server Roles
User defined server roles, which were introduced with SQL Server 2012, allow for creating custom server wide roles for use when the fixed server roles do not grant the rights needed. User defined server roles can have any server wide right granted or denied to them allowing for a great deal of flexibility when granting server wide roles. As an example, if several development teams share a single SQL Server instance and all of the development teams needed the ability to run SQL Profiler against the SQL Server instance a user defined server role could be granted, then the developers could be placed in the role and the role granted the right to view the system state which grants them the rights to use SQL Server Profiler against the system. Without using user defined server roles the developers would need to be granted the view server state right to each of their domain accounts, or to the various domain groups which need the rights.
Creating a user defined server role can be done either in T-SQL as shown Example 13.6 or via SSMS as shown in Figure 13.11.
Example 13.6
Creating a User Defined Server Role using T-SQL.
Figure 13.11 Creating a user defined server role using SQL Server Management Studio.
There are a huge number of potential use cases for user defined server roles. This inlcudes creating roles for junior level database administrators that need limited but elevated permissions, creating roles for auditors, developers, managers, or end users that need consistent but elivated instance wide permissions on the SQL Server instance. Another great use for user defined server roles is to allow systems admistrators to be able to fail over an always on group.
AlwaysOn Availability Groups
As AlwaysOn is deployed within companies the systems adminstrators may insist that they have the ability to fail the availability group from one replica of the availability group to another without the assistance of the database administraton team just like they would be able to do when using a traditional SQL Server instance which is installed as a clustered instance. The database administrator would want to grant this level of access without making the systems administrator a member of the sysadmin fixed server role. This could easily be done by using a user defined server role. When making user defined server roles for AlwaysOn control the rights need to be deployed on all the replica servers of the availability group.
Instance Wide Permissions
There are a lots of server permissions which can be granted to user defined server roles. This includes granting rights to all the various endpoints on the server, logins, availability groups, or other server roles. There are also 31 rights in SQL Server 2012 and below which can be granted against the instance such as VIEW SERVER STATE or ALTER ANY CONNECTION. Some of these rights are rights which anyone who administrates a SQL Server instance should be familiar with, such as the VIEW SERVER STATE right as this right allows any user with the right to view the running processes for other users within the instance. This can be very useful for developers who need to view applications which are running within SQL Server without having administrative rights. These 31 instance wide rights are the same rights which would need to be granted to specific logins on versions of SQL Server which are older than SQL Server 2012.
SQL Server 2014 introduces three new instance level rights. These new instance level rights are “Select All User Securables,” “Impersonate Any Login,” and “Connect Any Database.” These three new permissions are part of the separation of duties framework which has been introduced in SQL Server 2014.
Select All Database Level User Securables
This permission allows users to query data from any object within any database within the SQL Server. Denying this right will deny the user from being able to query any data. When this permission is denied to a login it overrides all other permissions (with the exception of not overwriting permissions granted by the sysadmin fixed server role). As an example a user could be granted the CONTROL SERVER right which would allow them to backup all databases, view all data on the SQL Server and perform many administrative functions. Once the user is denied the SELECT ALL USER SECURABLES right the user will be able to perform all actions, except view user data within the database tables.
Impersonate Any Login
The IMPERSONATE ANY LOGIN right allows the login to use the EXECUTE AS statement to impersonate any login which exists on the SQL Server. This is very useful for having operations teams which need to verify that a user can or cannot perform a specific action without the operations team needing to have systems administration rights on the instance.
Connect Any Database
The CONNECT ANY DATABASE right allows the user to connect to any database without needing to have a user within the database. The user would have no rights within the specific database unless those were specifically granted, however, the user would be able to verify that the database existed and the status of the database (online, offline, in recovery, etc.). If rights had been granted to the guest user the users with the CONNECT ANY RIGHT would have the ability to access those resources. The same would apply to any rights which are granted to the PUBLIC fixed database role.
Fixed Database Roles
There are 10 fixed database roles which are created within each database. These roles grant a variety of rights within the database in which the fixed database roles exist. The fixed database role which grants the highest level of permissions is the db_owner fixed database role. This role grants the members of the role effectively full permissions to the database. Members of the db_owner fixed database role cannot have access to objects denied or revoked. Any user within the database can be added to the db_owner fixed database role. Whatever user is mapped to the dbo database user will automatically be a member of the db_owner fixed database role as will any members of the sysadmin fixed server role. Logins within the sysadmin fixed server role cannot be removed from the db_owner fixed database role without removing them from the sysadmin fixed server role. Only members of the db_owner fixed database role can add members to the db_owner fixed database role.
The members of the db_accessadmin fixed database role have the ability to add or remove users from being able to access the database. In this role, they can create database users for logins which already exist, as well as remove users from the database.
Members of the db_securityadmin fixed database role can also add users to any of the fixed database roles with the exception of the db_owner fixed database role. The members of the db_securityadmin fixed database role can also manage all permissions within the database.
Members of the db_backupoperator have the ability to backup the database. It is important to remember that members of the db_backupoperator fixed database role do not have the ability to restore the database. Restoring the database requires instance level rights to be granted to the users login.
The members of the db_ddladmin fixed database role can run any DDL statement within the database. This gives the members db_ddladmin fixed database role the ability to create, alter, or drop any object within the database. The members of the db_ddladmin fixed database role do not have the rights to view the data within the objects, only to change the objects.
The members of the db_datareader fixed database role have the ability to read the data in any table or view within the database. The members of the db_datawriter fixed database role have the ability to write to any table or view within the database. The members of the db_denydatareader cannot read any data within the database, no matter what other permissions have been granted to the user. Membership of the db_datareader fixed database role is overridden by the sp_denydatareader fixed database role if the user is a member of both fixed database roles. Access to read data via stored procedures is not effected by the db_denydatareader fixed database role.
The members of the db_denydatawriter cannot write any data within the database, no matter what other permissions have been granted to the user. Membership of the db_datawriter fixed database role is overridden by the sp_denydatawriter fixed database role if the user is a member of both fixed database roles. Access to write data via stored procedures is not effected by the db_denydatawriter fixed database role.
All uses who have access to the database are members of the public database role. This role grants the most basic rights to the database so that user can open the database without issue. While the public fixed database role can have additional rights granted to it, this should not be done as all users are members of the public fixed database which includes the guest account. If the guest account is enabled within the database then all users of the database instance will have all rights granted to the public role.
Fixed Database Roles in the MSDB Database
The msdb database has many more database roles created within it than any other database does by default. In addition to the normal nine database roles the MSDB database will have another 18 database roles created within it.
The members of the DatabaseMailUserRole have the ability to use database mail.
The members of the db_ssisadmin have the ability to create, modify and delete SQL Server Integration Services packages from the SSIS repository within the MSDB database.
The members of the db_ssisltduser, which stands for SQL Server Integration Services Limited User, have the ability to change and run their own SSIS packages but not the ability to change or run other users packages.
The members of the db_ssisoperator fixed database role have the ability to run any users SQL Server Integration Services package, but can only edit their own packages.
The members of the dc_admin fixed database role have administrator rights to the Utility Control Point which the instance is a member of. The members of this role have CREATE, READ, UPDATE and DELETE rights to the data collector configuration.
The members of the dc_operator fixed database role have the ability to manage the system data collector for the Utility Control Point. The members of this role have Read and Update rights to the data collector.
The members of the dc_proxy fixed database role have rights to view the system data collector for the Utility Control Point.
The members of the PolicyAdministratorRole have the ability to deploy and execute policies against the database using Policy Based Management.
The members of the ServerGroupAdministratorRole have the ability to manage the server group membership when using the instance as a central management server.
The members of the ServerGroupReaderRole have the ability to user the instance as a central management server as it grants the users the rights to read the objects which contain the central management server’s metadata.
The SQLAgentOperatorRole is the most privileged of the three SQLAgent fixed database roles. This role grants the members of the role the ability to run jobs, enable and disabled jobs and enable and disable job schedules. The members of the SQLAgentOperatorRole do not need to own the job or job schedule in order for the changes to be made. Enabling and disabling jobs and job schedules is only available to the members of the SQLAgentOperatorRole by using the stored procedures. They will not be able to make these changes via SQL Server Management Studio’s UI. The members of the SQLAgentOperatorRole are automatically granted all the permissions which are granted to the SQLAgentReaderRole and the SQLAgentUserRole.
The SQLAgentReaderRole is the middle role of the three SQLAgent fixed database roles. Members of the SQLAgentReaderRole can view the jobs on the SQL Server instance, and they can view the job history of any job on the instance. The members of the SQLAgentReaderRole can only modify SQL Agent jobs which they are an owner of. The members of the SQLAgentReaderRole will automatically be granted any rights granted to the SQLAgentUserRole.
The SQLAgentUserRole is the least privileged of the three SQLAgent fixed database roles. This role grants the user the ability to create and modify jobs, but they can only modify jobs which they own. By default they cannot delete job history, changing this right requires granting the user the right to execute the system stored procedure sp_purge_jobhistory. With this right granted the user would only be able to purge the job history for jobs which they own. Attempting to purge the job history for other jobs would return an error message back to the user.
The members of the TargetServersRole have the ability to use the “Multi Server Administration” feature of the SQL Server Agent.
The members of the UtilityCMRReader fixed database role have the ability to view the data captured in the Utility Control Point tables and views. This fixed database role also grants execute on a few of the Utility Control Point functions.
The members of the UtilityIMRReader fixed database role have the ability to view the data internal data capture tables of the Utility Control Point such as the dbo.sysutility_mi_configuration_internal and dbo.sysutility_mi_cpu_stage_internal tables among others.
The members of the UtilityIMRWriter fixed database role have the ability to modify the data within the internal data capture tables. The UtilityIMRWriter fixed database role is a member of the UtilityIMRReader fixed database role which is how the members of the UtilityIMRWriter fixed database role are granted access to read these same tables. The UtilityIMRReader and UtilityIMRWriter fixed database roles are used by the Utility Control Point for data processing, not for end user use. The end users who are granted rights to view data in the Utility Control Point will be members of the other groups mentioned in this section.
User-defined Database Roles
User defined database roles have been available for many releases now as they date back to the Sybase code base which SQL Server used to be based on. The user defined database roles are the most common and the recommended way to grant permissions to users within the database. User defined database roles are recommended in this way due to the fact that the permissions are guaranteed to be the same for all members of the group, and it keeps the permissions within the database much easier to deal with. If there were 10 users in a database who all needed the same rights, and those 10 users needed a new right granted to them, granting that right to a role is much faster and easier than granting the rights to the 10 specific users. This becomes doubly true as the application gets more users on it and those 10 users become 100 or 1000.
Any right which can be granted to an object within the database can be granted to a user defined database role. This includes granting rights at the object (table, view, procedure, function, certificate, etc.) level, or the schema level. User defined database roles can be nested giving even more flexibility. Not only can user defined database roles be nested by they can also have fixed database roles be made members of the user defined database roles as well as having user defined database roles being members of fixed database roles.
Creating a user defined database role is done quite easily using either the CREATE ROLE statement in T-SQL or by using SQL Server Management Studio. The CREATE ROLE DDL accepts only two parameters which are the name of the role as well as the owner of the role as shown in Example 13.7. The “Authorization dbo” portion of the statement shown in Example 13.7 is optional and only needs to be included when another user should be considered to be the owner of the role.
Example 13.7
Showing the syntax for the CREATE ROLE DDL statement.
Creating a user defined role in SQL Server Management Studio is quite easy. Navigate to the database which the role should be created in within SQL Server Management Studio’s object explorer. Within the database navigate to Security > Roles > Database Roles and right click on Database Roles and select “New Database Roles” from the context menu which opens. Enter the name of the role and the owner of the role, optionally setting the permissions and membership of the role then click OK as shown in Figure 13.12.
Figure 13.12 Creating a new role using SQL Server Management Studio.
Default Sysadmin Rights
When installing a new SQL Server, special attention needs to be paid to who becomes a member of the sysadmin fixed server role. The sysadmin fixed server role grants all members of the role full rights to the entire database engine. Anyone who is a member of the sysadmin fixed server role can do anything they want, and there is no way to stop them.
When installing Microsoft SQL Server 2000 or older, anyone who is a member of the Administrators group will automatically be made a member of the sysadmin fixed server role. This includes any domain administrators or any other users who are members of the Administrators group. This group is shown within the Microsoft SQL Server engine as the BUILTIN\Administrators login. Best security practices dictate that this group should be removed from the sysadmin fixed server role. Before removing this group from the sysadmin fixed server role, the DBAs should be added to the sysadmin fixed server role. Otherwise, there may not be any members of the sysadmin fixed server role to administrate the system, especially if the instance is running in Windows only authentication mode.
When using Microsoft SQL Server 2005 or later, the local Administrators group is not made a member of the sysadmin fixed server role by default. During installation of the Microsoft SQL Server 2005 and higher database engine, the installer asks for the groups or logins that should be made members of the sysadmin fixed server role. If no one is added to this list, then only the sa account will be a member of the sysadmin fixed server role. This is fine if this is the intended installation. However, if the server is installed using the Windows Authentication only mode, then there are no members of the sysadmin fixed server role on the instance and no one can manage the database instance. For this reason during the installation the DBAs need to be added to the sysadmin fixed server role so that the DBAs can manage the instance. When going through the installer, no error message is displayed if there are no Windows logins or groups that are assigned to the sysadmin fixed server role. This can make it very easy to install the Microsoft SQL Server instance without any members of the sysadmin fixed server role.
Vendor’s and the Sysadmin Fixed Server Role
All too often when installing a vendor’s application, the vendor says that the login to the SQL Server instance needs to be a member of the sysadmin fixed server role. And all too often, the vendor is lying and does not actually need to be a member of the sysadmin fixed server role. Vendors typically say that they need the greatest permissions possible because it is simply the easiest way to ensure that their application will have the rights needed to run. For the bulk of vendor installation processes, only the login that is used to install the application needs to be a member of the sysadmin fixed server role. The login that runs the vendor application typically only needs to be a member of the dbo fixed database role. However, the login may need additional rights beyond this to function, such as the ability to create jobs or to create databases depending on the application design.
Getting a vendor to say what server wide rights the application actually needs can be difficult at best, especially if the application has already been purchased. If the DBA can get involved in the purchase process early on, the DBA will have a better chance of getting the information needed from the vendor. When possible, applications that require being members of the sysadmin fixed server role should be avoided. When preventing the purchase of the application that needs to be a member of the sysadmin fixed server role is not possible – which is probably 99.9% of the time – the application should be placed on its own instance or its own virtual machine. In that way, the impact of having the application being a member of the sysadmin fixed server role can be mitigated, and the application does not have the ability to impact any other applications or databases within the enterprise environment. This placement of the application on its own instance or virtual machine may have additional licensing costs and should be explored beforehand and possibly billed back to the business unit purchasing the application.
Summary
Some specific permissions can be easily granted so that people only have the rights that are necessary to get done what they need to get done, without having full rights to the operating system. When security is taken to the extreme, employees will quickly find ways to work around it in order to get things done, bypassing the security that is designed to protect the data and the systems and the company.