Secure Data SQL Server Best Practices Monica DeZulueta, Ph.D. Data Platform Technology Specialist Microsoft Corporation Session Objectives And Agenda Session Objectives Describe security best practices and how they help protect your valuable data
Focus on operational/administrative tasks Address them in roughly lifetime sequence What I hope you takeaway from this session 3 things you can do today to better secure your installation How to best leverage new SQL Server security capabilities Surface Area Reduction What Minimize Enabled/Exposed features Off by Default for new SQL2K5 and SQL2K8 installs
Features, services, connections On Upgrade, remain in pre-upgrade state Recommendation New installs leave features off Enable only what you will actually use Keep connectivity to a minimum Upgrades turn off whatever you dont need Surface Area Reduction Why Reduced attack surface
Heterogeneous installation footprint How Surface Area Configuration tool (SQLSAC) Launch from Setup or Start Menu SAC command line utility sac out server1.out -S server1 -U admin -I MSSQLSERVER sac in server1.out -S server2 sp_configure EXEC sp_configure SMO and DMO XPs, 0
Service Accounts What Services can run under built-in accounts (Local System, Network Service, or Local Service) or user account Each service can use a different account Recommendation Most desirable: Local or Domain user account Can change password without shutdown Least desirable: Local System Workable: Network Service/Local Service Use different accounts for different services
Service Accounts Why Least Privilege Isolation Defense in depth How Specified during Setup Change using SQL Configuration Manager tool Do not change service account from Windows
Authentication Mode What Windows Authentication (default) Windows principals only Mixed Authentication (optional) Windows and SQL principals Recommendation Use Windows Authentication whenever possible Use Mixed Authentication to get Legacy application support Cross platform client/server
Improved administrator separation Encrypt communications channel Uses self-signed cert during login by default Authentication Mode Why Single sign on Simplified administration No password management Protect conversations and credentials in transit Use real cert to prevents MITM attacks
How Selected during Setup Updated via Management Studio Network Connectivity What Protocols and endpoints enabled Demands on strength of channel protection Recommendation Enable minimal protocols (e.g. TCP/IP) Change and block default ports (1433, 1434)
Grant user access through restrictive endpoints Do not expose to internet Network Connectivity Why Minimize potential client population Block known attacks Restrict access paths How SQL Surface Area Configuration tool SQL Configuration Manager tool
Endpoint DDL CREATE ENDPOINT myEndpoint AS ... GRANT CONNECT ON ENDPOINT::myEndpoint TO Fred Lockdown of System Procedures What Removal of system XPs REVOKE EXECUTE permission on SPs and XPs from PUBLIC Recommendation Leave system XPs in place
Reconsider need to revoke EXECUTE permission Lockdown of System Procedures Why Many are Off By Default (including xp_cmdshell) Others made safe (e.g. xp_dirtree, xp_regread) Procedures contain appropriate authorization check Permission held, role membership Removal of XPs results in unsupported configuration How
N/A Password Policy What Complexity, Expiration, Lockout enforcement Common across Windows and SQL Win2K3 onwards (hard-coded rules for older versions) SQL Logins, App Roles, pass phrases, etc. Everywhere passwords are used Recommendation Leave CHECK_POLICY on
Set CHECK_EXPIRATION on to avoid old passwords Set MUST_CHANGE for new logins Password Policy Why Deter brute-force and dictionary attacks Prevent blank passwords Blank/trivial SA password is game over! How CREATE LOGIN Barney WITH PASSWORD = '3KHJ6dhx(0xVYsdf'
MUST_CHANGE ALTER LOGIN Barney WITH CHECK_EXPIRATION = ON Administrator Privileges What Principals with highly elevated privileges SA built-in login Members of SYSADMIN built-in server role Holders of CONTROL permission at server level Recommendation Use admin privileges only when needed
Minimize number of administrators Provision admin principals explicitly Have multiple distinct admins if more than one needed Avoid dependency on builtin\administrators Windows group Administrator Privileges Why Least privilege Repudiation/accountability Windows Vista User Account Control How
EXEC sp_addsrvrolemember 'Corporate\BamBam', 'sysadmin EXEC sp_dropsrvrolemember Fred', 'sysadmin GRANT CONTROL SERVER TO Barney Database Ownership & Trust What Each database is owned by DBO user (default = database creator) DB_OWNER role members Can confer trust on other databases Recommendation
Have distinct owners for databases Not all owned by SA Minimize owners for each database Confer trust selectively Leave CDOC setting off Migrate usage to selective trust instead Database Ownership & Trust Why Least privilege Repudiation/accountability
Isolation How ALTER AUTHORIZATION ON DATABASE::myDB to Barney EXEC sp_addrolemember db_owner', Wilma ALTER DATABASE myDB SET TRUSTWORTHY ON Schemas What Namespace in the container hierarchy Server>database->schema->object Can be owned by any user (SQL2K5)
Permissions grantable at schema level Recommendation Group related objects together into same schema Leverage ownership and permissions at schema level Have distinct owners for schemas Not all owned by DBO Minimize owners for each schema Schemas Why Isolation, aggregation
Flexibility Separate administrative grouping from application access Change owner without updating applications How CREATE SCHEMA mySchema AUTHORIZATION Betty CREATE TABLE mySchema.myTable (C1 int, C2 varchar(20)) GRANT SELECT ON SCHEMA::mySchema TO Dino Authorization What Who can access what
Recommendation Encapsulate access within modules Manage permissions via database roles Leverage permission granularity Many new permissions in SQL 2005 Do not enable Guest access Use Login-less users instead of Application Roles Authorization Why Least Privilege
Administrative ease Avoid password management How CREATE PROCEDURE mySchema.mySP WITH EXECUTE AS Wilma AS ... CREATE ROLE myDBRole AUTHORIZATION db_securityadmin GRANT EXECUTE ON OBJECT::mySchema.mySP TO myDBRole EXEC sp_addrolemember myDBRole, Betty Catalog Security What
Metadata visible only for objects permission is held on Some objects visible to public (e.g. filegroups) VIEW DEFINITION permission grantable Provides metadata visibility only no access Recommendation Grant VIEW DEFINITION selectively Legacy applications Delegating administration Catalog Security
Why Information disclosure System fingerprinting/profiling How Secure by default no action required Grant VIEW DEFINITION permission at object/schema/database/server level GRANT VIEW DEFINITION ON OBJECT::mySchema.myTable TO Dino
Encryption What Cryptographic protection of data against disclosure Applicable at column and cell level Algorithm choices depends on operating system Recommendation Encryption is very scenario specific Encrypt high value/sensitive data Symmetric key for data, asymmetric key to protect symmetric key Password protect keys and remove master key encryption
for most secure configuration Encryption Why Protection of data at rest (lost laptop, backups) Advanced/selective access control Need permission AND key to see data How CREATE MASTER KEY ENCRYPTION BY PASSWORD = Pa$$w0rD1
CREATE SYMMETRIC KEY mySymKey AUTHORIZATION Dino WITH ALGORITHM=TRIPLE_DES ENCRYPTION BY PASSWORD=MemorableYetObscurePassPhrase' Auditing What Record of security relevant activity Profile system and track potential security violations Recommendation Auditing is very scenario specific
Password policy in place -> audit failed logins (default) Sensitive database content -> audit security events Including successful logins Auditing Why Profile system and track potential security violations Forensic analysis of incidents How SQL Profiler Stored procedures
EXEC sp_configure c2 audit mode, 1 EXEC sp_trace_xxx ... Best Practice Analyzer What Scan installation for best practices usage Report on issues found Recommendation Regularly run MBSA 2.0 SQL 2000 only
Run SQL BPA against SQL 2005 incorporates security checks Best Practice Analyzer Why Maintain baseline of best practices usage Detect and correct deviations quickly How Microsoft Baseline Security Analyzer SQL Best Practices Analyzer
Patching What Keeping software up to date with security fixes SQL2000 SP4 onwards: Patching via Microsoft Update SQL2005 onwards: Separate code line for security fixes Recommendation Stay as current as possible! Enable automatic updates (where appropriate)
Patching Why Old attacks never go away (e.g. port 1434 probing) Proliferation of installations New issues can occur at any time How Enable automatic updates, or Run Microsoft Update explicitly Demos
SQL Server Certifications FIPS 140-2 Defines which algorithms can be used for encryption Algorithms certified for Windows 2003 In process for Vista/Windows Server 2008 Common Criteria International set of guidelines for security products In process of evaluating strategy and targets for Common Criteria
Summary Best Practices
Surface Area Reduction Enable only what you need Service Accounts Local/Domain user account Authentication Mode Windows authentication Network Connectivity Enable minimal endpoints Lockdown System Procs Secure by default Password Policy Enable Expiration/Must Change Admin Privileges Only where needed DB Ownership & Trust Distinct owners, no CDOC Schemas Group related objects
Summary Best Practices Authorization Use roles, granular perms
Catalog Security Grant access sparingly Encryption Use symmetric key Auditing Audit security events Best Practice Analyzer Run regularly Patching Stay current with Microsoft Update
Scarcity (pg. 16) Literacy Rate (pg. 3) Standard of Living (pg. 2) ... Movement and Migration. When a group of people or animals moves from one area to another . Journal pg. 23. ... Push & Pull Factors Last modified...
Structure Map Top of Productive Alida Formation *structure increases from current Crescent Point drilling *2 meter contour Horizontal Type Log Offset Wells *notice proximity of productive horizontal oil wells to the Lifeview Manor land holdings and the 4-3 vertical well...
Student satisfaction up (in particular positivity about assessment feedback) and employability up. C. Students Feedback 'My advocacy skills have improved, my confidence has improved a lot with public speaking as well as my analytical and research skills' ...
Island Arcs Two ocean plates hit head-on. One ocean plate is forced to subduct under the other forming an ocean trench. The subducting plate melts. Magma rises to the surface forming a string of volcanic islands parallel to the trench....
Graduate Assistance used to supervise first year and second year Chemistry students in the lab. Now. Senior academic/experienced staff supervise the laboratory classes. facilitate guided development of important hands-on research laboratory skills.
Example: True Shape of a Planar Figure - Auxiliary View. for more DETAILS and ORDER. Additional Task to the multimedia book „DarstellendeGeometrie/ 3D-Geometry", published by Veritas. Educational Edition. ISBN - 978-3-7058-9079-4. Student Edition
Ready to download the document? Go ahead and hit continue!