In  Part I on Security with SQL Server we did try to get into some aspects of security which as an administrator or developer one needs to know. We started with instance security, database security, authentication, authorization etc. Now we will get into aspects that I couldn’t cover before like Agent Service security, Object-level security, Execution contexts and many more. We will try to cover as much ground as possible in this article.

Securing the SQL Server Agent Service

The SQL Server Relational Engine includes an assistant service called SQL Server Agent, which handles administrative operations such as jobs, alerts, and defining operators. This service facilitates administrative tasks and is the fundamental element related to security because of the nature of the activities it performs. It could be a target of security attacks if it is improperly configured. With the context set, here are some guidelines to secure the SQL Server Agent service.

The SQL Server Agent service is a separate administrative process in Windows and must run under a Windows security context. In SQL Server 2000 and earlier versions, the security context of SQL Server Agent is the Local System account. This security context is a violation of the principle of least privilege because the Local System account has more permissions than those required by the SQL Server Agent service. And these in many cases were exploited by many hackers. Later in this article we will take a look at how this vulnerability got mitigated with SQL Server 2005’s Agent architecture.

Objects created in SQL Server Agent run under different security contexts, depending on the type of user who creates the object. For example, if the creator of the job is a member of the Sysadmin group, the job executes under the context of the SQL Agent service account or the user needs to specify a proxy account.

In SQL Server 2005, you can create multiple proxy accounts for multiple subsystems in SQL Server Agent. Subsystems include tasks such as SQL Server Integration Services Package Execution, ActiveX Script, PowerShell, Replication related and even Analysis Services Query. You can restrict access to a specific set of tasks and create multiple proxy accounts for each activity.

A proxy account is usually assigned to a subsystem, and a user is associated with the proxy account. If a user creates a step in a job and wants to run the step under the context of a specific proxy, it is possible only if the user is associated with the proxy account. You can control the security configuration by defining which user has the ability to use which proxies. By using this definition, a user can execute specific subsystem tasks under the context of a specific proxy.

The SQL Server Agent service includes a new security model to control access to this service. There are three new database roles created in the msdb system database to control access to SQL Server Agent: SQLAgentUserRole, SQLAgentOperatorRole, and SQLAgentReaderRole. Users with logons that belong to SQLAgentUserRole can create and add job steps. By default, no logon is a part of this database role. By now we have a fair idea to how granular the Agent Service has become since its SQL Server 2000 era inside of SQL Server 2005.

Securing Database-Level DDL Events

One of the risks to SQL Server security is the modification, creation, or deletion of objects inside a database. To control the risks associated with these activities, a new kind of trigger—data definition language (DDL) triggers—has been implemented in SQL Server 2005. Use the following guidelines to audit and monitor DDL events. In the earlier article we did discuss about the instance level DDL triggers though.

There are specific DDL events that support DDL triggers. These triggers allow you to cancel or audit the event that fired the trigger. This feature helps you to protect databases and instances from unwanted DDL events, such as a DROP TABLE statement.

For example, if you have a security requirement to store audit information about the attempts to modify the objects in a database, you can create a trigger that records the attempts and rolls back the operation. The trigger can then store the information related to the two activities in a table for auditing purposes. In the case of DDL events, you can use the EVENTDATA function to make the context information available. This function will return an XML value with the information related to the event.

There is a specific event class called Objects that has three events related to object management: Object:Altered, Object:Created, and Object:Deleted. By using these events, you can keep track of who modified what object, and when an object was modified in the database.

Object-Level Security Policy

The security that you apply to database objects is the final layer in your database security configuration. To secure database objects, permissions are granted at different layers of the SQL Server architecture. When defining object permissions, you should consider the granularity level and data access methods. The granularity level reduces the administrative overhead on SQL Server without violating the principle of least privilege. Access methods are used to work with the data in the database objects. These access methods will have an impact on the level of control that we can create for users’ actions.

The three levels for granting access to objects in a database are database level, schema level, and object level. The three main data access methods are ad hoc data access, stored procedures, and views.

Here are some rough guidelines for designing the granularity of permissions:

• Define permissions at various levels: When defining the granularity of permissions, you should apply access permissions at the object level. Defining permissions at the object level allows you to control, in a very granular and specific way, the principles that access specific resources.
• Reduce the administrative overhead by using higher scopes: If there are generalizations in your permission sets, you can use the higher levels (scopes) in the permission hierarchy to reduce the administrative overhead involved when using object-level permissions. For example, if you need to grant EXECUTE permissions to all stored procedures in a schema, you do not need to grant EXECUTE permissions to each stored procedure individually. Instead, you can grant EXECUTE permissions at the schema level. The same concept is valid if you need to grant EXECUTE permissions to all schemas—you can grant EXECUTE permissions at the database level, which will be applicable across all schemas.
• Choose appropriate permissions: Whether you choose permissions at the object level, schema level, or database level, you must keep in mind that the most restrictive permission takes precedence over other permissions. For example, a DENY permission at any level overrides permissions set at higher or lower granularity levels.

Here are some other aspects while designing the data access methods with SQL Server:

• Disable ad hoc users: Accessing tables directly could imply that users and applications understand the internal structure of the database. If you need to implement schema changes on tables, the process of determining the impact of these schema changes becomes complex. This is because there is no centralized place to make changes, and these changes must be made for each user and application.
• Create stored procedures to access data: Database administrators (DBAs) who are responsible for data security should create stored procedures to access the data in a database. Many business requirements and government regulations require DBAs to restrict access to sensitive data in their databases. Permitting ad hoc access violates these requirements and does not allow users or applications to use predefined stored procedures or views.
• Use predefined procedures or views to restrict data access: DBAs are responsible for users or applications that do not use predefined views or stored procedures.

Now that we have taken a tour on the access and the permissions part, let us move into another interesting aspect of Execution Context which is equally important and very much linked with the discussions we just started.

Designing the Execution Context

The execution context establishes a security identity against which permissions to execute statements or perform actions are checked. This is determined by the user login. To impersonate another principal, SQL Server supports the explicit use of the stand-alone EXECUTE AS statement, or the implicit use of EXECUTE AS clause on modules such as stored procedures, triggers, and user-defined functions.

Use EXECUTE AS to create a custom permission set. Instead of the user calling the EXECUTE AS statement or the module, SQL Server checks permissions against the login and user for the account to which the execution context has been switched. In other words, the user or login account is impersonated for the duration of the session or module. The execution context can be explicitly or implicitly switched at both the server and database level. The execution context of a session can be explicitly changed by specifying a user or login name in an EXECUTE AS statement.

The execution context of a module, such as a stored procedure, trigger, or user-defined function, can be implicitly changed by specifying a user or login name in an EXECUTE AS clause in the module definition. The impersonation scope of a context established within a database can be extended to other scopes if the authenticator is trusted in the target scope, and the source database must be marked as trustworthy. There are four ways in which you can use the EXECUTE AS clause: EXECUTE AS CALLER, EXECUTE AS 'user', EXECUTE AS SELF, and EXECUTE AS OWNER. The recommended EXECUTE AS clauses are EXECUTE AS 'user' or EXECUTE AS OWNER.

Use code signing in scenarios in which you do not trust all the content, but only trust a few modules in the database.

Securing Module Execution

The switching of the execution context has multiple granularities. Moreover, you might not want to trust all of the content in a database, but only a few specific modules in it. In such cases, consider implementing the security features by using digital signing. The objective of using digital signing is to include an intermediary whom you trust to create a certificate. This intermediary will sign the code that you want to trust with the certificate. The intermediary drops the private key from the database to ensure that nothing can be signed in another module. Digital signing prevents anyone who does not have access to the public key from executing the signed module.

The intermediary gives you a copy of the public key. You create another certificate with that public key, which you use as the base for creating a user in your database. You can then trust that user because it is signed by the certificate created with the public key. This user can execute modules that are signed by certificates created with the private key.

When you use certificates to sign individual modules, you define a security environment that will not require marking the complete database as trustworthy. Therefore, digital signatures ensure a finer control over impersonation and code security.

Security for SQLCLR Objects

SQL Server 2005 is integrated with the Microsoft .NET Framework. This integration enables you to take advantage of all the objects and the speed of development that is available in the different parts of the common language runtime (CLR). However, this option requires a security model to permit safe interaction between the CLR code and SQL Server.

When the CLR code is executed in SQL Server, the code must be compliant with all the rules defined in the security policies. If a policy allows a specific feature and another policy denies it, the denied option takes precedence. This means that the effective set of permissions in an assembly is a mutually allowed set of permissions defined in the policies affecting that assembly. You should catalogue most of the code as SAFE. If an assembly has a SAFE code and an EXTERNAL_ACCESS code, the codes should be separated into two different assemblies and catalogued separately.

When accessing external resources, CLR objects will use the security context of the SQL Server service account by default. However, this behavior can be overridden by implementing impersonation in the CLR code. To define which objects can be accessed, the security model is the same as that for every other object in SQL Server.

The code accessing the internal SQL Server resources will have the security context of the user executing the module. For example, if a user executes a CLR stored procedure that tries to access a table, the permissions of the user are evaluated to access that table.

Quick Tips for Alerting

To get your alerting mechanism done, it is critical to undestand the high level classification of the error levels for which alerts might be required. There can be 4 types of alerts and each have different characteristics.

• There are informational messages which are not severe and are status like information like the login / logout informations. These can be logged for further analysis but dont quite need immediate attention. Sometimes abnormal behaviours in these informational messages can be indicators of malicious activities like # of Invalid Login attempts in a short span of time can be an indicator of DOS attacks.
• Errors such as “permission denied” or a “failed logon” can be resolved based on user interaction. For example, the user could try to log on again by using the correct user name and password. To rectify such errors, the user could access a stored procedure that returns information from a table by using a view. Incidentally, these errors are very much corrected by the user itself. Although such errors could be managed by the client application code, they could be an indication of more severe errors or performance issues. For example, you can manage these errors by using the TRY...CATCH structure in the Transact-SQL code.
• Then comes the Software errors, These errors provide information about the status of SQL Server databases—for example, unauthorized disabling of a network library or a change of port because of an attack. You must define alerts for these types of errors.
• And finally the fatal errors, these are errors that require immediate action from the DBA Group—for example, corruption of a table or index due to a hardware problem or a suspect database or torn-page detection or corrupt check-sum page etc.

Conclusion

In this Part II we continuted from where we left on the other article and continuted to build the base with Agent Service specifics, Object-Level Security, Execution Context, CLR Object specifics and many more. Even this in my opinion might not complete all the aspects of security and hardening infrastructure one might want with working with SQL Server. But I would want to take this opportunity to iterate that these are basic concepts and SQL Server 2008 very much builds on these fundamentals. We might get specific features but the core features never change. Hope you enjoyed reading and do drop your comments.