SQL Server

Views:
 
Category: Entertainment
     
 

Presentation Description

No description available.

Comments

Presentation Transcript

SQL Server™ Security:

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

Agenda:

Agenda Changing threat environment The growing software security issue Database Security: shifting ground Evolving database threat environment Securing SQL Server ™ 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:

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:

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

Database Security: Shifting Ground:

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 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 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 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:

Evolving Database Threat Environment A decade ago 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 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 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:

Agenda Changing threat environment The growing software security issue Database security: shifting ground Evolving database threat environment Securing SQL Server Installation Configuration Monitoring security of installation Customer tools Recommended periodic scans SQL Server in the enterprise Best practices for applications over SQL

Secure Installation:

Secure Installation Physical security Protect all related systems, media, backups, etc Never place database unprotected on public net Or on unprotected private net Firewall protected S/W mediating database access Install on NTFS file system This allows securing the files appropriately Do not install on a domain controller Choose weak service account 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:

Configuration Options Authentication mode 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 Login auditing 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:

Secure Operation Understand the security model Security White Paper for SQL 2000 Security White Paper for SQL 7.0 Security section of SQL Server 2000 Operations Guide Only configure and run needed features Replication, Agent, SQL MAIL, etc. Xp_cmdshell usage Do not change default permissions If you must change, never set proxy account to administrator Smallest possible administrator groups Don’t put all enterprise/box administrators in one group Changing service accounts Use Enterprise Manager KB article Q283811 Disallow direct catalog updates

Secure Operation (cont.):

Secure Operation (cont.) Media security including backups Assume damage possible and have aggressive backup policy Test disaster recovery system Turn on appropriate level of auditing 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:

Monitoring SQL Health Microsoft Baseline Security Analyzer Graphical and command-line tool Performs local and remote scans Scans for missing weaknesses in Windows ® IIS SQL Server Enables customers to verify the security of the current configuration of their systems Built in association with Shavlik Systems Example SQL Server checks 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.):

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 Interesting in attach/detach scenarios Sp_change_users_login with report option Enumerate membership in privileged roles 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:

Agenda Changing threat environment The growing software security issue Database security: shifting ground Evolving database threat environment Securing SQL Server Installation Configuration Monitoring security of installation Customer tools Recommended periodic scans SQL Server in the enterprise Best practices for applications over SQL

Multitier Scenarios:

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:

Flowing Caller Context All machines need to be on same or trusted domains Active directory required Kerberos and delegation need to be enabled Impersonation must be enabled in ASP.NET The service needs to be trusted for delegation Advantages 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):

Midtier to Database Connection (Integrated Security) Run ASP.NET as low-privileged account End users authenticate at application level Database trusts application to authenticate users Connection to database in context of ASP.NET account 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):

Midtier to Database Connection (SQL Security) End users authenticate at application level Database trusts application to authenticate users Connection to database using standard SQL login Use low-privileged login account Use strong passwords Leverage SSL to protect authentication over the wire Secure midtier credentials data protection APIs Encrypted using service’s credentials Only same service account can decrypt Disadvantages Credentials storage required Standard SQL authentication weaker than Windows authentication Advantages Works across firewalls and nontrusted domains Connection pooling possible

Agenda:

Agenda Changing threat environment The growing software security issue Database security: shifting ground Evolving database threat environment Securing SQL Server 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:

Application Best Practices Use weak access accounts Only capable of actions needed to run application Use different account for administration 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 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:

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 User3 Select Perms checked for User3 Execute Perms checked for User3 User2.Proc1 User1.Proc1 Execute Perms checked for User3 NO Perms checked for User3 User1.T1 User1.T1

Preventing SQL Injection:

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:

SQL Injection Why SQL injection works? Connection made in context of higher-privileged 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:

Tips for App Dev Teams Understanding various security issues Different threat vectors, attack scenarios Awareness of issues such as SQL injection, cross-site scripting, buffer-overflow attacks Construct threat analysis for each S/W component 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:

SP3 Security Changes Nonblank SA passwords required on upgrade Sp_change_users_login Password required for autofix option No creation of logins with NULL passwords Changing database ownership Only sysadmins can Restriction to prevent cross-database escalation of privilege Cross-database Ownership Chaining 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

PowerPoint Presentation:

© 2002 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

authorStream Live Help