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 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
Authentication Modes and Logon Security
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:
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.
Server 2005 has the following authentication features:
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.
can integrate the Windows password policy and apply it to SQL Server logons
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.
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.
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.
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.
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.
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.
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
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
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
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
Instance-Level DDL Events
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:
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
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.
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.
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
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.
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 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.
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.
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
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.
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
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.
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.
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.
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
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:
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.
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
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
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.
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
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
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.