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.
In part
one we will cover the following:
- SQL
Server Service Account
- SQL
Server User Authentication
- Database
File Encryption (EFS)
- Backup
File Encryption
- Windows
Crypto API
- Re-working
the BUILTIN\Administrator SQLServer Login Account
- The
SA account
- Keep
a close eye on service packs and join security mailing lists
- xp_cmdshell
- Other
extended and non-extended procedure lock down
- Locking
down DTS
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
user account.
a)
Create a new NT user
b)
Grant login as service rights
c)
Grant administrator group privileges to this user
d)
Create DB data and log file directories for database files to be created
in
e)
Install SQL Server as this user
f)
Once installed, shutdown the MSSQLServer service
g)
Login as Administrator of the server and alter the users rights as
follows
a.
Full control of SQL Server binaries (install directory)
b.
Full control of all directories to store database files (mdf,ndf,ldf)
c.
For the above directories, remove the "everyone" group
privilege
d.
Full control of the registery keys
i.
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer
ii.
HKEY_LOCAL_MACHINE \System\CurrentCOntrolset\Services\MSSQLServer
iii.
or MSSQL$<INSTANCE> for named instances
iv.
HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\Perflib
v.
And associated service registery keys for SQLAgent$InstanceName, MSSearch,
and MSDTC
h)
Revoke administrative rights to the SQL Server user account
i)
Login as the SQL Server user and attempt to re-start the database, debug
as required.
See the section of xp_cmdshell as
this may affect the running of the command.
SQL Server User Authentication
Authentication occurs in two forms:
a)
Mixed mode
b)
Windows authentication
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:
a)
Application database roles hold object privileges, database login user
allocated to these
a.
Logins in SQL Server associated with local groups on the database server
i.
Local groups created on the server
1.
Pointing to global groups on the domain controller
a.
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
level security.
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:
http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q273856.
Managing encryption via the
command line:
use cipher.exe, see
windows help for a thorough coverage.
Setting encryption via GUI:
You may be prompted with the following
during file encryption:
General Notes:
- 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.
Always shutdown the service before attempting the encryption. - Once
encrypted other users can not edit, copy, move delete etc the files.
Other users can rename the files through, but renaming will retainthe 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.
- Once
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
encryption.
- The
windows binaries cannot be encrypted.
- Encryption
can be set a the folder level, thus encrypting all files in the folder
(copied, or moved).
- Microsoft
recommends folder encryption be used over individual files to ease
administration.
- Look
at encrypting your backup folder.
- EFS
and all SQL Server 2k encryption options for views, stored procedures etc
utilises the Windows Crypto API.
What I have not tested with
de-encryption:
a)
Roaming profiles
b)
Copying a profile, deleting it, creating a new one then restoring the old
profile
c)
Thorough performance testing and impacts to database backups to an EFS
enabled folder.
For a good summary of the technical
architecture of EFS:
Backup Encryption
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 LoginAccount
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:
a)
Remove the BUILTIN\Administrators login from within SQL Sever
b)
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.
c)
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:
a)
never use it scheduled or regularly executed non-scheduled DTS jobs
b)
alter the password regularly
c)
guard it religiously
d)
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 securitymailing lists
Why should I apply service packs? well
check this out for an example:
http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;q274773
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
through winmag.
xp_cmdshell
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
administer. Why?
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:
a)
now “application” databases are using the SA account and rely on it
to run their jobs.
b)
altering the SA password has application impact
c)
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
tasks).
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
instead.
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.
NOTE: Check
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q292587
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):
OLE Automation
Sp_OACreate Sp_OADestroy
Sp_OAGetErrorInfo
Sp_OAGetProperty
Sp_OAMethod
Sp_OASetProperty
Sp_OAStop
Registry Access
Xp_regaddmultistring Xp_regdeletekey
Xp_regdeletevalue
Xp_regenumvalues
Xp_regread
Xp_regremovemultistring
Xp_regwrite
Other routines
sp_sdidebug
xp_availablemedia
xp_cmdshell
xp_deletemail
xp_dirtree
xp_dropwebtask
xp_dsninfo
xp_enumdsn
xp_enumerrorlogs
xp_enumgroups
xp_enumqueuedtasks
xp_eventlog
xp_findnextmsg
xp_fixeddrives
xp_getfiledetails
xp_getnetname
xp_grantlogin
xp_logevent
xp_loginconfig
xp_logininfo
xp_makewebtask
xp_msver
xp_perfend
xp_perfmonitor
xp_perfsample
xp_perfstart
xp_readerrorlog
xp_readmail
xp_revokelogin
xp_runwebtask
xp_schedulersignal
xp_sendmail
xp_servicecontrol
xp_snmp_getstate
xp_snmp_raisetrap
xp_sprintf
xp_sqlinventory
xp_sqlregister
xp_sqltrace
xp_sscanf
xp_startmail
xp_stopmail
xp_subdirs
xp_unc_to_drive
Locking down DTS
Disabling the following via a role or
revoking PUBLIC execution to lock down data transformation tasks (all in the
MSDB database):
sp_add_dtspackage
save dts into sysdtspackages
sp_enum_dtspackages
open dts package
sp_add_job
adds new job executed by the SQLServerAgent service
sp_add_jobstep
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.
Web References
http://www.microsoft.com/sql/techinfo/administration/2000/security.asp
http://www.ntsecurity.net/Articles/Index.cfm?ArticleID=23639
