SQL Server™ Security Girish Chander, SQL Server Security Program

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 0 Source: http://www.cert.org/stats/cert stats.html

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.