![Page 1: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/1.jpg)
SQLCAT: Addressing Security and Compliance Issues
Il-Sung Lee, Denny Lee, Ayad Shammout
![Page 2: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/2.jpg)
SQL Server Customer Advisory Team (SQLCAT)• Works on the largest, most complex SQL Server projects worldwide
• MySpace - 4.4 million concurrent users at peak time, 8 billion friend relationships, 34
billion e-mails, 1 PetaByte store, scale-out using SSB and SOA
http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000004532
• Bwin – Most popular European online gaming site – 30000 database transactions /
second, motto: “Failure is not an option”; 100 TB total storage http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000004138
http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000001470
• Korea Telecom - Largest telco in Korea serves 26 million customers; 3 TB Data
Warehousehttp://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000001993
• Drives product requirements back into SQL Server from our customers and ISVs
• Shares deep technical content with SQL Server community
• SQLCAT.com
• http://blogs.msdn.com/mssqlisv
![Page 3: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/3.jpg)
SQL Server Design Win Program
• Target the most challenging and innovative SQL Server
applications
• 10+ TB DW, 3k/tran/s OLTP, Large 500GB+ Cubes, Competitive
migrations, Complex deployments, Server Consolidation (1000+)
• Invest in large scale, referenceable SQL Server projects
across the world
• Provide SQLCAT technical & project experience
• Conduct architecture and design reviews covering performance,
operation, scalability and availability
• Offer use of HW lab in Redmond with direct access to SQL Server
development team
• Work with Marketing Team Developing PR
![Page 4: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/4.jpg)
SQLCAT and SQL CSS Invite You…
ROOM 611
• To the SQL Server Clinic where the most experienced SQL Server experts in the world will be waiting to talk with you.• Bring your toughest Questions / Challenges to the experts who have seen it all• Architect and Design your future applications with experts who have done it before with some of the largest, most complex systems in the world• Or just stop in to say hello!
![Page 5: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/5.jpg)
Agenda
• Introduction to Compliance
• Payment Credit Industry Compliance
Showcase
• Health Information Portability and
Accountability Act Compliance Showcase
• Application of SQL Server to fulfill HIPAA compliance scenarios
• Enacted at CareGroup Healthcare
![Page 6: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/6.jpg)
Introduction to ComplianceAddressing Security and Compliance Issues
![Page 7: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/7.jpg)
Importance of Compliance
• Widely cited within the academic
community, 87% of the US
population is uniquely identifiable
by the three attributes of zip
code, birth date, and gender
(Sweeney, 2002)
• Sweeney was able to identify the
medical records of Gov William
Weld (MA) by joining masked
medical data and a voter’s list.
NameAddress
DatesParty
Voted Date
EthnicityVisit DateDiagnosisProcedureMedicationTotal Charge
Zip
DOB
Gender
*based on Sweeney L, k-Anonymity: A model for protecting privacy, International Journal on Uncertainty, Fuzziness and Knowledge-based Systems, 10(5), 2002, 557-570
![Page 8: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/8.jpg)
What is Compliance? (GRC)
![Page 9: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/9.jpg)
GRC Example
Loss from theft,
vandalism and
injury to personnel
Review entrance
and guard logs,
tapes and news
reports
Locked door, guard,
camera, badges and
policies
![Page 10: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/10.jpg)
Compliance Requirements
IT Control SO
X
PC
I
HIP
AA
GL
BA
ID Management
Separation of Duties
Encryption
Key Management
Auditing
Control Testing
Policy Management
http://www.microsoft.com/sql/compliance
![Page 11: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/11.jpg)
Payment Credit Industry (PCI) Guidance
Addressing Security and Compliance Issues
![Page 12: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/12.jpg)
Disclaimers
• I am not a QSA (Qualified Security Assessor)
• But I will provide guidance and best practice on
PCI DSS Compliance.
• No feature
deep dive
• More detailed
info available
in this
whitepaper
![Page 13: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/13.jpg)
Overview of the PCI DSS
• Visa, Mastercard, AmEx, Discover, and JCB created the PCI
Security Standards Council in Dec. 2004 and released the PCI Data
Security Standard v1
• Created “to help facilitate the broad adoption of consistent data
security measures on a global basis” for enhancing payment account
data security
• Applies to any business that stores, processes, or transmits Primary
Account Number (PAN)
• Requires annual compliance audit
• Noncompliance leads to levy of significant fines.
• Latest version is 1.2.1,
https://www.pcisecuritystandards.org/security_standards/pci_dss.sht
ml
![Page 14: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/14.jpg)
PCI Objectives and Requirements
Build and Maintain a Secure Network
• Requirement 1: Install and maintain a firewall configuration to protect cardholder data
• Requirement 2: Do not use vendor-supplied defaults for system passwords and other security parameters
Protect Cardholder Data
• Requirement 3: Protect stored cardholder data
• Requirement 4: Encrypt transmission of cardholder data across open, public networks
Maintain a Vulnerability Management Program
• Requirement 5: Use and regularly update anti-virus software
• Requirement 6: Develop and maintain secure systems and applications
Implement Strong Access Control Measures
• Requirement 7: Restrict access to cardholder data by business need-to-know
• Requirement 8: Assign a unique ID to each person with computer access
• Requirement 9: Restrict physical access to cardholder data
Regularly Monitor and Test Networks
• Requirement 10: Track and monitor all access to network resources and cardholder data
• Requirement 11: Regularly test security systems and processes
Maintain an Information Security Policy
• Requirement 12: Maintain a policy that addresses information security
![Page 15: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/15.jpg)
SQL Server 2008 Compliance Toolbox
Audit
TDE
EKMSigned
ModulePBM
CDC
![Page 16: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/16.jpg)
Req 2: Do not use vendor-supplied defaults for system pwds and other security params
• No default passwords in SQL Server
• Features/services Off-by-Default
• E.g., protocols, CLR, dbmail, XPcmdshell
• BUILTIN/Administrators are not sysadmin
• sa account is not enabled in Windows
Auth mode
![Page 17: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/17.jpg)
Req 3: Protect stored cardholder data
• Enable Transparent Data Encryption on
databases containing credit card data
• Periodic key rotation – at least once a year
• EKM for split-key ownership
• HSM administrator different from db_owner and sysadmin
• Key management without EKM permissible
• No single user with access to both dbbackup and certificate backup files
![Page 18: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/18.jpg)
Req 4: Encrypt transmission of cardholder
data across open, public networks • Full support for TLS/SSL
• Can be set server-wide or on a per connection basis.
• Enable for all connections transmitting cardholder data
SSL
LOGIN
Userid
Password
...
![Page 19: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/19.jpg)
Req 7: Restrict access to cardholder data by
business need-to-know• Limit inclusion in sysadmin
• Windows authentication
• BUILTIN/Administrators are not sysadmin
• Using principals of least privilege
• Role-based access
• Instance and database permissions
• Signed modules
• Disable sa login
![Page 20: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/20.jpg)
Req 8: Assign a unique ID to each person
with computer access• SQL Server uses Windows SIDs for Windows
users and groups
• SQL Logins use GUID for generating SID
• Enable Windows password policy support
• Set to change password on next logon
• Enforce 90 day password expiration
• Do not use a single login for application
connections (or any shared accounts in
general); applies to sa – disable!
![Page 21: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/21.jpg)
Req 10: Track and monitor all access to network resources and cardholder data
• SQL Server Audit to monitor data access
• Granular auditing of tables
• Audit trail must be retained for 1 year
• Log should be protected from SQL users/DBA
• Configure Audit to shutdown on failure
• Change Data Capture to record committed
changes to data
• Policy-based Management to monitor server
settings and detect changes
![Page 22: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/22.jpg)
Audit Settings
• At a minimum, Audit:
• Login success and failures
• Changes to server configurations, encryption keys,
logins, server level permissions, databases
• CREATE/DELETE/ALTER of schema objects
• SELECT/INSERT/UPDATE/DELETE and ALTER of
tables containing cardholder data
• Changes to Audit configuration
• Enable the CDC against any table containing
cardholder data
![Page 23: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/23.jpg)
Achieving PCI Compliance
• With careful planning, proper organizational
procedures, and process controls, PCI
compliance with SQL Server 2008 attainable
• TDE, Audit, PBM and other 2008 features are
all useful tools in achieving compliance
• Take time to read the whitepaper
• http://www.parentebeard.com/lib/pdf/Deploying_SQL_Server_2008_Based_on_PCI_DSS.pdf
• Consult a PCI Qualified Security Assessor
![Page 24: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/24.jpg)
Health Information Portability and Accountability Act (HIPPA) Case Study
Addressing Security and Compliance Issues
![Page 25: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/25.jpg)
Business Drivers
• Avoiding disruptions in patient care
• Improving patient and staff access to medical records
and other vital information
• Complying with HIPAA regulations
• Maintaining privacy of medical information
• Its Expensive To Be Careless!
o Direct Costs – Customers lost, Revenue, Legal, Audit Fees
o Indirect Losses – Reputation
![Page 26: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/26.jpg)
The Challenges
Protecting the privacy and integrity of
patient medical records
Monitoring database access and capturing
access information for compliance and
audit purposes
Who accessed which databases, when and
how?
![Page 27: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/27.jpg)
What we can and can’t do
We can ….
Enforce account/password policy
Define strong policies and procedures
We can’t …..
Enforce audit log in every vendor application
Determine who is doing what and when
![Page 28: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/28.jpg)
How we are providing for data integrity and security
Database classifications (AAA vs. AA)
Have policies and procedures for data access authorization
Online HIPAA and Security employee training
Automatically terminate data access when employees leave
Create scripts to collect data and user access details
Implement our Centralized Audit Solution on SQL Server 2008
![Page 29: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/29.jpg)
Implementing IT Control with SQL Server 2008
• Securing the Platform
• Controlling Identity and Separation of Duties
• Encrypting Database Data
• Auditing Sensitive Information
• Using Policy-Based Management to Define,
Deploy and Validate Policy
![Page 30: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/30.jpg)
Securing the Platform
• Limit number of users have access to SQL Server
• Minimize surface area of attack by limiting running services,
installing only the software needed, disable unnecessary
ports and configuring the firewall
• Install latest OS/SQL service packs and security patches
![Page 31: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/31.jpg)
Controlling Identity and Separation of Duties
• Limit who can access the database and grant the least
privileges.
• Use Windows Authentication
• Use Policy-Based Management to validate security policies
![Page 32: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/32.jpg)
Encrypting Database Data
• Transparent Data Encryption (TDE)
• Protecting sensitive data
![Page 33: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/33.jpg)
Auditing Sensitive Information
![Page 34: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/34.jpg)
SSIS
SSIS
SSIS
Ce
ntr
al S
QL
Au
dit
Re
po
sito
ry
SQL 2
00
8 E.E.
Ap
plicatio
ns
Au
dit Lo
gs.
![Page 35: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/35.jpg)
Auditing…in actionHIPAA Case Study
![Page 36: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/36.jpg)
Using Policy-Based Management to Define, Deploy and Validate Policy
SQLAudit Central Server
Server 1
Server 2
Server nPolicy Extract
Extract Policy Data
Obtain Server List
Load Policy Data
View
Reports
Extract Logs to
fileshare
![Page 37: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/37.jpg)
Enterprise Policy Management Framework
![Page 38: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/38.jpg)
Results
• Spend less time on regulatory compliance
• Automate compliance and IT security controls
• Proactively measure and remediate deficiencies to sustain the control environment
• Embrace best practices and build policies and processes
• Reduce the impact of a breach by providing Analysis and Alerts of malicious or suspicious activity.
• Complete audit log
![Page 39: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/39.jpg)
Complete the Evaluation Form & Win!
• You could win a Dell Mini Netbook – every day – just for
handing in your completed form! Each session form is
another chance to win!
Pick up your Evaluation Form:
• Within each presentation room
• At the PASS Booth near registration area
Drop off your completed Form:
• Near the exit of each presentation room
• At the PASS Booth near registration area
Sponsored by Dell
![Page 40: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/40.jpg)
Thank youfor attending this session and the 2009 PASS Summit in Seattle
![Page 41: SQLCAT: Addressing Security and Compliance Issues with SQL Server 2008](https://reader031.vdocuments.us/reader031/viewer/2022022415/58f13a4c1a28ab767f8b45ed/html5/thumbnails/41.jpg)
Visit the
Microsoft Technical Learning Center
Located in the Expo Hall
Microsoft Ask the Experts Lounge
Microsoft Chalk Talk Theater Presentations
Microsoft Partner Village