Secure SQL Server configuration

1
Secure SQL Server configuration

• Pat Larkin
• Ward Solutions
• Pat.larkin_at_ward.ie

2
Session Prerequisites

• Understanding of network security essentials
• Hands-on experience with Windows 2000 Server or
  Windows Server 2003
• Experience with Windows management tools
• Hands-on experience with SQL Server 2000 and
  management tools

Level 300
3
Agenda

• Introduction
• Protecting the application layer
• Protecting SQL Server 

4
Defense in Depth

• Using a layered approach
• Increases an attackers risk of detection
• Reduces an attackers chance of success

Policies, Procedures, Awareness
ACL, encryption
Physical Security
Data
Application hardening, antivirus
Application
OS hardening, update management, authentication,
HIDS
Host
Internal Network
Network segments, IPSec, NIDS
Firewalls, VPN quarantine
Perimeter
Guards, locks, tracking devices
User education
5
Why Application Security Matters

• Perimeter defenses provide limited protection
• Many host-based defenses are not application
  specific
• Most modern attacks occur at the application
  layer

6
Why Data Security Matters

• Secure your data as the last line of defense
• Configure file permissions
• Configure data encryption
• Protects the confidentiality of information when
  physical security is compromised

7
Application Server Best Practices
Configure security on the base operating system
Apply operating system and application service
packs and patches
Install or enable only those services that are
required
Assign only those permissions needed to perform
required tasks
Application accounts should be assigned minimal
permissions
Apply defense-in-depth principles to increase
protection
8
Defense in Depth
9
Agenda

• Introduction
• Protecting the application layer
• Protecting SQL Server 

10
Basic Security Configuration

• Apply service packs and patches
• Use MBSA to detect missing SQL updates
• Use SQL Critical Update Kit
• Disable unused services
• MSSQLSERVER (required)
• SQLSERVERAGENT
• MSSQLServerADHelper
• Microsoft Search
• Microsoft DTC

11
What MSBA typically checks on SQL Server

• Excessive rights granted to the guest account.
• SQL Server running on a system that is also a
  domain controller.
• Improper configuration of the Everyone group,
  providing access to certain registry keys.
• Improper configuration of SQL Server service
  accounts.
• Missing service packs and security updates

• Too many members of the sysadmin fixed server
  role.
• Granting of right to create CmdExec jobs to roles
  other than sysadmin.
• Blank or trivial passwords.
• Weak authentication mode.
• Excessive rights granted to the Administrators
  group
• Incorrect ACLs on SQL Server data directories.
• Plain-text sa password in setup files

12
Common Database Server Threats and
Countermeasures
13
Database Server Security Categories
14
Network Security

• Restrict SQL to TCP/IP
• Harden the TCP/IP stack
• Restrict ports

15
Operating System Security

• Isolate SQL from all other server roles
• Configure the SQL Server service account with the
  lowest possible permissions
• Delete or disable unused accounts
• Secure authentication traffic

16
Logins, Users, and Roles

• Use a strong system administrator (sa) password
• Remove the SQL guest user account
• Remove the BUILTIN\Administrators server login
• Do not grant permissions for the public role

17
Access Permission Models
18
Alternate Model
19
Files, Directories, and Shares

• Use NTFS (EFS) non system volumes
• Verify permissions on SQL Server installation
  directories
• Verify that Everyone group does not have
  permissions to SQL Server files
• Secure setup log files
• Secure or remove tools, utilities, and SDKs
• Remove unnecessary shares
• Restrict access to required shares
• Secure registry keys with ACLs

20
SQL Security

• Set authentication to Windows only
• If you must use SQL Server authentication, ensure
  that authentication traffic is encrypted

21
Authentication Modes
22
SQL Auditing

• Log all failed Windows login attempts
• Log successful and failed actions across the file
  system
• Enable SQL Server login auditing
• Enable SQL Server general auditing

23
Securing Database Objects

• Remove the sample databases
• Secure stored procedures
• Secure extended stored procedures
• Restrict cmdExec access to the sysadmin role

24
Using Views and Stored Procedures

• SQL queries may contain confidential information
• Use stored procedures whenever possible
• Use views instead of direct table access
• Implement security best practices for Web-based
  applications

25
Restrict Remote Administration

• Restrict number of Windows Administration
  accounts
• Restrict the tools
• Internet services manager
• Terminal Services
• SQL Server Enterprise Manager restrict to a
  single windows user
• Restrict the computers using IPSec

26
Securing Web Applications

• Validate all data input
• Secure authentication and authorization
• Secure sensitive data
• Use least-privileged process and service accounts
• Close all unneeded DB connections
• Configure auditing and logging
• Use structured exception handling

27
Knocking Down The Side Door
//Three mistakes in this statement
alone SqlConnection conn new
SqlConnection() conn.ConnectionString
"data sourcePYN-SQL" "initial
catalogpubs" "user idsa"
"passwordpassword" "persist security
infoTrue "packet size4096" conn.Ope
n() //Don't do this at home folks SQL Query
Composition string strQuery strQuery "select
from Users where UserName '" username.Text
"' and Password '" password.Text
"'"
Oh, thats bad, not using DSNs
Worse, privileged user
with a lame password
28
But Wait It Gets Better
int rowCount ds.Tables"Users".Rows.Count if(r
owCount gt 0) // If we get back something... //.
..we must be succesfully logged
in Session"LoggedIn" true // Store the
username in a session variable Session"username"
username.Text ... ltbodygt ltaspLabel
id"Username" runat"server"gt Label lt/aspLabel
gt ... private void Page_Load(object sender,
System.EventArgs e) ... Username.Text
Session"username".ToString()
This is the user name from the form
Which we cross-site script onto the page!
29
Top Ten Things to Protect SQL Server
Install the most recent service pack
1
Run MBSA
2
Configure Windows authentication
3
Isolate the server and back it up
4
Check the sa password
5
Limit privileges of SQL services
6
Block ports at your firewall
7
Use NTFS
8
Remove setup files and sample databases
9
Audit connections
10
30
Next Steps

• Stay informed about security
• Sign up for security bulletins
• http//www.microsoft.com/security/security_bullet
  ins/alerts2.asp
• Get the latest Microsoft security guidance
• http//www.microsoft.com/security/guidance/
• Get additional security training
• Find online and in-person training seminars
• http//www.microsoft.com/seminar/events/security.
  mspx
• Find a local CTEC for hands-on training
• http//www.microsoft.com/learning/

31
For More Information

• Microsoft Security Site (all audiences)
• http//www.microsoft.com/security
• TechNet Security Site (IT professionals)
• http//www.microsoft.com/technet/security
• MSDN Security Site (developers)
• http//msdn.microsoft.com/security

32
Questions and Answers