|
|
|
Microsoft
SQL Server
Security
"because there is no patch ..for stupidity"
|
|
Whatever
your feelings about Microsoft, the bottom line
is that these servers are showing up everywhere.
If anyone tells you that security ends with
the OS, they are dead wrong.
Many times ..excellent
network and host-based security countermeasures
have been bypassed exposing the very heart of
the enterprise: all because of poor SQL Server
configuration.
Have you blocked access
to TCP 1433 and UDP 1434 from all un-trusted
clients? No? Then get to it!
Security is a major concern
for the modern age systems, network, and 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 the 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.
|
|
|
|
|
The
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. |
| x |
x
|
 |
Login:
A valid login name is required to connect
to a SQL Server instance
A login can be:
A Windows NT/2000 login that has been granted
access to SQL Server
A
SQL Server login, that is maintained within
SQL Server
|
| x |
x |
| x |
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. |
| x |
x |
|
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. |
| x |
x |
| |
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 process is called authentication.
SQL Server supports two authentication modes: |
| x |
x |
|
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 or workstation.
A DBA must specify to SQL Server all the
Microsoft Windows NT/2000 accounts or groups
that can connect to SQL Server. |
| x |
x |
|
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. |
| x |
x |
| |
One
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,”
then go to the “Security” tab).
Authentication mode can also be changed
using SQL DMO object model, allowing developers
to write programs to manage SQL Server security.
Here is a list of helpful stored procedures
for managing logins and users: |
| x |
x |
|
sp_addlogin:
Creates a new login that allows users to
connect to SQL Server using SQL Server authentication |
| x |
x |
|
sp_grantlogin:
Allows a Windows NT/2000 user account or
group to connect to SQL Server using Windows
authentication |
| x |
x |
|
sp_droplogin:
Drops a SQL Server login |
| x |
x |
|
sp_revokelogin:
Drops a Windows NT/2000 login/group from
SQL Server |
| x |
x |
|
sp_denylogin:
Prevents a Windows NT/2000 login/group from
connecting to SQL Server |
| x |
x |
|
sp_password:
Adds or changes the password for an SQL
Server login |
| x |
x |
|
sp_helplogins:
Provides information about logins and their
associated users in each database |
| x |
x |
|
sp_defaultdb:
Changes the default database for a login |
| x |
x |
|
sp_grantdbaccess:
Adds an associated user account in the
current database for an SQL Server login
or Windows NT/2000 login |
| x |
x |
|
sp_revokedbaccess:
Drops a user account from the current database |
| x |
x |
|
sp_helpuser:
Reports information about the Microsoft
users and roles in the current database
|
|
|
|
|
|
Controlling
access to objects within the database, and managing
permissions
|
| |
Apart
from managing permissions at the individual
database user level, SQL Server 7.0 and
2000 implements permissions using roles.
A role is nothing but a group to which individual
logins and users can be added, so that the
permissions can be applied to a group, instead
of applying the permissions to all the individual
logins and users. |
| x |
x
|
| x |
There
are three types of roles in SQL Server
7.0 and 2000:
|
| x |
x |
|
Fixed
server role descriptions |
| |
Fixed
server roles 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: |
| x |
x |
|
sysadmin:
Can perform any activity in SQL Server |
| x |
x |
|
serveradmin:
Can set server-wide configuration options,
shut down the server |
| x |
x |
|
setupadmin:
Can manage linked servers and startup procedures |
| x |
x |
|
securityadmin:
Can manage logins and CREATE DATABASE permissions,
also read error logs and change passwords |
| x |
x |
|
processadmin:
Can manage processes running in SQL Server |
| x |
x |
|
dbcreator:
Can create, alter, and drop databases |
| x |
x |
|
diskadmin:
Can manage disk files |
| x |
x |
|
bulkadmin:
Can execute BULK INSERT statements |
| xx |
xx |
| x |
Here
is a list of stored procedures that are
helpful in managing fixed server roles: |
| x |
x |
|
sp_addsrvrolemember:
Adds a login as a member of a fixed
server role |
| x |
x |
|
sp_dropsrvrolemember:
Removes an SQL Server login, Windows user
or group from a fixed server role |
| x |
x |
|
sp_helpsrvrole:
Returns a list of the fixed server roles |
| x |
x |
|
sp_helpsrvrolemember:
Returns information about the members of
fixed server roles |
| x |
x |
|
sp_srvrolepermission:
Returns the permissions applied to a fixed
server role |
| x |
x |
| |
Fixed
database role descriptions |
| x |
x |
| |
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: |
| x |
x |
|
db_owner:
Has all permissions in the database |
| x |
x |
|
db_accessadmin:
Can add or remove user IDs |
| x |
x |
|
db_securityadmin:
Can manage all permissions, object ownerships,
roles and role memberships |
| x |
x |
|
db_ddladmin:
Can issue ALL DDL, but cannot issue GRANT,
REVOKE, or DENY statements |
| x |
x |
|
db_backupoperator:
Can issue DBCC, CHECKPOINT, and BACKUP
statements |
| x |
x |
|
db_datareader:
Can select all data from any user table
in the database |
| x |
x |
|
db_datawriter:
Can modify any data in any user table in
the database |
| x |
x |
|
db_denydatareader:
Cannot select any data from any user table
in the database |
| x |
x |
|
db_denydatawriter:
Cannot modify any data in any user table
in the database |
| x |
x |
| x |
Here
is a list of stored procedures that are
helpful in managing fixed database roles: |
| x |
x |
|
sp_addrole:
Creates a new database role in the current
database |
| x |
x |
|
sp_addrolemember:
Adds a user to an existing database role
in the current database |
| x |
x |
|
sp_dbfixedrolepermission:
Displays permissions for each fixed database
role |
| x |
x |
|
sp_droprole:
Removes a database role from the current
database |
| x |
x |
|
sp_helpdbfixedrole:
Returns a list of fixed database roles |
| x |
x |
|
sp_helprole:
Returns information about the roles in the
current database |
| x |
x |
|
sp_helprolemember:
Returns information about the members of
a role in the current database |
| x |
x |
|
sp_droprolemember:
Removes users from the specified role in
the current database |
| x |
x |
| |
Application
role descriptions |
| x |
x |
| |
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: |
| x |
x |
|
There
are no built-in application roles. |
| x |
x |
|
Application
roles contain no members. |
| x |
x |
|
Application
roles need to be activated at run-time,
by the application, using a password. |
| x |
x |
| |
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:
|
| x |
x |
|
sp_addapprole:
Adds an application role in the current
database |
| x |
x |
|
sp_approlepassword:
Changes the password of an application
role in the current database |
| x |
x |
|
sp_dropapprole:
Drops an application role from the current
database |
| x |
x |
|
sp_setapprole:
Activates the permissions associated with
an application role in the current database |
| x |
x |
|
|
|
|
|
Permissions
|
| |
Granting
and revoking permissions to and 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. |
| x |
x
|
|
GRANT
|
| x |
Grants
the specific permission (SELECT, DELETE
etc.) to the specified user or role in the
current database |
| x |
x |
|
REVOKE |
| x |
Removes
a previously granted or denied permission
from a user or role in the current database |
| x |
x |
|
DENY
|
| x |
Denies
a specific permission to the specified user
or role in the current database |
| x |
x |
| x |
Using
the above commands, permissions can be granted,
denied, or revoked to users and roles on
all database objects. You can manage permissions
at as low as the column level.
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 creating user specific
views and granting SELECT permission on
these views to users. But it will be an
ugly solution in case of too many users
with varying data access requirements. 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
|
|
|
|
|
|
|
Standard
security practices and tips
|
|
Restrict
physical access to the SQL Server computer.
Always lock the server while not in use. |
| x |
x
|
|
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.
|
| x |
x |
|
Use
the NTFS file system, as it provides advanced
security and recovery features. |
| x |
x |
|
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. |
| x |
x |
|
Rename
the Windows NT/2000 Administrator account
on the SQL Server computer to discourage
hackers from guessing the administrator
password. |
| x |
x |
|
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. |
| x |
x |
|
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
the following URL for the latest in the
security area from Microsoft. |
| x |
x |
|
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. |
| x |
x |
|
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. |
| x |
x |
|
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. |
| x |
x |
|
Disable
guest user account of Windows. Drop guest
user from production databases using sp_dropuser. |
| x |
x |
|
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. |
| x |
x |
|
Let
your users query views instead of giving
them access to the underlying base tables. |
| x |
x |
|
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. |
| x |
x |
|
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. |
| x |
x |
|
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. |
| x |
x |
|
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. You will probably want to remove this
builtin group from SQL Server. |
| x |
x |
|
Constantly
monitor error logs and event logs for security
related alerts and errors. |
| x |
x |
|
Secure
your registry by restricting access to the
SQL Server specific registry keys like HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer. |
| x |
x |
|
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. |
| x |
x |
|
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. |
| x |
x |
|
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. |
| x |
x |
|
DBAs
generally tend to run SQL Server service's
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. |
| x |
x |
|
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. |
| x |
x |
|
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. |
| x |
x |
|
When
using mixed mode authentication, consider
customizing the system stored procedure
sp_password, to prevent users from using
simple and easy-to-guess passwords. |
| x |
x |
|
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. |
| x |
x |
|
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. |
| x |
x |
|
Do
not save passwords in your .udf files, as
the password gets stored in clear text. |
| x |
x |
|
If
your database code is proprietary, encrypt
the definition of stored procedures, triggers,
views and user defined functions using the
WITH ENCRYPTION clause. |
| x |
x |
|
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.
For a list of other best practices in a
database development environment, click
here to visit my database programming
guidelines and coding conventions. |
| x |
x |
|
Store
the data files generated by DTS or BCP in
a secure folder or share and delete these
files once you are done. |
| x |
x |
|
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. |
| x |
x |
|
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. |
| x |
x |
|
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. |
|
|
|
|
|
|
|
|
| "backup
routines balance expense and effort against risk" |
|
|
