SQL Server security best practices
SIO - Uttar Pradesh
Security is an important feature
which is to kept in mind while database design. Most of the people ignore this
fact and always design their database with default declarations provided by
the software vendor. The following listed points will give a brief idea
about the security features that are to be kept in mind while designing a
Once after successful Database design, the point that comes into picture is
installing this database at user organisations. People mainly tend to worry
about external attacks and hackers, ignoring the fact that the attack can also
be an internal one. It is essential to implement security, first at
organisation level, i.e. make sure, that right people have access to right
data. Chalk out a security plan to identify which users in the organisation
can see which data and perform which activities on the data.
After taking care of these facts, now comes the important security features
which as a database designer everyone should know. The following are the
points indented towards SQL Server over Windows, where as the same
can be implemented accordingly to other databases such as ORACLE etc.
The ideal implementation of security in a Windows NT/2000 environment with SQL Server 7.0/2000 database
server is as follows.
- 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
- Add these Windows
NT/2000 local groups to the required fixed server roles in SQL
- 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
- 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
- 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
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.
- 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.
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.
- 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.
- 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
- Do not save
passwords in your .udf files, as the password gets stored in clear
- Install anti-virus
software on the SQL Server computer, but exclude your database
folders from regular scans. Keep your anti-virus signature files up
- 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.
: NIC officials
are requested to send in their articles to wsg[at]up[dot]nic[dot]in
, to get featured in the "Knowledge Desk" link. Kindly
mention your Name, Designation and Contact Email