SQL Server Security Girish Chander SQL Server Security

SQL Server™ Security Girish Chander, SQL Server Security Program Manager James Hamilton, SQL Server Architect

Agenda u Changing threat environment Ø Ø Ø u Securing SQL Server™ Ø Ø Ø u u 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 u u 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 Ø Ø u 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 u u CERT/CC incident statistics 1988 through 2002 Incident: single security issue grouping together all impacts of that issue Ø u e. g. , Love. Letter worm defined to be a single “incident” Issue: disruption, DOS, loss of data, misuse, damage, loss of confidentiality Source: http: //www. cert. org/stats/cert_stats. html

Database Security: Shifting Ground u u Most applications of value have persistent data Data valuable to company, organization or even individual typically also has value to others Ø u Even ephemeral data has significant value, when trends analyzed and understood Ø Ø u Decreased storage and data management costs enable ephemeral data Competitive pressure demands ephemeral data Where there is value, there are bad guys Ø u 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/top 20/

Evolving Database Threat Environment u A decade ago Ø Ø u Now increasingly databases are externally accessible Ø Ø Ø u Suppliers are directly connected Customers and partners are directly sharing data Data is most valuable resource in application stack Ø Ø u 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: Ø Two database issues on SANS/FBI top 20 list (Source: http: //www. securitytracker. com/) http: //www. sans. org/top 20/)

Agenda u Changing threat environment Ø Ø Ø u Securing SQL Server Ø Ø Ø u u 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 u Physical security Ø u Never place database unprotected on public net Ø Ø Ø u u This allows securing the files appropriately Do not install on a domain controller Choose weak service account Ø Ø u Or on unprotected private net Firewall protected S/W mediating database access Install on NTFS file system Ø u Protect all related systems, media, backups, etc Do not choose Local. System, 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 u Authentication mode Ø Ø u Login auditing Ø u u 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 UDP 1434 at firewall

Secure Operation u Understand the security model Ø Ø Ø u Only configure and run needed features Ø u Ø Don’t put all enterprise/box administrators in one group Changing service accounts Ø Ø u Do not change default permissions If you must change, never set proxy account to administrator Smallest possible administrator groups Ø u Replication, Agent, SQL MAIL, etc. Xp_cmdshell usage Ø u 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 Q 283811 Disallow direct catalog updates

Secure Operation (cont. ) u Media security including backups Ø Ø u Turn on appropriate level of auditing Ø Ø u 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 u Microsoft Baseline Security Analyzer Ø Ø Ø Graphical and command-line tool Performs local and remote scans Scans for missing weaknesses in Ø Ø Ø u Enables customers to verify the security of the current configuration of their systems Built in association with Shavlik Systems Example SQL Server checks Ø u 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. ) u u Scan for/remove accounts with NULL passwords Remove old unused logins Scan for objects with permissions granted to public Verify login-user mapping Ø Ø u Enumerate membership in privileged roles Ø u u u 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 u Changing threat environment Ø Ø Ø u Securing SQL Server Ø Ø Ø u u 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 u Three possible options Ø Ø Ø u 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 u All machines need to be on same or trusted domains Ø Ø u Impersonation must be enabled in ASP. NET Ø u The service needs to be trusted for delegation Advantages Ø Ø u 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) u u Run ASP. NET as low-privileged account End users authenticate at application level Ø u Connection to database in context of ASP. NET account Ø Ø Ø u 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) u End users authenticate at application level Ø u u Connection to database using standard SQL login Use low-privileged login account Ø Ø u Ø Encrypted using service’s credentials Only same service account can decrypt Disadvantages Ø Ø u Use strong passwords Leverage SSL to protect authentication over the wire Secure midtier credentials data protection APIs Ø u 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 u Changing threat environment Ø Ø Ø u Securing SQL Server Ø Ø Ø u u 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 u Use weak access accounts Ø Ø u Use Windows auth rather than SQL Auth Ø Ø Ø u u 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 Ø Ø Ø u 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 u Hide underlying schema through views/SPs Ø Leverage ownership chaining to manage perms u Ownership Chaining: calling and called object have same owner Ø Permissions check skipped on called object Example Ø Create table user 1. t 1 (c 1 int not null) Ø Create proc user 2. proc 1 as select * from user 1. t 1 return Ø If user 3 has execute permissions on proc 1, still need select permissions on user 1. t 1 u Execute Perms checked for User 3 Select Perms checked for User 3 User 2. Proc 1 User 1. T 1 User 1. Proc 1 User 1. T 1 Execute Perms checked for User 3 NO Perms checked for User 3

Preventing SQL Injection u Attacker allowed to send SQL queries to backed datastore APPLICATION CODE var shipcity; Ship. City = Request. form (“Shipcity”) var sql = “SELECT * FROM Orders. Table WHERE Ship. City = ‘” + Shipcity + “’”; GOOD USER Inputs Redmond in the form Query to back-end is: SELECT * FROM Orders. Table WHERE Ship. City = ‘Redmond’ MALICIOUS USER Inputs the following in the form: Redmond’ DROP TABLE Order. Table – Query to the back-end is: SELECT * FROM Orders. Table WHERE Ship. City = ‘Redmond’ DROP TABLE Orders. Table—’

SQL Injection u Why SQL injection works? Ø Ø u 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 u Understanding various security issues Ø Ø u Construct threat analysis for each S/W component Ø Ø Ø u 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 Ø

SP 3 Security Changes u u Nonblank SA passwords required on upgrade Sp_change_users_login Ø Ø u Changing database ownership Ø Ø u Only sysadmins can Restriction to prevent cross-database escalation of privilege Cross-database Ownership Chaining Ø Ø u 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.