Undeniably, security is a critical task
for small business to enterprise computing environments but still remains a
mystical science for many computer professionals. In this series of articles, I will discuss some of the
finer aspects of SQL Server security and also touch on OS, physical and
application security to open up this science for general discussion.
one we will cover the following:
Server Service Account
Server User Authentication
File Encryption (EFS)
the BUILTIN\Administrator SQLServer Login Account
a close eye on service packs and join security mailing lists
extended and non-extended procedure lock down
Please note that in all articles I
assume a reasonable working knowledge of SQL Server and do not mention the
step-by-step processes of security lockdown.
MSSQLServer Service Account
In production (and ideally in DEV and
TEST), never install SQL Server whilst logged in as the administrator.
Always create a domain or local user account with login as service rights
to run the MSSQLServer service under.
If you are using replication, use a domain user account over the local
Create a new NT user
Grant login as service rights
Grant administrator group privileges to this user
Create DB data and log file directories for database files to be created
Install SQL Server as this user
Once installed, shutdown the MSSQLServer service
Login as Administrator of the server and alter the users rights as
Full control of SQL Server binaries (install directory)
Full control of all directories to store database files (mdf,ndf,ldf)
For the above directories, remove the "everyone" group
Full control of the registery keys
or MSSQL$<INSTANCE> for named instances
And associated service registery keys for SQLAgent$InstanceName, MSSearch,
Revoke administrative rights to the SQL Server user account
Login as the SQL Server user and attempt to re-start the database, debug
See the section of xp_cmdshell as
this may affect the running of the command.
SQL Server User Authentication
Authentication occurs in two forms:
The problem with mixed mode user
authentication is that username/password combinations are passed free-text to
SQLServer (I am not 100% sure if this is overridden when using a encrypted
multi-protocol network library, if so, mixed authentication may be suitable).
In Windows authentication, the authentication part is handled by the
underlying NOS and as such, is very secure.
The authentication mode used by SQL Server is defined at installation
time but can be altered via a right click on properties within Enterprise
Manager and selecting the security tab.
Ideally, all user logins to the
SQL Server user databases use Windows Authentication.
This should be implemented as:
Application database roles hold object privileges, database login user
allocated to these
Logins in SQL Server associated with local groups on the database server
Local groups created on the server
Pointing to global groups on the domain controller
With users added to these global groups on the domain controller
Groups offer a lot of flexibility
but care must be taken when allocating access.
See below for information on using SQL Server Roles to facilitate object
Database File Encryption (Encrypted File System- EFS)
This is a Windows 2000 option only for
NTFS file systems. This
option facilitates file encryption via the Windows Crypto Architecture feature
and associated public key infrastructure features.
A good summary is found at:
Managing encryption via the
use cipher.exe, see
windows help for a thorough coverage.
Setting encryption via GUI:
You may be prompted with the following
during file encryption:
is based on the users profile, if you change it, you may not be able to
access the files again. Therefore ensure you de-encrypt first before altering
profiles. The user that
encrypted the files/folders will naturally have full access to the contents
of the file. Therefore, encrypt
the files as the user that runs MSSQLService.
encrypted other users can not edit, copy, move delete etc the files.
the encryption. Always set
other security properties on the file/folder to stop other users from
altering privileges or renaming the encrypted files.
Either way, the file will remain encrypted.
a folder is encrypted, all subsequent files placed in this folder will be
encrypted and only accessible by the users profile that initiated the folder
windows binaries cannot be encrypted.
can be set a the folder level, thus encrypting all files in the folder
(copied, or moved).
recommends folder encryption be used over individual files to ease
at encrypting your backup folder.
and all SQL Server 2k encryption options for views, stored procedures etc
utilises the Windows Crypto API.
What I have not tested with
Copying a profile, deleting it, creating a new one then restoring the old
Thorough performance testing and impacts to database backups to an EFS
For a good summary of the technical
architecture of EFS:
Always consider EFS or another
encryption method for database backups. Using
the password option with a database backup does not mean the backup is unusable
without it, it only means that recovering via SQL Server routines impossible
without the password. Therefore, it
is important to encrypt database backup files to ensure 3rd party software
cannot be used with the backup files.
Consider using the Windows Crypto API
As used above with EFS, programmers
should never write their own cryptography routines, look up the well-written
technical documentation on the Windows Crypto API at http://technet.microsoft.com
Re-working the BUILTIN\Administrator SQLServer Login
This is well described in Brian
Knight’s article “Removing NT Administrator as Sysadmins”. On installation of SQL Server a login account called
“BUILTIN\Administrators” is created, anyone allocated to the Administrators
NT local group on the server will have sysadmin access to the database server
via natural Windows Authentication. This
login will give the user DBO access to all system and user databases coupled
with sysadmin access. To alter this
and we can:
Remove the BUILTIN\Administrators login from within SQL Sever
Under NT, create another group called “SQL Server Administrators” or
something like that and place the SQL Server user that starts the SQL Server
service in it. Grant access to any
other server administrators access if they need sysadmin access.
In SQL Server, re-create the NT login account linked to the new “SQL
Server Administrators” NT group (don’t re-create BUILTIN\Administrators).
It is important that you don’t
re-create BUILTIN\Administrators, why? remember
that this account will naturally have DBO access to all databases within SQL
Server, even when its deleted and re-created.
The only thing is wont have on re-creation is sysadmin access.
If you created a local or domain user NT
login account to manage the SQL Server service, you will notice that a SQL
Server login is also created with sysadmin privileges.
The only difference between this login and BUILTIN\Administrators is that
no explit DBO access is given to any system or user database.
The SA account
As a minimum, you should always
set a password for the SA account even when you select pure windows
authentication. The SA account is
the master account for the entire SQL Server instance, in SQL Server 2k it can
not be removed, you can not revoke sysadmin
access, and by default it has DBA account for all databases in the SQL Server
instance. The DBA should:
never use it scheduled or regularly executed non-scheduled DTS jobs
alter the password regularly
guard it religiously
ensure backup and recovery documents clearly document its use, how to get
the password and associated responsibilities of using the login
What makes the SA account powerful is
simply the sysadmin (System Administrator) fixed server role.
Never grant this privilege to any other user, there is simply no reason
to in a production environment.
Keep a close eye on service packs and join security
Why should I apply service packs? well
check this out for an example:
The DBA should religiously read the
service pack list of fixes and carefully evaluate their eventual implementation.
On top of this, consider joining security mailing lists that are
dedicated with Windows NT environments, I believe such a service is available
The extended stored procedure
xp_cmdshell allows you to
shell out and execute an valid operating system command.
By default, all users allocated to the fixed system role
sysadmin (ie. SA account) have execute access.
This is a real tough command to
every project I have worked on to date has some need for it.
Because of the fact that SA is the only user with sysadmin access, rather
than creating a special account or looking at other work-arounds, the SA account
is used to execute the task. Classic
examples are stored procedures wrapped up in DTS jobs or supposed global
“administrative” functions. This
is problematic because:
now “application” databases are using the SA account and rely on it
to run their jobs.
altering the SA password has application impact
xp_cmdshell will be executed under the security context in which the SQL
Server service is running
Points a) and b) are obvious and the fix
is a simple matter of explicitly granting execute access to xp_cmdshell
via a role and allocating that role to a managed and secure database user whose
actual login is rarely used (ie. DTS connectivity only to run the command shell
Point c) is the very important.
If the database user is a member of sysadmin then, more than
likely, the user will have “local administrative” privileges to the server,
as this is the user running the MS SQLServer and associated services.
This is even more the reason why not to use the SA account.
If the user is not a sysadmin but has been granted execute access as
described above, then the SQL Server Agent proxy service user will be used
The SQL Server Agent proxy account can
be altered via xp_sqlagent_proxy_account (undocumented in BOL) which defines the
account used to run the SQLServerAgent service. This may be your SQLServer NT user if you are not using the
Administrator account (which is bad security practice), so you may decide to
alter this to another user with restricted access rights for finer control to
the operating system.
In the end, xp_cmdshell should be
carefully evaluated before using it. Ideally
it should be totally disabled (revoke execute permission)
for ultimate security. Look
at use isql jobs scheduled via NT and look closer at the options
available to you via DTS.
before running xp_sqlagent_proxy_account on a BDC.
Other extended and non-extended procedure lock down
The security check-list from
sqlsecurity.com mentions a variety of other extended stored procedures to lock
down where possible. In the end, I
believe its better to ‘disable’ them rather than simply removing them and
loosing functionality in Enterprise Manager or other 3rd party products.
With strict role management and user security they should not be
accessible. The list is
(not definitive by any means):
Locking down DTS
Disabling the following via a role or
revoking PUBLIC execution to lock down data transformation tasks (all in the
save dts into sysdtspackages
open dts package
adds new job executed by the SQLServerAgent service
add new step for a job created above
It should be very rare that DTS creation
and editing in production is required unless of course during emergency fixes.
You may have routines that dynamically create jobs, add job steps
(classic examples are backup routines) where the above may need to be rethought,
but in a majority of cases DTS can be safely locked out.
By default in SQL Server 2k the DTS job
is encrypted but unless you specify a password they remain editable.