SQLServerCentral Article

SQL Server Security Part 1

,

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 retain

    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.

  • 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:

http://www.microsoft.com/technet/treeview/default.asp?url=/TechNet/prodtechnol/winxppro/reskit/prnb_efs_jals.asp

 

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 Login

Account

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 security

mailing 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

 

Rate

5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (2)

You rated this post out of 5. Change rating