1. No category

SQL Server™ Security
Girish Chander, SQL Server Security Program Manager
James Hamilton, SQL Server Architect
Agenda

Changing threat environment




Securing SQL Server™





The growing software security issue
Database Security: shifting ground
Evolving database threat environment
Installation
Configuration
Monitoring security of installization

Customer tools

Recommended periodic scans
SQL Server in the enterprise
Best practices for applications over SQL
Growing Problem: S/W Security


Survivability: the capability of a system to fulfill its
mission, in a timely manner, in the presence of
attacks, failures and accidents. — Lipson, Howard and Fisher, 1999
Survivability challenge



Previous focus primarily on S/W failure, human error and
natural disaster
Primary security measure was physical

Keep external bad guys away

Protection against insiders primarily via legal
protection and data isolation
Industry shifts



Shift from mediated access to direct application access

Vendors, customers and partners
Shift from central administration to distributed
administration
Shift from survivability focus largely ignoring security to
security as the prime concern
Incidents Reported


CERT/CC incident statistics 1988 through 2002
Incident: single security issue grouping together all
impacts of that that issue


e.g., LoveLetter worm defined to be a single “incident”
Issue: disruption, DOS, loss of data, misuse, damage, loss
of confidentiality
80,000
70,000
60,000
50,000
40,000
30,000
20,000
10,000
Source: http://www.cert.org/stats/cert_stats.html
'0
2
'0
0
'9
8
'9
6
'9
4
'9
2
'9
0
'8
8
0
Database Security: Shifting Ground


Most applications of value have persistent data
Data valuable to company, organization or even individual
typically also has value to others


Even ephemeral data has significant value, when trends
analyzed and understood



Decreased storage and data management costs enable
ephemeral data
Competitive pressure demands ephemeral data
Where there is value, there are bad guys


Information is becoming the most valuable asset in many
industries; e.g., Charles Schwab and Wal-Mart both identify
management of information assets as key competitive
advantage
And professional services guys, and press guys, and industry
analysts …
Battleground evolving to include the database

“Port 1433 [SQL Server] regularly registered as one of the top
scan ports in the Internet Storm Center” — Source:
http://www.sans.org/top20/
Evolving Database Threat Environment

A decade ago





Now increasingly databases are externally accessible




Suppliers are directly connected
Customers are directly connected
Customers and partners are directly sharing data
Data is most valuable resource in application stack



Databases were physically secure
They were housed in central data centers — not distributed
External access was mediated through customer service
representatives, purchasing managers, etc.
Security issues were rarely reported
Value increases with greater integration and aggregation
Opportunities exist for data theft, modification or destruction
Database security is a growing problem

101 database alerts since January 2001 (Source:
http://www.securitytracker.com/)

Two database issues on SANS/FBI top 20 list (Source:
http://www.sans.org/top20/)
Agenda

Changing threat environment




Securing SQL Server





The growing software security issue
Database security: shifting ground
Evolving database threat environment
Installation
Configuration
Monitoring security of installation

Customer tools

Recommended periodic scans
SQL Server in the enterprise
Best practices for applications over SQL
Secure Installation

Physical security


Never place database unprotected on public net





This allows securing the files appropriately
Do not install on a domain controller
Choose weak service account



Or on unprotected private net
Firewall protected
S/W mediating database access
Install on NTFS file system


Protect all related systems, media, backups, etc
Do not choose LocalSystem, box admin or domain admin
Cracked database won’t get access to rest of enterprise
Latest code is most secure code

Apply latest service packs and security patches
Configuration Options

Authentication mode



Login auditing



Use Integrated Security

More secure protocols (Kerberos and NTLM)

Kerberos allows for delegation

Allows for password policy enforcements

Typically does not require application to store
passwords
If using Mixed mode (Standard SQL Authentication)

Use SSL to encrypt network traffic

Use strong passwords

Never use blank passwords
Audit failed login attempts at the very least
Disallow ad hoc queries
Choose static ports for named instances

Avoid opening UDP1434 at firewall
Secure Operation

Understand the security model




Only configure and run needed features



Don’t put all enterprise/box administrators in one group
Changing service accounts



Do not change default permissions
If you must change, never set proxy account to
administrator
Smallest possible administrator groups


Replication, Agent, SQL MAIL, etc.
Xp_cmdshell usage


Security White Paper for SQL 2000
Security White Paper for SQL 7.0
Security section of SQL Server 2000 Operations Guide
Use Enterprise Manager
KB article Q283811
Disallow direct catalog updates
Secure Operation (cont.)

Media security including backups



Turn on appropriate level of auditing



Assume damage possible and have aggressive backup policy
Test disaster recovery system
Track critical user actions at a minimum

Examples: sysadmin actions, server role membership
changes, password changes, login-related activity
Keep overhead minimum
Encryption options


Protect sensitive data over the wire

Use SSL, IPSEC, VPN, etc.
File-level encryption

Prevents illicit copying of database files

SQL supports Encrypted File System

Third-party support: http://www.netlib.com/
Monitoring SQL Health

Microsoft Baseline Security Analyzer



Graphical and command-line tool
Performs local and remote scans
Scans for missing weaknesses in






Enables customers to verify the security of the
current configuration of their systems
Built in association with Shavlik Systems
Example SQL Server checks


Windows®
IIS
SQL Server
Blank SA passwords, file and registry
permissions, number of sysadmins, exposure of
xp_cmdshell to nonsysadmins
Version 1.1 will support multiple instances
Monitoring SQL Health (cont.)




Scan for/remove accounts with NULL passwords
Remove old unused logins
Scan for objects with permissions granted to
public
Verify login-user mapping



Enumerate membership in privileged roles




Interesting in attach/detach scenarios
Sp_change_users_login with report option
Ensure membership is given to trusted individuals only
Ensure startup procedures are safe and trusted
Verify file and registry key permissions
Ensure passwords not present in install files

Run Killpwd utility
Agenda

Changing threat environment




Securing SQL Server





The growing software security issue
Database security: shifting ground
Evolving database threat environment
Installation
Configuration
Monitoring security of installation

Customer tools

Recommended periodic scans
SQL Server in the enterprise
Best practices for applications over SQL
Multitier Scenarios

Three possible options




Flowing original caller to database
Single Windows context to database
Single connection to database using SQL
authentication
Consider IIS, to ASP.NET talking to SQL
IIS
ASP.NET
SQL
Flowing Caller Context

All machines need to be on same or trusted
domains



Impersonation must be enabled in ASP.NET


The service needs to be trusted for delegation
Advantages



Active directory required
Kerberos and delegation need to be enabled
All security enforced in SQL Server
Full auditability of all user actions
Disadvantages


Not always feasible in extranet/Internet scenarios
Connection pooling is limited

Original callers cannot share connections
Midtier to Database Connection
(Integrated Security)


Run ASP.NET as low-privileged account
End users authenticate at application level


Connection to database in context of ASP.NET account




Database trusts application to authenticate users
Recommend low-privileged domain account
Alternatively, local Windows account on SQL Server box
with same username and password

Useful if connection made across nontrusted domain
Account has only necessary runtime permissions in SQL

Is not a high-privileged account; not a sysadmin
Advantages




No storage of credentials needed
No need to pass credentials over the wire to SQL
Running as low-privileged account, minimizes potential
damage from compromise
Connection pooling possible as single account is used
Midtier to Database Connection
(SQL Security)

End users authenticate at application level



Connection to database using standard SQL login
Use low-privileged login account




Encrypted using service’s credentials
Only same service account can decrypt
Disadvantages



Use strong passwords
Leverage SSL to protect authentication over the wire
Secure midtier credentials data protection APIs


Database trusts application to authenticate users
Credentials storage required
Standard SQL authentication weaker than Windows
authentication
Advantages


Works across firewalls and nontrusted domains
Connection pooling possible
Agenda

Changing threat environment




Securing SQL Server





The growing software security issue
Database security: shifting ground
Evolving database threat environment
Installation
Configuration
Monitoring security of installation

Customer tools

Recommended periodic scans
SQL Server in the enterprise
Best practices for applications over SQL
Application Best Practices

Use weak access accounts



Use Windows auth rather than SQL Auth





Easier to secure
No password storage required
If using SQL auth, use SSL
Turn on encryption for sensitive data
Use roles for permissions and ownership




Only capable of actions needed to run application
Use different account for administration
Ease of management
Objects owned by roles, need not be dropped/renamed when
user dropped
Do not grant permissions to public
Don’t show “developer quality” error messages to users

Can reveal information to attackers in multiphase attacks
Using Ownership Chaining

Hide underlying schema through views/SPs
 Leverage ownership chaining to manage perms

Ownership Chaining: calling and called object have same owner
 Permissions check skipped on called object
Example
 Create table user1.t1 (c1 int not null)
 Create proc user2.proc1 as select * from user1.t1 return
 If user3 has execute permissions on proc1, still need select
permissions on user1.t1

Execute Perms
checked for User3
Select Perms checked
for User3
User2.Proc1
User1.T1
User1.Proc1
User1.T1
Execute Perms
checked for User3
NO Perms checked
for User3
User3
Preventing SQL Injection

Attacker allowed to send SQL queries to backed
datastore
APPLICATION CODE
var shipcity;
ShipCity = Request.form (“Shipcity”)
var sql = “SELECT * FROM OrdersTable
WHERE ShipCity = ‘” + Shipcity + “’”;
GOOD USER
Inputs Redmond in the form
Query to back-end is:
SELECT * FROM OrdersTable WHERE ShipCity = ‘Redmond’
MALICIOUS USER
Inputs the following in the form:
Redmond’ DROP TABLE OrderTable –
Query to the back-end is:
SELECT * FROM OrdersTable WHERE ShipCity = ‘Redmond’
DROP TABLE OrdersTable—’
SQL Injection

Why SQL injection works?



Connection made in context of higherprivileged account
Application accepts arbitrary user input
Mitigating SQL injection



Validate all user input
 Define set of valid input, accept only that
 Reject all invalid input
Avoid using dynamic SQL in stored procs
Run applications in minimally privileged
contexts
 Never run as sysadmin
Tips for App Dev Teams

Understanding various security issues



Construct threat analysis for each S/W component




Different threat vectors, attack scenarios
Awareness of issues such as SQL injection, cross-site
scripting, buffer-overflow attacks
Enumerate component boundaries
Analyze component data flow, interfaces and interactions

Can it be compromised?

What data flows in and out?
Compromise could be through different kinds of threats

Escalation of privileges, tampering of data, spoofing,
information disclosure, code injection
Code Review

Develop Code review checklists

Guideline for common security issues

Directed code reviews — based on threat analysis
Generic file reviews — top-down approach

SP3 Security Changes


Nonblank SA passwords required on upgrade
Sp_change_users_login



Changing database ownership



Only sysadmins can
Restriction to prevent cross-database escalation of
privilege
Cross-database Ownership Chaining



Password required for autofix option
No creation of logins with NULL passwords
Off by default; option to turn on at instance level
Per database knob as well
Marking system objects

Only sysadmin can mark objects as system objects
© 2002 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.