DBA 327 SQL Server Security Best Practices Gert

DBA 327 SQL Server™ Security: Best Practices Gert E. R. Drapers Software Architect Customer Advisory Team SQL Server Development

Agenda The World has Changed… SQL Server Security Threats Securing SQL Server Multi-tier Security Scenarios Best Practices

Increasing Threat… A decade ago, databases were: Physically secure Housed in central data centers – not distributed External access mediated through clerks, purchasing managers, etc. Security issues rarely reported Now increasingly DB’s externally accessible: Suppliers directly connected Customers & partners directly sharing data Data is most valuable resource in application stack Value increases with greater integration & aggregation Opportunities for data theft, modification, or destruction DB security a growing problem: 101 DB alerts since January 2001 -- www. securitytracker. com Two database issues on SANS/FBI top 20 list http: //www. sans. org/top 20/

Incidents Reported CERT / CC incident statistics 1988 through 2002 Incident: single security issue grouping together all impacts of that issue Issues: disruption, DOS, loss of data, misuse, damage, loss of confidentiality Source: http: //www. cert. org/stats/cert_stats. html

Agenda The World has Changed… SQL Server Security Threats Securing SQL Server Multi-tier Security Scenarios Best Practices

Common Security Issues Ownership Chains Dynamic T-SQL inside stored procedures Changing Object Owner Should everything be owned by dbo? xp_cmdshell SQL injection

Ownership Chaining If calling object and caller object have same owner; permissions check skipped on called object Can be used to hide underlying schema through views and/or stored procedures Leverage ownership chaining to manage permissions

Ownership Chaining… 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 user 2. proc 1, (s)he still need select permissions on user 1. t 1 Execute permissions checked for User 3 Select permissions checked for User 3 User 2. Proc 1 User 1. T 1 User 1. Proc 1 User 1. T 1 User 3 Execute permissions checked for User 3 NO permisssions checked for User 3

Dynamic SQL inside stored procedures runs in the security context of the executor of the proc, not in the owner of the stored proc This is a security feature to protect you! Example: Admin: create proc myproc @p 1 nvarchar(500) as exec (@p 1) return 0 Malicious user: exec myproc “sp_addlogin ‘joe’ exec sp_addsrvrolemember ‘joe’, ’sysadmin’ ” And you’re sysadmin…

Changing Object Owner If you drop a user you must first drop / reassign all their objects Use sp_changeobjectowner Don’t directly update the system tables Not supported! SQL Server development team is working on a better long-term fix for the next release

DBO Own Everything? No, the issue here is to avoid ownership chains No need at all to have everything owned by dbo But it does simplify name resolution Names resolved as your username, then dbo to find objects

xp_cmdshell Only sysadmins can execute by default Runs as the service account or as the proxy account Best practices: Don’t grant unnecessarily Don’t run the service as an administrator Never run on a domain controller and grant permissions to xp_cmdshell Use wrapper Stored Procedure

SQL Injection Class of attack where attacker can insert or manipulate queries created by the application which are send to backend Why does SQL injection work? Application accepts arbitrary user input Trusting the user to provide well-formed data Connections are made in context of higher privileged account

Why It’s Wrong (1 Of 2) Good Guy Name: 1001 SELECT Has. Shipped FROM detail WHERE ID=‘ 1001' Not so Good Guy Name: 1001' or 1=1 -SELECT Has. Shipped FROM detail WHERE ID=‘ 1001' or 1=1 -- '

Why It’s Wrong (2 Of 2) Really Bad Guy Name: 1001’ drop table orders -SELECT Has. Shipped FROM detail WHERE ID= ‘ 1001' drop table orders -- ' Downright Evil Guy Name: 1001’ xp_cmdshell(‘fdisk. exe’) -SELECT Has. Shipped FROM detail WHERE ID= 'b' xp_cmdshell(‘fdisk. exe’) -- '

SQL Injection – C# string Status = "No"; string sqlstring =""; try { Sql. Connection sql= new Sql. Connection( @"data source=localhost; " + "user id=sa; password=password; "); sql. Open(); sqlstring="SELECT Has. Shipped" + " FROM detail WHERE ID='" + Id + "'"; Sql. Command cmd = new Sql. Command(sqlstring, sql); if ((int)cmd. Execute. Scalar() != 0) Status = "Yes"; } catch (Sql. Exception se) { Status = sqlstring + " failednr"; foreach (Sql. Error e in se. Errors) { Status += e. Message + "nr"; } } catch (Exception e) { Status = e. To. String(); }

Pseudo Remedies Quoting the Input Escaping character input does not protect you from SQL injection attacks Use Stored Procedures When called as a literal it is still a risk Dynamic SQL can be exploited

Remedies Never connect as sysadmin Least privilege principle Building secure SQL statements Input validation: check of valid input; rest is bad. Don’t check for bad input, you will always miss a case Use stored procedure to hide application logic Use parameterized input, not string concatenation Multi layered input checking: application, stored procedure, database schema No direct access to base tables

Agenda The World has Changed… SQL Server Security Threats Securing SQL Server Multi-tier Security Scenarios Best Practices

Secure Installation Physical security Protect all related systems, media, backups, etc. Never place RDBMS unprotected on public net Nor on 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 Use low privilege service account Do not use Local. System, Local (machine) or Domain Administrator Prevents compromised database to be used to bootstrap access to rest of enterprise Latest code is most secure code Apply latest service packs and security patches!

Configuration Options Authentication Mode Use Integrated Security (Windows Authentication) More Secure Protocols (Kerberos and NTLM) Kerberos allows for delegation Allows for password policy enforcements Typically does not require app storing passwords If using Mixed mode (Standard SQL Authentication) Always use SSL to encrypt at least the login packets Use strong passwords Never use blank passwords! Login Auditing At a minimum audit failed login attempts! Disallow ad hoc queries Choose static ports for named instances Avoid opening UDP 1434 at firewall

Secure Operation Understand the security model, see: 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 required, never set proxy account to administrator Smallest possible admin groups Don’t place all enterprise/machine administrators in one group Changing service accounts Use Enterprise Manager KB article Q 283811 Disallow direct catalog updates

Secure Operation 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 Example: sysadmin actions, server role membership changes, password changes, login related activity, etc. 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 (EFS) Third party: www. netlib. com

Security Diagnostics Microsoft Baseline Security Analyzer (MBSA) Graphical & command line tool Performs local & remote scans Scans for missing weaknesses in: Windows, IIS and SQL server Enables customers to verify the security of the current configuration of their systems Built in association with Shavlik systems http: //www. microsoft. com/technet/security/tools/ Tools/mbsahome. asp Example SQL Server checks: Blank SA passwords, file and Registry permissions, number of sysadmins, exposure of xp_cmdshell to non sysadmins, etc. MBSA version 1. 1 supports multiple instances

Security Diagnostics… 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 to trusted individuals only Ensure start-up procedures are safe and trusted Verify file and registry key permissions Ensure passwords not present in install files Run killpwd utility

Agenda The World has Changed… SQL Server Security Threats Securing SQL Server Multi-tier Security Scenarios Best Practices

Multi-Tier Security Scenarios Three possible options Flowing original caller to database Single Windows context to database Single connection to database using standard SQL Server authentication Consider IIS, to ASP. NET talking to SQL IIS ASP. NET SQL

Flowing Caller Context All machines need to be on single 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 audit-ability of all user actions Disadvantages: Not always feasible in extranet/internet scenarios Connection pooling is limited Original callers cannot share connections

Single Context (Windows) Run ASP. NET as low privileged account End users authenticate at app level DB trusts app to authenticate users Connection to DB made 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 non-trusted domain Account has only necessary run time 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

Single SQL Context End users authenticate at app level DB trusts app to authenticate users Connection to DB using standard SQL login and password Use Low privileged login account Use Strong passwords Leverage SSL to protect authentication over the wire Secure Credentials in middle tier using data protection APIs Encrypted using service’s credentials Only same service account can decrypt Disadvantages: Credentials storage required Standard SQL auth weaker than windows auth Advantages: Works across firewalls and non trusted domains Connection pooling possible

Agenda The World has Changed… SQL Server Security Threats Securing SQL Server Multi-tier Security Scenarios Best Practices

SQL Server Best Practices Don’t run SQL Server service under Administrator or Local. System account Remove BUILTINAdministrators from sysadmins If you must use Mixed mode Use strong passwords Never use a blank password Leverage SSL Apply the latest security patches Turn on appropriate level of auditing and tracing Constantly monitor logs for security related alerts Do not overload sysadmin role

Application Best Practices Use low privileged accounts Only capable of actions needed to run application Use different account for administration Use Windows authentication rather than SQL Server Authentication Easier to secure, leverages Windows security policies No password storage required If using SQL authentication, 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 return “too detailed” error messages to users Can reveal information to attackers in multi phase attacks

SQL Injection Best Practices Do not trust the user’s input! Be strict about what represents valid input and reject everything else Regular expressions are your friend! Any client-side access and validity checks are performed on the server also Do not use string concatenation for creating SQL statements Use parameterized queries to build queries Do not divulge too much information/detail to the attacker (information disclosure) Connect to the database server using a low-privileged account No connections to SQL Server as sa or sysadmin role member

SP 3 Security Changes Requiring non blank SA passwords on upgrade Option to by-pass if really required Non blank strong SA password highly recommended sp_change_users_login Password required for autofix option No creation of logins with NULL pwds Changing database ownership Only sysadmins can Restriction to prevent cross DB escalation of privilege Cross DB Ownership Chaining Off by default; option to turn on at instance level Per database knob as well Marking system objects On sysadmin can mark objects as system objects

Resources Writing Secure Code v 2 MS Press www. nextgenss. com Threat Profiling Microsoft SQL Server Cracking SQL Server Passwords Advanced SQL Injection (more) Advanced SQL Injection http: //www. sqlsecurity. com SQL Injection FAQ SQLSecurity Checklist Lockdown Script Project Tools (SQLPing, etc. ) www. appsecinc. com Manipulating Microsoft SQL Server Using SQL Injection Introduction to Database and Application Worms

Summary You are always at risk and vulnerable Do not trust the user’s input Principle of least privilege “Closed by default” Actively & regularly look for irregularities Stay up-to-update & informed!

Community Resources http: //www. microsoft. com/communities/default. mspx Most Valuable Professional (MVP) http: //www. mvp. support. microsoft. com/ Newsgroups Converse online with Microsoft Newsgroups, including Worldwide http: //www. microsoft. com/communities/newsgroups/default. mspx User Groups Meet and learn with your peers http: //www. microsoft. com/communities/usergroups/default. mspx

evaluations

Questions… gertd@microsoft. com