Data security is the
cornerstone of every business application. There is no arguing to the
fact that confidential information needs to be protected by
overlapping layers of security. Another aspect of security is data
integrity. It is incumbent on every organization to periodically
verify if the legal requirements of data security and integrity are
being adhered to, i.e. the data is not inadvertently modified,
correct values of data are stored, etc.
The database applications
that primarily use SQL Server can take advantage of the security
features built into the platform. Every new version of SQL Server
builds on its existing capabilities and the security features have
also seen some radical changes. However, a database application
cannot solely depend on these security features as other factors need
to be considered when planning for data security, such as application
requirements, execution environment, physical location, and more. The
best time in the development cycle to decide these requirements is
the design phase. It empowers you to prepare for all the security
threats and vulnerabilities that may be confronted in the foreseeable
Every SQL Server database
comprises a list of securable objects This SQL Server securable has
permissions defined for any individual, group, or process that needs
access to SQL Server. Two primary concepts are associated with the
SQL Server security framework, i.e. 'authentication' and
'authorization'. Authentication refers to the process of identifying
a user's identity or validating a process. On the contrary,
authorization is the process of identifying the access rights of a
principal (user, group, or process) on various securable resources
and determining the operations that can be performed for those
resources by the principal.
There are two types of
authentication modes in SQL Server, i.e. Windows Authentication and
Mixed mode authentication:
Windows authentication in
SQL Server is known as integrated security that allows Windows user
and group accounts to securely log on to SQL Server without having to
provide additional credentials. Generally, the following connection
string would suffice to log on to SQL Server using Windows
Mixed mode authentication
in SQL Server implies a user is authenticated by both Windows and SQL
Server. This type of authentication requires you to create SQL Server
logins and the user needs to provide his login credentials at run
time. SQL Server logins can be created by using the Windows password
policy mechanisms (if SQL Server is installed on Windows Server 2003
Database Roles in SQL Server
SQL Server incorporates
database and server roles to grant permissions to a specific group of
users rather than individual users.
Fixed Server Roles
Fixed server roles are
largely responsible for administering SQL Server. They are granted
specific permissions that cannot be changed. These server roles can
be granted access to the server without having any user account in a
database. You should diligently create members of this role as these
roles have unlimited scope.
Fixed Database Roles
Fixed database roles have
some associated set of permissions. These roles help you manage
groups of permissions.
You need to map logins to
database user accounts in order to allow users work with database
objects. Once this is done, users are added to the database roles for
assigning them specific sets of permissions associated with these
and User-Schema Separation
SQL Server security is
primarily based on ownerships. If you are an object owner, then your
permissions are assigned irrevocably, i.e. they cannot be changed.
Further, an object owner cannot be dropped from a database.
A user-schema is
considered as a unique namespace for grouping database objects. This
provides additional level of flexibility and makes managing database
permissions much easier. When referring to objects, you include the
schema name as follows:
Schema Owners and
You can make multiple
database principals as the owners of a schema. If you set certain
rules and permissions for a schema, they are automatically set for
all objects within the schema. You can also assign the same schema to
multiple database users.
All objects that are
added to a schema are owned by the owner of that schema. The objects
belonging to a schema may have associated granular permissions.
However, this introduces complexity in the SQL Server security
and Permissions in SQL Server
SQL Server has securable
objects and each securable object has permissions that are assigned
to a database principal for allowing access to this object.
The Principle of Least
In order to drive away
all data security threats, applications are created that can be
accessed only with a least-privileged user account. When a user logs
on using this account, he has limited access rights. The
administrative tasks are divided into several fixed server roles and
fixed server role has limited scope.
This principle should be
followed while assigning permissions to database users and roles.
It is easier to implement
a role-based security model rather than creating permissions sets for
individual users. You can use fixed database users and add users to
them to further simplify security administration. Roles are often
nested; however, this nesting may impact performance dramatically.
Encryption in SQL Server
SQL Server facilitates
data encryption using various built-in functions. It features an
internal certificate store that manages various certificates and keys
used for data encryption. These certificates and keys are stored
using a secret mechanism in which the layer above acts as a guard for
the certificates and key at one level. Symmetric key encryption is
ideal when dealing with large volumes of data.
Keys and Algorithms
You can use any of these
algorithms in SQL Server to implement symmetric key encryption: DES,
Triple DES, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES,
and 256-bit AES. These algorithms employ Windows Crypto API.
A proper implementation
of all the above techniques will suffice to counter most of the
security threats in your SQL Server environment.