Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Security Part 1

By Chris Kempster, (first published: 2002/01/14)

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

 

Total article views: 49997 | Views in the last 30 days: 32
 
Related Articles
FORUM

Modifying access to local Administrator account

Modifying access to local Administrator account

FORUM

BUILTIN\Administrators account overrides the permissions

BUILTIN\Administrators account overrides the permissions of all security accounts

FORUM

Denying Local Administrators accounts Sysadmin rights ?

How do I - Denying Local Administrators accounts Sysadmin rights ?

FORUM

What's the different between sql server accounts NT AUTHORITY\SYSTEM and BUILTIN\Administrators?

What's the different between sql server accounts NT AUTHORITY\SYSTEM and BUILTIN\Administrators?

FORUM

SQL server accounts for development

SQL server accounts, design

Tags
administration    
dts    
security    
sql server 6.5    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones