I have seen many articles about security and have seen customers get really paranoid when security breaches occur. Obviously, they have a valid reason to do so especially when the concern is about data security. In this digital aided world, everything boils down to just how can we keep our personal or company related data (which can be LOB data) in a secure manner and what are the tips and techniques we have in hand. This article will provides some of the guidelines and considerations for designing instance-level, database-level, and object-level security policies, and for defining security monitoring standards for instances and databases. Though not exhaustive, this will surely be able to form some starting base to work with in SQL Server.

Instance-Level Security Policy

An instance is the root object in the SQL Server structure for which you need to decide an operational mode, such as which authentication model is to be used and what type of security implementation is required for SQL Server Agent. You must analyze the impact of your decisions when implementing security because it could increase or decrease the surface of attack of your server and thereby affect the overall security of the server. In addition, you should also consider various security options, such as authentication modes and fixed-roles membership

Determining Authentication Modes and Logon Security

When defining security for an instance, you should consider the choice of authentication modes as one of the security options. SQL Server 2005 supports two types of authentication modes: Windows authentication and mixed-mode authentication. Use the following to determining an appropriate authentication mode and logon security:

Use Windows Authentication: When a user tries to log on to SQL Server using Windows authentication, a trusted connection is established between the user and SQL Server. In such a trusted connection, the user's security context is sent to SQL Server by authentication methods such as Windows NT LAN Manager (NTLM) or Kerberos V5 authentication protocol. Using Windows authentication is more secure than using SQL Server logons when you do not want to send logon information, such as user names and passwords, over the network. Windows authentication includes logon security policy features such as password length, password complexity, and account locks. In addition, Windows authentication supports Kerberos protocol, which is also a secure authentication mechanism.

SQL Server 2005 has the following authentication features:

• By default, SQL Server authentication uses encrypted communications for the logon process. This feature has been implemented by using a self-signed certificate to encrypt the user name–password packet.
• You can integrate the Windows password policy and apply it to SQL Server logons consistently.

Using these features, a company can have a policy that is applicable for multiple scenarios. For example, if the Windows account lock threshold of five logon attempts is reached, the SQL logon account can be blocked when a SQL Server logon tries to log on for the sixth time.

Implement Password Aging: SQL Server 2005 supports password aging. For example, when Microsoft Windows requests a password change in the next logon, the password aging feature is also implemented for SQL Server logons. Implementing password aging includes the capability of requesting a password change the next time a SQL Server logon tries to connect to the database.

When adding a new logon to SQL Server, you can define the server roles for that logon. You should apply the principle of least privilege to ensure that the logon is implemented securely and is compliant with the security policy.

Securing End-Points Strategy: You can create an endpoint by executing the CREATE HTTP ENDPOINT Transact-SQL DDL statement. This is something new that was introduced with SQL Server 2005. When an endpoint is created, only members of the sysadmin role and the owner of the endpoint can connect to the endpoint. You must grant a connect permission for other users to access the endpoint. You can do so by using the GRANT CONNECT ON HTTP ENDPOINT Transact-SQL DDL statement. Endpoints should be secure by design. To make the endpoint secure, it should be explicitly created and specified. There should not be any default endpoints or Web methods.

The next security consideration for endpoints is to apply security checks to objects. For example, to execute a stored procedure, a user should have connect permissions on the endpoint and execute permissions on the stored procedure. Another security consideration is that all requests must be authenticated. Clients must authenticate against SQL Server principals to submit requests.

A case in point, HTTP endpoints support authentication mechanisms such as Basic, Digest, NTLM, Kerberos, and Integrated. The Basic authentication mechanism is made up of an authentication header that contains the base64-encoded user name and password, separated by a colon. When using Basic authentication, the PORTS value cannot be set to CLEAR, and credentials sent as basic HTTP authentication must be mapped to a valid Windows login. You can use Basic authentication in scenarios in which the user that is granted permissions to the endpoint is a local user on the server computer.

Digest authentication is made up of the user name and password. This is then hashed with MD5, a one-way hashing algorithm, and sent to the server. The server has access to either the raw password or a stored MD5 hash that was created when the password was set. The server can then compare the stored calculated value with the one provided by the client. The credentials that are sent as a part of a Digest authentication over HTTP must be mapped to a valid Windows domain account. Local user accounts are not supported by Windows-based Digest authentication.

The NTLM authentication mechanism is a challenge-response protocol that offers stronger authentication than either Basic or Digest authentication. NTLM is implemented in Windows 2000 and later versions by a Security Support Provider Interface (SSPI).

Kerberos authentication is supported in Windows 2000, and later versions, by an SSPI. When Kerberos authentication is used, the instance of SQL Server must associate a Service Principal Name (SPN) with the SQL Server Service account it is running on.

Endpoints that are configured to support integrated authentication can respond with Kerberos or NTLM authentication as a part of the authentication challenge. In an Integrated authentication configuration, the server tries to authenticate the client by using whichever mechanism the client uses for requesting authentication. If the process fails for one Integrated authentication type, the server terminates the connection for the client without trying the other authentication types.

Securing Instance-Level DDL Events

Data definition language (DDL) triggers are an extension of data modeling language (DML) triggers. A DDL trigger is activated when a DDL event for which the trigger was created occurs on the server. Triggers have the ability to roll back operations, which supports the process of securing the server. A trigger also provides information related to the event. Use the following guidelines for securing instance-level DDL events:

• Use DDL triggers to prevent DDL events. You can use DDL triggers in the same way in which they are used in a DML operation. For server or database events, you can include ROLLBACK and cancel the operation that fired the trigger.
• Use DDL triggers to audit security. When structural changes occur, the administrator can include features inside the DDL trigger definition for sending an e-mail message using Database Mail, writing a log table, or for any other notification or auditing mechanism. Now that I have touched on this point, SQL Server 2008 introduces an out-of-box support for auditing and is very robust and scriptable. We will discuss on later articles.

Database-Level Security Policy

After securing the instance, the next level at which you should design a security policy is the database level. In SQL Server, until you specify a logon at the server level, by default, that logon has no access to user databases. A new feature in the SQL Server 2005 security infrastructure is the highly granular permissions framework. SQL Server uses the basic grant, deny, and revoke permission states of earlier versions.

In addition, the general permission scheme uses a grantee that receives permission at either the server or database level. The grantee also receives a securable that represents an object, such as a table or an entire database that needs protection.

Some of the new permission types available in SQL Server 2005 are CONTROL, ALTER, ALTER ANY, IMPERSONATE, and TAKE OWNERSHIP. Using these new permission types and levels, you can grant permissions to a user at the database level, and then remove similar permissions at the table level.

Using the new granular permission scheme, you can protect both metadata and data. SQL Server 2005 examines the permissions a principal has within the database and displays a catalog view of the object if the principal is the owner or has some permission within the object. The VIEW DEFINITION permission can grant permission to view metadata information even without other permissions in the object. SQL Server 2005 supports user-defined database, application, and fixed database roles.

User-defined database roles are used to group users who have the same security privileges within the database. You can add Windows user or group accounts to user database roles and use those roles to establish permissions on individual database objects, such as stored procedures, tables, and views. To create a least-privilege database account, you need to create a SQL login for a SQL account or a Windows account. You can then add this SQL login to a database user role and assign permissions to that role.

Application roles are similar to user- defined databases, but they do not contain users or groups. Application roles contain no members and are inactive by default. They are activated by an application by using a built-in stored procedure such as sp_setapprole. After an Application role is activated, the permissions granted to the role determine the data access capabilities of the application. Application roles remain active until the connection closes. In the case of application roles, it is unsafe to store the password in managed code that includes stored procedures such as sp_setapprole, which passes the role name and the password for each connection.

Usually, the application role credentials are passed to the database in clear text format, which is not safe. Therefore, you need to secure the network by using IPsec or SSL. If the security context of a connection changes, SQL application roles do not work in conjunction with connection pooling. You can use EXECUTE AS to enable application roles to access other databases and create their own specific permissions for high-level tasks, without compromising the security of the server.

Because application roles are a database-level principal, they can access other databases only through permissions granted in those databases to the guest user account. Therefore, any database without a guest user account will be inaccessible to application roles in other databases. In the application role, a user executes the application, and the application uses the sp_setapprole stored procedure to create an application role. The user can have the permissions assigned to the application role, thereby losing the permissions of the user and assuming the permissions of the application role. Therefore, it is not possible to audit users within an application role.

Fixed database roles are defined at the database level and exist in each database. SQL Server 2005 provides fixed roles such as db_datareader and db_datawriter. These built-in roles are present in all databases and can be used to quickly give a user read-specific and other commonly used sets of permissions within the database.

Designing Database Schemas

A schema is a container of objects inside a database that functions as a namespace for those objects. This container has an important impact on the way objects are referenced. In SQL Server 2000, objects are owned by a specific user. You can name these objects by combining their name and the qualification of their owner’s name.

With the new schemas in SQL Server 2005, there are changes in the way that database objects interact with the server. You need to carefully analyze these schemas to determine the impact of their deployment on the database. You also need to analyze these schemas from a management perspective. Use the following considerations for designing database schemas.

Object Qualification: In earlier versions, SQL Server objects such as tables were named using a four-part name: server name, database name, object owner, and object name. This four-part name is called the Completely Qualified Name of the object.

For example, in SQL Server 2000, a table named Products that is created by user A in the Sales database, in an instance named Server1, would have Server1.Sales.A.Products as its Completely Qualified Name. In SQL Server 2005, schemas replace the name of the owner. If the same table is created in a schema named Stores, it will have Server1.Sales.Stores.Products as its Completely Qualified Name.

Permissions: Schemas are also database-level securables. These securables contain objects and the new layer permissions that can be assigned to these objects. The following permissions can be applied to schemas:

• TAKE OWNERSHIP
• ALTER
• EXECUTE
• INSERT
• DELETE
• UPDATE
• SELECT
• REFERENCES
• VIEW DEFINITION

When you grant SELECT permissions to a database-level principal over a schema, you also grant SELECT permissions to the objects inside that schema. You must keep in mind that the most restrictive permission takes precedence. For example, if you have three tables in the schema and Table3 has an explicit DENY on it, the principal that was granted SELECT permissions on the schema will have access to Table1 and Table2, but not to Table3.

Keep in mind that a database-level principal must be the owner of a schema, and such a schema can be configured to be owned by a role so that multiple users in that role will have ownership permissions on that schema. With this configuration, there is no need to change the ownership of the schema when adding or removing database users.

Schemas have the ability to group objects based on the role these objects play inside a database. For example, all the tables related to products—such as ProductCategory, ProductSubCategory, and ProductDescription—could be a part of a schema named Product. This grouping makes the database more consistent and simple to understand, and does not affect ownership chaining because ownership rules still apply. An example of the same can be got from the Samples AdventureWorks database.

Database User Privileges

When designing permissions for users at the database level, you must consider the principle of least privilege. SQL Server 2005 includes schemas, which are the main containers of objects to which you can assign permissions. When a user does not require access to a particular database, the user must be removed from that database. After a user is removed, verify that the user has not been added again to the database by a member of the database administrators’ team.

You should use the predefined database roles as much as possible. This prevents the administrative overhead of adding new permissions. However, you must be aware of the whole set of permissions that a user will inherit on being added to these groups. You need to monitor memberships to highly privileged groups. For example, the db_owner role has complete control over a database. It is therefore defined as a high-privilege group. Monitor the members of this group closely.

You should also define a specific default schema for users. When designing schemas, remember that a user can have two kinds of relationships with schemas. A user can be the owner of one or more schemas, and by default, that user is the owner of the objects created in those schemas. Users must also have a schema as their work environment.

Conclusion

This is Part-I of the series and I can see there are tons and tons of these aspects to be talked. In the next article we will continue the discussion on the security aspect like securing objects, permissions, Execution contexts, Seuring modules, CLR Considerations and many more. Security must be thought as part of the initial product design and not as an after thought.