About myself
My technical
skills
My favorites
My picture album
Shortcut keys
My code library
VB resources
SQLServer
resources
SQLServer books
Replication FAQ
Scripting
resources
ASP resources
Search my site
Sign my
guestbook
Contact
information
SQL Server Articles New
Database encryption for SQL Server and MSDE:
ActiveCrypt.com
|
NEW!!! Subscribe to my
newsletter:
Want to
keep in touch with the latest in SQL Server world? Email vyaskn@hotmail.com
with
'subscribe' in the subject line |
This article discusses the security model
of Microsoft SQL Server 7.0/2000 and security best
practices to help you secure your data. Special thanks to
my friend Divya Kalra for her valuable input and
content review.
Security is a major concern for the modern age
systems/network/database administrators. It is natural
for an administrator to worry about hackers and external
attacks while implementing security. But there is more to
it. It is essential to first implement security within
the organization, to make sure right people have access
to the right data. Without these security measures in
place, you might find someone destroying your valuable
data, or selling your company's secrets to your
competitors or someone invading the privacy of others.
Primarily a security plan must identify which users in
the organization can see which data and perform which
activities in the database.
SQL Server security
model
To be able to access data from a database, a user must
pass through two stages of authentication, one at the SQL
Server level and the other at the database level. These
two stages are implemented using Logins names and User
accounts respectively. A valid login is required to
connect to SQL Server and a valid user account is
required to access a database.
Login:
A valid login name is required to connect to an SQL
Server instance. A login could be:
- A Windows
NT/2000 login that has been granted access to
SQL Server
- An SQL Server
login, that is maintained within SQL Server
These login names
are maintained within the master database. So, it is
essential to backup the master database after adding
new logins to SQL Server.
User: A valid user account within a
database is required to access that database. User
accounts are specific to a database. All permissions
and ownership of objects in the database are
controlled by the user account. SQL Server logins are
associated with these user accounts. A login can have
associated users in different databases, but only one
user per database.
During a new connection
request, SQL Server verifies the login name supplied, to
make sure, that login is authorized to access SQL Server.
This verification is called Authentication. SQL Server
supports two authentication modes:
- Windows
authentication mode: With Windows
authentication, you do not have to specify a
login name and password, to connect to SQL
Server. Instead, your access to SQL Server is
controlled by your Windows NT/2000 account (or
the group to which your account belongs to), that
you used to login to the Windows operating system
on the client computer/workstation. A DBA must
first specify to SQL Server, all the Microsoft
Windows NT/2000 accounts or groups that can
connect to SQL Server
- Mixed mode:
Mixed mode allows users to connect using Windows
authentication or SQL Server authentication. Your
DBA must first create valid SQL Server login
accounts and passwords. These are not related to
your Microsoft Windows NT/2000 accounts. With
this authentication mode, you must supply the SQL
Server login and password when you connect to SQL
Server. If you do not specify SQL Server login
name and password, or request Windows
Authentication, you will be authenticated using
Windows Authentication.
Point to note is that,
whatever mode you configure your SQL Server to use, you
can always login using Windows authentication.
Windows authentication is the recommended security mode,
as it is more secure and you don't have to send login
names and passwords over the network. You should avoid
mixed mode, unless you have a non-Windows NT/2000
environment or when your SQL Server is installed on
Windows 95/98 or for backward compatibility with your
existing applications.
SQL Server's authentication mode can be changed using
Enterprise Manager (Right click on the server name and
click on Properties. Go to the Security tab).
Authentication mode can also be changed using SQL DMO
object model.
Here is a list of helpful stored procedures for managing
logins and users:
sp_addlogin |
Creates a new
login that allows users to connect to SQL Server
using SQL Server authentication |
sp_grantlogin |
Allows a
Windows NT/2000 user account or group to connect
to SQL Server using Windows authentication |
sp_droplogin |
Drops an SQL
Server login |
sp_revokelogin |
Drops a
Windows NT/2000 login/group from SQL Server |
sp_denylogin |
Prevents a
Windows NT/2000 login/group from connecting to
SQL Server |
sp_password |
Adds or
changes the password for an SQL Server login |
sp_helplogins |
Provides
information about logins and their associated
users in each database |
sp_defaultdb |
Changes the
default database for a login |
sp_grantdbaccess |
Adds an
associated user account in the current database
for an SQL Server login or Windows NT/2000 login |
sp_revokedbaccess |
Drops a user
account from the current database |
sp_helpuser
|
Reports
information about the Microsoft users and roles
in the current database |
Now let's talk about
controlling access to objects within the database and
managing permissions. Apart from managing permissions at
the individual database user level, SQL Server 7.0/2000
implements permissions using roles. A role is nothing but
a group to which individual logins/users can be added, so
that the permissions can be applied to the group, instead
of applying the permissions to all the individual
logins/users. There are three types of roles in SQL
Server 7.0/2000:
- Fixed server roles
- Fixed database
roles
- Application roles
Fixed server
roles: These are server-wide roles. Logins can
be added to these roles to gain the associated
administrative permissions of the role. Fixed server
roles cannot be altered and new server roles cannot be
created. Here are the fixed server roles and their
associated permissions in SQL Server 2000:
Fixed
server role |
Description |
sysadmin |
Can perform
any activity in SQL Server |
serveradmin |
Can set
server-wide configuration options, shut down the
server |
setupadmin |
Can manage
linked servers and startup procedures |
securityadmin |
Can manage
logins and CREATE DATABASE permissions, also read
error logs and change passwords |
processadmin |
Can manage
processes running in SQL Server |
dbcreator |
Can create,
alter, and drop databases |
diskadmin |
Can manage
disk files |
bulkadmin |
Can execute
BULK INSERT statements |
Here is a list of
stored procedures that are helpful in managing fixed
server roles:
sp_addsrvrolemember |
Adds a login
as a member of a fixed server role |
sp_dropsrvrolemember |
Removes an SQL
Server login, Windows user or group from a fixed
server role |
sp_helpsrvrole |
Returns a list
of the fixed server roles |
sp_helpsrvrolemember |
Returns
information about the members of fixed server
roles |
sp_srvrolepermission |
Returns the
permissions applied to a fixed server role |
Fixed database
roles: Each database has a set of fixed database
roles, to which database users can be added. These fixed
database roles are unique within the database. While the
permissions of fixed database roles cannot be altered,
new database roles can be created. Here are the fixed
database roles and their associated permissions in SQL
Server 2000:
Fixed
database role |
Description |
db_owner |
Has all
permissions in the database |
db_accessadmin |
Can add or
remove user IDs |
db_securityadmin |
Can manage all
permissions, object ownerships, roles and role
memberships |
db_ddladmin |
Can issue ALL
DDL, but cannot issue GRANT, REVOKE, or DENY
statements |
db_backupoperator |
Can issue
DBCC, CHECKPOINT, and BACKUP statements |
db_datareader |
Can select all
data from any user table in the database |
db_datawriter |
Can modify any
data in any user table in the database |
db_denydatareader |
Cannot select
any data from any user table in the database |
db_denydatawriter |
Cannot modify
any data in any user table in the database |
Here is a list of
stored procedures that are helpful in managing fixed
database roles:
sp_addrole |
Creates a new
database role in the current database |
sp_addrolemember |
Adds a user to
an existing database role in the current database |
sp_dbfixedrolepermission |
Displays
permissions for each fixed database role |
sp_droprole |
Removes a
database role from the current database |
sp_helpdbfixedrole |
Returns a list
of fixed database roles |
sp_helprole |
Returns
information about the roles in the current
database |
sp_helprolemember |
Returns
information about the members of a role in the
current database |
sp_droprolemember |
Removes users
from the specified role in the current database |
Application
roles: Application roles are another way of
implementing permissions. These are quite different from
the server and database roles. After creating and
assigning the required permissions to an application
role, the client application needs to activate this role
at run-time to get the permissions associated with that
application role. Application roles simplify the job of
DBAs, as they don't have to worry about managing
permissions at individual user level. All they need to do
is to create an application role and assign permissions
to it. The application that is connecting to the database
activates the application role and inherits the
permissions associated with that role. Here are the
characteristics of application roles:
- There are no
built-in application roles
- Application roles
contain no members
- Application roles
need to be activated at run-time, by the
application, using a password
- Application roles
override standard permissions. For example, after
activating the application role, the application
will lose all the permissions associated with the
login/user account used while connecting to SQL
Server and gain the permissions associated with
the application role
- Application roles
are database specific. After activating an
application role in a database, if that
application wants to run a cross-database
transaction, the other database must have a guest
user account enabled
Here are the stored
procedures that are required to manage application roles:
sp_addapprole |
Adds an
application role in the current database |
sp_approlepassword |
Changes the
password of an application role in the current
database |
sp_dropapprole |
Drops an
application role from the current database |
sp_setapprole |
Activates the
permissions associated with an application role
in the current database |
Now that we discussed
different kinds of roles, let's talk about
granting/revoking permissions to/from database users and
database roles and application roles. The following T-SQL
commands are used to manage permissions at the user and
role level.
- GRANT: Grants the
specific permission (Like SELECT, DELETE etc.) to
the specified user or role in the current
database
- REVOKE: Removes a
previously granted or denied permission from a
user or role in the current database
- DENY: Denies a
specific permission to the specified user or role
in the current database
Using the above
commands, permissions can be granted/denied/revoked to
users/roles on all database objects. You can manage
permissions at as low as the column level.
Note: There is no way to manage
permissions at the row level. That is, in a given table,
you can't grant SELECT permission on a specific row to
User1 and deny SELECT permission on another row to User2.
This kind of security can be implemented by using views and stored procedures effectively. Click here to read about
row level security implementation in SQL Server databases.
Just an FYI, Oracle has a feature called "Virtual
Private Databases" (VPD) that allows DBAs to
configure permissions at row level.
SQL Server security
best practices
Here is an ideal
implementation of security in a Windows NT/2000
environment with SQL Server 7.0/2000 database server:
- Configure SQL
Server to use Windows authentication mode
- Depending upon the
data access needs of your domain users, group
them into different global groups in the domain
- Consolidate these
global groups from all the trusted domains into
the Windows NT/2000 local groups in your SQL
Server computer
- The Windows
NT/2000 local groups are then granted access to
log into the SQL Server
- Add these Windows
NT/2000 local groups to the required fixed server
roles in SQL Server
- Associate these
local group logins with individual user accounts
in the databases and grant them the required
permissions using the database roles
- Create custom
database roles if required, for finer control
over permissions
Here is a security checklist and some standard
security practices and tips:
- Restrict physical
access to the SQL Server computer. Always lock
the server while not in use.
- Make sure, all the
file and disk shares on the SQL Server computer
are read-only. In case you have read-write
shares, make sure only the right people have
access to those shares.
- Use the NTFS file
system as it provides advanced security and
recovery features.
- Prefer Windows
authentication to mixed mode. If mixed mode
authentication is inevitable, for backward
compatibility reasons, make sure you have complex
passwords for sa and all other SQL Server logins.
It is recommended to have mixed case passwords
with a few numbers and/or special characters, to
counter the dictionary based password guessing
tools and user identity spoofing by hackers.
- Rename the Windows
NT/2000 Administrator account on the SQL Server
computer to discourage hackers from guessing the
administrator password.
- In a website
environment, keep your databases on a different
computer than the one running the web service. In
other words, keep your SQL Server off the
Internet, for security reasons.
- Keep yourself
up-to-date with the information on latest service
packs and security patches released by Microsoft.
Carefully evaluate the service packs and patches
before applying them on the production SQL
Server. Bookmark this page for the latest in the
security area from Microsoft: http://www.microsoft.com/security/
- If it is
appropriate for your environment, hide the SQL
Server service from appearing in the server
enumeration box in Query Analyzer, using the
/HIDDEN:YES switch of NET CONFIG SERVER command.
- Enable login
auditing at the Operating System and SQL Server
level. Examine the audit for login failure events
and look for trends to detect any possible
intrusion.
- If it fits your
budget, use Intrusion Detection Systems (IDS),
especially on high-risk online database servers.
IDS can constantly analyze the inbound network
traffic, look for trends and detect Denial of
Service (DoS) attacks and port scans. IDS can be
configured to alert the administrators upon
detecting a particular trend.
- Disable guest user
account of Windows. Drop guest user from
production databases using sp_dropuser
- Do not let your
applications query and manipulate your database
directly using SELECT/INSERT/UPDATE/DELETE
statements. Wrap these commands within stored
procedures and let your applications call these
stored procedures. This helps centralize business
logic within the database, at the same time hides
the internal database structure from client
applications.
- Let your users
query views instead of giving them access to the
underlying base tables.
- Discourage
applications from executing dynamic SQL
statements. To execute a dynamic SQL statement,
users need explicit permissions on the underlying
tables. This defeats the purpose of restricting
access to base tables using stored procedures and
views.
- Don't let
applications accept SQL commands from users and
execute them against the database. This could be
dangerous (known as SQL injection), as a skilled
user can input commands that can destroy the data
or gain unauthorized access to sensitive
information.
- Take advantage of
the fixed server and database roles by assigning
users to the appropriate roles. You could also
create custom database roles that suit your
needs.
- Carefully choose
the members of the sysadmin role, as the members
of the sysadmin role can do anything in the SQL
Server. Note that, by default, the Windows
NT/2000 local administrators group is a part of
the sysadmin fixed server role.
- Constantly monitor
error logs and event logs for security related
alerts and errors.
- SQL Server error logs can
reveal a great deal of information about your server. So, secure
your error logs by using NTFS permissions.
- Secure your
registry by restricting access to the SQL Server
specific registry keys like
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer.
- If your databases
contain sensitive information, consider
encrypting the sensitive pieces (like credit card
numbers and Social Security Numbers (SSN)). There
are undocumented encryption functions in SQL
Server, but I wouldn't recommend those. If you
have the right skills available in your
organization, develop your own
encryption/decryption modules using Crypto API or
other encryption libraries.
- If you are running
SQL Server 7.0, you could use the encryption
capabilities of the Multi-Protocol net library
for encrypted data exchange between the client
and SQL Server. SQL Server 2000 supports
encryption over all protocols using Secure Socket
Layer (SSL). See SQL Server 7.0 and 2000 Books
Online (BOL) for more information on this topic.
Please note that, enabling encryption is always a
tradeoff between security and performance,
because of the additional overhead of encryption
and decryption.
- Prevent
unauthorized access to linked servers by deleting
the linked server entries that are no longer
needed. Pay special attention to the login
mapping between the local and remote servers. Use
logins with the bare minimum privileges for
configuring linked servers.
- DBAs generally
tend to run SQL Server service using a domain
administrator account. That is asking for
trouble. A malicious SQL Server user could take
advantage of these domain admin privileges. Most
of the times, a local administrator account would
be more than enough for SQL Server service.
- DBAs also tend to
drop system stored procedures like xp_cmdshell
and all the OLE automation stored procedures
(sp_OACreate and the likes). Instead of dropping
these procedures, deny EXECUTE permission on them
to specific users/roles. Dropping these
procedures would break some of the SQL Server
functionality.
- Be prompt in
dropping the SQL Server logins of employees
leaving the organization. Especially, in the case
of a layoff, drop the logins of those poor souls
ASAP as they could do anything to your data out
of frustration.
- When using mixed
mode authentication, consider customizing the
system stored procedure sp_password, to prevent
users from using simple and easy-to-guess
passwords.
- To setup secure
data replication over Internet or Wide Area
Networks (WAN), implement Virtual Private
Networks (VPN) . Securing the snapshot folder is
important too, as the snapshot agent exports data
and object scripts from published databases to
this folder in the form of text files. Only the
replication agents should have access to the
snapshot folder.
- It is good to have
a tool like Lumigent
Log Explorer handy, for a closer look at the
transaction log to see who is doing what in the
database.
- Do not save
passwords in your .udf files, as the password
gets stored in clear text.
- If your database
code is proprietary, encrypt the definition of
stored procedures, triggers, views and user
defined functions using the WITH ENCRYPTION
clause. dbLockdown is a tool that automates the
insertion of the WITH ENCRYPTION clause and handles all the archiving of
encrypted database objects so that they can be restored again in a single
click. Click here to find out more information about this product.
- In database
development environments, use a source code
control system like Visual Source Safe (VSS) or
Rational Clear Case. Control access to source
code by creating users in VSS and giving
permissions by project. Reserve the 'destroy
permanently' permission for VSS administrator
only. After project completion, lock your VSS
database or leave your developers with just
read-only access.
- Store the data
files generated by DTS or BCP in a secure
folder/share and delete these files once you are
done.
- Install anti-virus
software on the SQL Server computer, but exclude
your database folders from regular scans. Keep
your anti-virus signature files up to date.
- SQL Server 2000
allows you to specify a password for backups. If
a backup is created with a password, you must
provide that password to restore from that
backup. This discourages unauthorized access to
backup files.
- Windows 2000
introduced Encrypted File System (EFS) that
allows you to encrypt individual files and
folders on an NTFS partition. Use this feature to
encrypt your SQL Server database files. You must
encrypt the files using the service account of
SQL Server. When you want to change the service
account of SQL Server, you must decrypt the
files, change the service account and encrypt the
files again with the new service account.
The above points pretty
much cover my security check list. Feel free to email me your comments and suggestions.
Be sure to check back once in a while, as I will be
constantly updating this page.
|