SQL Server Security Update and Best Practices

1
SQL Server Security Update and Best Practices
ssqlnews_at_microsoft.com

• Ken Burge
• Sr. Technical Solutions Advisor
• Microsoft Corporation

2
Incidents Reported

• CERT/CC incident statistics 1988 through Q3 2003
• Incident single security issue grouping together
  all impacts of that that issue
• Issue disruption, DOS, loss of data, misuse,
  damage, loss of confidentiality

Source http//www.cert.org/stats/cert_stats.html
3
Evolving DB Threat Environment

• A decade ago, databases were
• Physically secure
• Housed in central data centers not distributed
• External access mediated through customer service
  reps, purchasing managers, etc.
• Security issues rarely reported
• Now increasingly DBs externally accessible
• Suppliers directly connected
• Customers directly connected
• Customers partners directly sharing data
• Data is most valuable resource in application
  stack
• Value increases with greater integration
  aggregation
• Opportunities for data theft, modification, or
  destruction

4
The Cost of Weak Security

• CSI/FBI

5
DB Attack Toolkit Well Armed

• Brute force dictionary-based password crackers
• Network sniffers and Port scanners
• Object code de-compilers and Quality debuggers
• Symbols typically available for problem
  determination
• Application source code not needed for deep
  attacks
• Leveraging cracked systems
• Credentials leverage escalate by steps
• Compute power host distributed denial of
  service
• DB Security tools consulting
• NGSSoftware (http//www.nextgenss.com/)
• Internet Security Services (http//www.iss.net/)
• Application Security Inc. (http//www.appsecinc.co
  m)
• And many others
• Community shared resources
• Exploit, risk, data sharing in the community

6
Who Cracks DBs?

• Who Cracks DBs?
• Black Hats in search of personal gain or
  system/data damage
• Security Professional Services
• Individuals in search of fame
• We interact with many of these folks
• We keep current on new attack vectors security
  research
• Black hats typically dont report
  vulnerabilities
• Most attacks not particularly innovative
• Vast majority via known patched vulnerabilities
• Most commercial DB security vulnerabilities found
  in research systems

7
Slammer Overview

• Slammer re-using existing ideas
• "The Slammer code is a straight cut-and-paste
  job" D. Litchfield
• Most attacks exploit known vulnerabilities
• Recent SQL Server Service Levels unaffected
• Single UDP packet delivery very effective (376
  bytes)
• Spread doubles every 8.5 seconds
• 90 of vulnerable computers in 10 min
• Max rate hit 55 million scans/second
• 74,855 systems affected in one minute

8
Reality CheckResponding to the Crisis

• Patches proliferating
• Time to exploit decreasing
• Exploits are more sophisticated
• Current approach is not sufficient

9
Microsofts Responsibility

• We understand your frustration
• We sympathize with the inconvenience youve
  experienced due to deploying patches and dealing
  with viruses
• Were accountable to helping you secure your
  environment

ssqlnews_at_microsoft.com
10
SQL Injection Attacks Explained

• Method to pass rogue statements into SQL Server
• Allows a hacker to access the rest of the
  network, probe the SQL Server, or create data
• Not just a SQL Server problem
• Runs under the authentication of whatever SQL
  Server login the application uses

11
SQL Injection Attacks Explained

• A query that looks like thisselect from login
  where login_nm InputFromScreen
• Can be injected with a single quote and a comment
  to look like thisselect from login where
  login_nm DELETE FROM login - -
• This is not a bug in SQL Server but in the
  application
• Firewall is bypassed with these types of attacks

12
SQL Injection Attacks Explained

• Based on the level of permissions your
  application has, a hacker could
• Drop your database
• Delete your data
• Insert a login for himself
• Create a Windows domain account with admin rights
• Gain access to your network
• Backup the SAM for hash cracking

13
SQL Injection Attacks Explainedhow to protect
yourself

• Validate all screen input
• Use the ADO command object with strict parameter
  validation
• Permissions
• Make sure SQL Server runs with the lowest
  possible permissions. It does not need admin
  privileges on the server.
• Make sure the login that your application uses
  doesnt have direct access to your data.
• Use stored procs to access all data

14
Getting SecureMicrosoft SQL Server security
best practices
15
Best PracticesService Packs and Hot Fixes

• Install SQL Server SP3 as soon as you can
• Install hot fixes as soon as you test for
  application regressions
• Sign up for the Microsoft security bulletin at
  http//www.microsoft.com/technet/security

16
Best PracticesThe big three

• Use Windows authentication if at all possible
• Can be difficult if you run in a multi-domain or
  an environment that has a strong firewall.
• Start with the lowest permissions possible for
  the user then work your way up
• Avoid easily guessed login names like ksmith.
  Instead use something more obscure and less
  obvious.

17
Best PracticesSA Account

• SQL Snake virus preyed on SQL Servers with SA and
  no password
• Make the password a hard PW to crack, even if you
  use Windows Authentication
• Should be virtually impossible to rememeber
• Never ever use the SA Account
• It is a known account which makes it an obvious
  target
• If a developer knows the SA password, he will use
  it because it is convenient. It encourages lazy
  security measures.

18
Best PracticesFirewall and Port Assignments

• Block SQL Server TCP/IP port 1433 and UDP port
  1434 from vulnerable areas (such as Internet)
• Set each SQL Server instance to use a unique
  TCP/IP port that is not 1433
• Note you cannot change UDP port 1434
• If you are using MSDE or multiple SQL Server
  instances the nodes could choose a random
  available port

19
Best PracticesDefault Logins

• Remove BUILTIN\Administrators account
• If needed, you should only explicitly give access
  to Windows administrators
• Dont start SQL Server with the localsystem
  account
• Start SQL Server and Agent with an account with
  minimal permissions
• SP3 fixes a problem where SQL Server Agent had to
  start with Windows domain account
• Always change login accounts for SQL Server
  through the Enterprise Manager interface.
• Much easier and sets the appropriate registry
  settings for you

20
Best PracticesCommunicating to SQL Server

• A strong firewall policy is a must
• Do not allow employees other than DBAs to have
  direct access to the production machines. This
  includes developers.
• Attempt to use SSL for communication with SQL
  Server
• Will see a slight performance hit
• Multiprotocol or IPSec are alternatives but not
  as string as TCP/IP and SSL

21
Best PracticesPublic Role and Guest Account

• Any login with rights to your SQL Server account
  is automatically given all the privileges that
  the Guest login has
• Any user with rights to a given database has all
  the rights given to the Public role has
• Never let the guest account have access to your
  database
• You cannot remove guest account from Master or
  TempDB
• Northwind and Pubs by default give the Guest
  account sysadmin privileges thereby creating a
  security hole

22
Best PracticesAuditing

• Always turn on Failed Login auditing
• Create alerts to email you when this occurs
• Increase the number of error logs you keep to
  prevent the hacker from cycling the error log to
  cover his tracks
• Audit failed access to objects (error 229)
• UPDATE sysmessages SET dlevel (dlevel 0x80)
  WHERE error 229
• C2 level auditing is handy, but will cause a
  performance hit

23
Best PracticesC2 level auditing

• Audits access to every object and use of
  permissions on the server
• Turn on by using sp_configure proc
• Sp_configure c2 audit mode,1
• Causes a noticeable performance hit
• If the drive that holds the logs fills, SQL
  Server will stop

24
Tighten AccessDirectories and registry keys

• Always install SQL Server on a NTFS partition
• Watch who has access to the SQL Server binaries
  and data files
• If you have the MDF and LDF files, the you have
  all the sensitive data you need
• You can encrypt the physical files using 3rd
  party or Windows encryption
• Watch who has access to the followign registry
  keys
• HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MSSQLSERVER
• HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MICROSOFT
  SQL SERVER\INSTANCE

25
Tighten AccessProtection of Extended Procs

• Revoke access to extended sprocs that read and
  write to the registry
• Xp_regwrite
• xp_regread
• xp_regremovemultistring
• xp_addmultistring
• xp_regdeletevalue
• xp_regenumvalues

26
Tighten AccessOA Sprocs

• Revoke access to stored procs that can create
  objects
• Sp_OACreate
• Sp_OADestroy
• Sp_OAStop
• Sp_OAGetProperty
• Sp_OASetProperty
• Sp_OAMethod
• Sp_OAGetErrorInfo

27
Tighten AccessOther extended sprocs

• Revoke access to stored procs tha can cause other
  damage
• Xp_commandShell
• sp_runWebTask
• sp_readWebTask
• sp_MSSetServerProperties
• sp_MScopyScriptFiles
• sp_MSsetAlertInfo
• Manually delete the underlying .dll file for each
  extended sproc you remove

28
Tighten AccessDTS Lockdown

• By default anyone who has a login to SQL Server
  can create a DTS package
• Protect your packages with a user and owner
  password
• Revoke public access to
• RTblDBMProps table
• Too many sprocs to list (See books online)
• Create role for users who need to use DTS
• As always, test and retest this before applying
  in production. Have a good rollback script ready.

29
Tighten AccessJob System Lockdown

• Too man sprocs here to list, but here are a few
• Sp_ add_job
• Sp_delete_job
• Sp_start_job
• Sp_purge_jobhistory
• You should create a SQL Server user defined role
  for all who need to be able to create and start
  jobs.

30
Tighten Access

• Revoke access to the guest account
• Remove or revoke access to certain system stored
  procedures
• Create roles for users who can create DTS
  packages and jobs
• Caution make sure you test all of these types of
  changes as you may affect an application or
  Enterprise Manager!

31
SQL Security Top 10

1. Install SQL Server Service Pack 3
2. Use Microsoft Baseline Security Analyzer to audit
   your server
3. Use Windows authentication mode
4. Isolate your server
5. Assign a very strong password to sa, and never
   use sa from that point on
6. Limit privileged level of SQL Server services
7. Disable SQL Server ports on your firewall
8. Use NTFS file system
9. Delete old setup files and remove Pubs and
   Northwind
10. Audit failed connections to SQL Server

32
Staying SecureMicrosoft SQL Server management
update
33
Microsofts Responsibility
Youve Told Us
Our Action Items
The quality of the SQL Server patching process
is low and inconsistent
Improve the Patching Experience
I need to know the right way to run an
enterprise on SQL Server
Provide Guidance and Training
I cant keep upnew SQL patches are released too
often
Mitigate Vulnerabilities Without Patches
There are still too many vulnerabilities in SQL
Server
Continue Improving Quality
34
Improve the Patching ExperienceNew Patch Policies

• Extending security support to June 2004
• Windows 2000 SP2
• Windows NT4 Workstation SP6a
• Security patches on a monthly predictable release
  cycle

• Allows for planning a predictable monthly test
  and deployment cycle
• Packaged as individual patches that can be
  deployed together

NOTE Exceptions will be made if customers are at
immediate risk from viruses, worms, attacks or
other malicious activities
35
Improve the Patching ExperiencePatch Enhancements
Your Need
Our Response
36
Patch Management Roadmap

• Unified infrastructure Microsoft Update
• 2 Standard Installers
• Common scanning for all tools
• Common standards for install behavior
• MBSA 1.2
• SUS 2.0
• SMS 2003

• Microsoft Baseline Security Analyzer
• Windows Update
• Software UpdateService
• SMS Feature Pack
• Patch Management Guides

37
Providing Guidance and TrainingIT Professionals

• Global Education Program
• TechNet Security Seminars
• Monthly Security Webcasts
• www.microsoft.com/events
• New Prescriptive Guidance
• Patterns and practices
• How-to configure for security
• How Microsoft Secures Microsoft
• Online Community
• Security Zone for IT Professionals
• Authoritative Enterprise Security Guidance
• http//www.microsoft.com/technet/security/bestprac
  .asp

38
Go Beyond Patching
Make customer more resilient to attack, even when
patches are not installed

• Help stop known unknown vulnerabilities
• Goal Make 7 out of every 10 patches installable
  on your schedule

39
Delivering Security Technologies

• Windows XP SP2
• Improved network protection
• Safer email and Web browsing
• Enhanced memory protection
• Beta by end of 2003, RTM based on customer
  feedback
• Windows Server 2003 SP1
• Role-based security configuration
• Inspected remote computers
• Inspected internal environment
• RTM H2 CY04

40
Continue Improving Quality
For some widely-deployed, existing products
Mandatory for all new products
Critical or important vulnerabilities in the
first
41
Continue Improving QualityMaking Progress
23 Products In the TwC Release Process
.NET Framework (for 2002 2003) ASP.NET (for
2002 2003) Biztalk Server 2002 SP1 Commerce
Server 2000 SP4 Commerce Server 2002 SP1 Content
Management Server 2002 Exchange Server 2003 Host
Integration Server 2002 Identity Integration
Server 2003 Live Communications Server
2003 MapPoint.NET
Office 2003 Rights Mgmt Client Server
1.0 Services For Unix 3.0 SQL Server 2000
SP3 Visual Studio .NET 2002 Visual Studio .NET
2003 Virtual PC Virtual Server Windows CE
(Magneto) Windows Server 2003 Windows Server 2003
ADAM
42
Resources
SQL Server Security Chip Andrews, David
Litchfield, Bill Grindley McGraw-Hill Osborne
Media ISBN 0072225157 Whitepaper SQL
Server 2000 SP3 Security Features and Best
Practices http//www.microsoft.com/technet/treevie
w/default.asp?url/technet/prodtechnol/sql/maintai
n/security/sp3sec/Default.asp
43
URLs

• General
• http//www.microsoft.com/security
• http//www.sqlserversecurity.com (See the
  lockdown.sql script at this site)
• Technical Resources for IT Professionals
• http//www.microsoft.com/technet/security
• Best Practices for Defense in Depth
• http//www.microsoft.com/technet/security/bestprac
  .asp
• How Microsoft Secures Microsoft
• http//www.microsoft.com/technet/itsolutions/msit/
  security/mssecbp.asp
• MSDN Security Development Tools
• http//msdn.microsoft.com/security/downloads/tools
  / default.aspx

44
Call To Action

• Read SQL Server SP3 whitepaper
• Install SQL SP3 as soon as possible
• Run MBSA on your servers
• Sign up for the quarterly SQL Server Newsletter
  by emailing ssqlnews_at_microsoft.com

You can get this presentation by emailing
ssqlnews_at_microsoft.com.