Build Secure Database Applications with Microsoft SQL Server

Build Secure Database Applications with Microsoft SQL Server Bryan Smith brysmi@microsoft. com

In the last few months …

Lessons Learned 1. Systems are at risk 2. Databases are prime targets 3. Databases are highly vulnerable

Objectives 1. Explore security features & practices 2. Encourage dialog between responsible parties

80 SQL Server Oracle DB 2 My. SQL 60 40 2002 2003 2004 2005 2006 2007 2008 2009 Notes: Update as of 10/31/2010 inclusive. This chart counts NIST CVE – Reported Software Flaws. , utilizing the NIST NVD. SQL Server Query = Vendor Name: Microsoft Corporation; Product Names: sql_server, sql_server_desktop_engine, sql_server_express_edition, sql_server_reporting services, sql_srv_desktop_engine; Oracle Query = Vendor Name: Oracle; Product Name: ‘Any’, all CVEs where “Vulnerable software and versions” lists a database product; DB 2 Query = Vendor Name: IBM; Product Names: db 2, db 2_content_manager_toolkit, db 2_server, db 2_universal_database; My. SQL Query = Vendor Name: mysql, mysql-ocaml, mysql_auction, mysql_eventum, mysql_quick_admin, mysqldumper, mysqlnewsengine; Product Name: ‘Any’ 2010

Thousands $ 400 $ 350 DB Vault, $ 92 $ 300 Label Security, $ 46 $ 250 Advanced Security, $ 46 $ 200 $ 150 $ 100 $ 50 Base Product, $ 190 Base Product, $ 55 $- SQL Server Oracle Notes: Comparisons based on list price for 2 socket X 4 -core server running Oracle 11 g Enterprise Edition or SQL Server 2008 R 2 Enterprise Edition. Details extracted from the following references: - Oracle Processor Core Factor Table: http: //www. oracle. com/us/corporate/contracts/processor-core-factor-table-070634. pdf Oracle Database Licensing: http: //www. oracle. com/us/corporate/pricing/databaselicensing-070584. pdf Oracle Technology Commercial Price List: http: //www. oracle. com/us/corporate/pricing/technology-price-list-070617. pdf SQL Server How To Buy: http: //www. microsoft. com/sqlserver/en/us/get-sql-server/how-to-buy. aspx

• DOD

Recommended Actions 1. 2. 3. 4. 5. 6. 7. Harden the database server Regulate network connectivity Secure the authentication process Assign minimal permissions Encrypt the data Defend against common exploits Monitor and enforce policies

Recommended Actions 1. 2. 3. 4. 5. 6. 7. Harden the database server Regulate network connectivity Secure the authentication process Assign minimal permissions Encrypt the data Defend against SQL injection Monitor and enforce policies

Guidance on Standards • • • Common Criteria Certification FIPS 140 -2 HIPAA PCI DSS & PCI DSS 2. 0 Achieving Separation of Duties with SQL Server

1. Harden the Database Server • Adhere to general hardening guidance – Microsoft Security Compliance Manager – Hardening Guidance for SQL Server Database Servers • Keep SQL Server up-to-date • Leave unnecessary SQL Server features off

Keep SQL Server Up-to-Date • Get off of old SQL Server versions – Review Support Lifecycle here • Consider new security features – New SQL Server 2008 R 2 security features here • Apply security hotfixes aggressively – Subscribe to security notifications here – Consider automating patching with tools such as System Center • Prepare to upgrade & patch in a timely manner – Have a ready-to-execute test plan • Notify Microsoft of stubborn vendors

Leave unnecessary features off • • • Database & SQL Mail CLR Integration OLE Automation xp_cmdshell Ad Hoc Remote Queries • HTTP & Service Broker Endpoints • Additional Network Protocols • Remote (Dedicated) Admin Connections

2. Regulate Network Connectivity • Use an alternative port number • Hide the SQL Server instance • Use application-specific endpoints

Port Numbers & SQL Browser Client A TCP 5000 P TC Client B SQL Browser SQL Server? SQ LS r? e v er TCP 5000 0 50 0 SQL Server

Instance Hiding Client A … … Client B SQL Browser SQL Server? SQ LS r? e v er TCP 5000 SQL Server

Endpoints Client A Client B SQL Server TCP 3000 TCP 5000

3. Secure the Authentication Process • Employ Windows authentication – Consider using Extended Protection • If you must use SQL authentication: – – Don’t reuse logins Enforce password policies Employ a very strong password for SA account Change name or disable SA account

SQL Authentication -- ENFORCE PASSWORD POLICIES ON LOGIN ALTER LOGIN mylogin WITH CHECK_POLICY=ON, CHECK_EXPIRATION=ON; -- CHANGE NAME OF SA LOGIN ALTER LOGIN sa WITH NAME = superuser; -- DISABLE RENAMED SA LOGIN ALTER LOGIN superuser DISABLE;

Extended Protection Client SQL Server A Application B Client A

Extended Protection AD Client C SQL Server A Application B Client A Service Binding

Extended Protection AD Client C SQL Server A Application B Client A Channel Binding

4. Assign Minimal Permissions • Use roles appropriately • Use schemas to isolate objects w/ broad rights • Employ ownership chains, context switching & module signing

Ownership Chains User A GRANT EXECUTE ON [Stored Proc] TO [User A]; Stored Proc Owner B Table Owner B Table

Context Switching User A GRANT EXECUTE ON [Stored Proc] TO [User A]; Stored Proc Owner Execute As C B Table Owner C Table

Module Signing User A GRANT EXECUTE ON [Stored Proc] TO [User A]; Stored Proc Owner B Table Owner C Table

5. Encrypt Your Data • Protect files with TDE • Protect values with cryptographic functions – Cell-level encryption – Digital signing • Protect data in motion with IPSec or SSL • Integrate with Extensible Key Management (EKM) infrastructure

Transparent Data Encryption Client SQL Server A . mdf. ndf Application B . ldf. bak

Cell-Level Encryption Client SQL Server A Encrypt. By. Passphrase() Encrypt. By. Key() Encrypt. By. Cert() Encrypt. By. Assym. Key() Order Num Amount Customer Info AB 1234 $101. 01 0 x 12 D 5 A 323 F 234 AB 3 AB 1235 $500. 00 0 x 238 AFEB 23 B 35 AA 4 AB 1236 $99. 98 0 x 13 D 5 A 423 F 2 BC 4 AB AB 1237 $201. 56 0 x 28 AFE 335 ABC 1235

Digital Signing Client SQL Server A Sign. By. Asym. Key() Sign. By. Cert() Verify. Signed. By. Asym. Key() Verify. Signed. By. Cert() Order Num Customer Info Signature AB 1234 123 Main St. 0 x 32145 A 52 CDB 6 AB 1235 456 Broadway 0 x. F 3214 AB 2 C 4 B 9 AB 1236 789 1 st Ave. 0 x 12145 AB 2 CDB 3 AB 1237 642 Avenue A 0 x. A 2145 A 11 CDB 5

EKM Integration • Key management through external infrastructure • Separation of key generation & key application duties • Hardware-acceleration improves performance

IPSec & SSL Client A SQL Server

6. Defend Against Common Exploits • Avoid use of dynamic SQL – Consider using LINQ or Entity-Framework for flexibility • LINQ & Entity-Framework not immune to exploit • If you must use dynamic SQL: – Limit & validate inputs – Use absolute minimum permissions

6. Defend Against Common Exploits • Do not disclose unnecessary information – Anticipate & validate query results • Adhere to application development best practices – See ADO. NET Security Best Practices White Paper • Secure connection string information

Secure Connection Information • Use Windows authentication –. config files support identity impersonation • If SQL authentication used, – Encrypt connection strings within the <connection. Strings> element of. config files – Avoid Universal Data Link (UDL) files – Limit retrieval with Persist Security Info=False; • Avoid dynamic connection strings – Parameters accepted on last wins basis – Use Connection. String. Builder & Sql. Connection classes to limit exposure • Technology-specific info: — ADO. NET — Entity-Framework — LINQ to SQL

7. Monitor & Enforce Policies • Use policy-based management to enforce configuration • Use audit to monitor critical activity • Use System Center Advisor to monitor best practices

Policy-Based Management Policy SQL Server

Audit Client SQL Server Audit Specification Client B Database Audit Specification Audit

System Center Advisor

Recommended Actions 1. 2. 3. 4. 5. 6. 7. Harden the database server Regulate network connectivity Secure the authentication process Assign minimal permissions Encrypt the data Defend against common exploits Monitor and enforce policies

Resources

Resources • http: //blogs. msdn. com/b/data_otaku/

Securing the DW • Network Connectivity – Don’t hide instance or disable SQL Browser – Configure non-standard ports & pipes • Authentication – Use Windows-only Authentication • Minimal Permissions – Leverage schemas & database roles • Encryption – TDE & IPSec/SSL • Audit & Monitoring – Audit access to sensitive data