Secure DataSQL Server Best Practices

Secure DataSQL Server Best Practices

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

Recently Viewed Presentations

  • Push & Pull Factors - Paulding County High School

    Push & Pull Factors - Paulding County High School

    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...
  • Solar Car Mechanical Design - Recognized Student Organization ...

    Solar Car Mechanical Design - Recognized Student Organization ...

    (1) A point on the ground 8 m in front of the solar car (2) A minimum of 17° above the horizon on level ground (3) A full 100° to either side of center (4) The driver will be required...
  • Food, Soil, and Pest Management

    Food, Soil, and Pest Management

    There are two spiritual dangers in not owning a farm. One is the danger of supposing that breakfast comes from the grocery, And the other that heat comes from the furnace.
  • PRTISP Process

    PRTISP Process

    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...
  • Higher Education Academy Senior Fellowship Application Alan ...

    Higher Education Academy Senior Fellowship Application Alan ...

    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' ...
  • Earth Science and M.E.A.P - bath.k12.ky.us

    Earth Science and M.E.A.P - bath.k12.ky.us

    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....
  • Implementation of RSD in Chemistry Undergraduate Courses

    Implementation of RSD in Chemistry Undergraduate Courses

    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.
  • Principle Lines - members.geometry.at

    Principle Lines - members.geometry.at

    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