Securing SQL servers : protecting your database from attackers

* ''Denny Cherry is what would happen if Bill Gates and AC/DC got together to create a sibling. He’s a bare-knuckles, no holds-barred technologist, and you can bet that if he tells you that something does or doesn’t work, he’s speaking from experience. Active in the community, his passion is sharing. You’ll enjoy this book.''--**Buck Woody, Senior Technology Specialist, Microsoft**''Securing SQL Server - Protecting Your Database from Attackers and SQL Injection Attacks and Defense are two new books out on SQL security. The first, Securing SQL Server - Protecting Your Database from Attackers, author Denny Cherry takes a high-level approach to the topic. The book explains how to secure and protect a SQL database from attack. The book details how to configure SQL against both internal and external-based attacks. This updated edition includes new chapters on analysis services, reporting services, and storage area network security. For anyone new to SQL security, Cherry does a great job of explaining what needs to be done in this valuable guide. In and SQL Injection Attacks and Defense, editor Justin Clarke enlists the help of a set of experts on how to deal with SQL injection attacks. Since SQL is so ubiquitous on corporate networks, with sites often running hundreds of SQL servers; SQL is prone to attacks. SQL injection is a technique often used to attack databases through a website and is often done by including portions of SQL statements in a web form entry field in an attempt to get the website to pass a newly formed rogue SQL command to the database. SQL injection is a code injection technique that exploits security vulnerability in a website's software. The vulnerability happens when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and unexpectedly executed. With that, the need to defend servers against such attacks is an imperative and SQL Injection Attacks and Defense should be required reading for anyone tasks with securing SQL servers.''--**RSA Conference** Securing SQL Server Acknowledgements Dedication Author Biography About the Technical Editor Introduction 1 Securing the Network Securing the Network Network Firewalls Web Server on the Public Internet Network Web Server on the Internal Side of the Network Web Server in the Demilitarized Zone Server Firewalls Windows Firewall Inbound Rules Windows Firewall Outbound Rules Special Requirements for Clustering Direct Internet Access Public IP Addresses versus Private IP Addresses Accessing SQL Server from home Physical Security Keep Your Hands Off My Box Open Network Ports Unlocked Workstations Automatically Locking Computers Social engineering Finding the Instances Testing the Network Security Summary References 2 Database Encryption Database Encryption Hashing versus Encryption Triple DES RC Algorithms AES Hashing SHA2 and SQL Server Encrypting Objects Encrypting data within tables Encrypting within Microsoft SQL Server Encrypting within the Application Tier Encrypting data at rest TDE and FILESTREAM Log Shipping, Database Mirroring, and Always On Key Protection Encrypting data on the wire SQL Server Over SSL SQL Server 7 and 2000 SQL Server 2005 and Up Certificate Strength Differences Managing SSL Certificates Hiding the Instance IP Sec Encrypting data with MPIO drivers PowerPath Encryption with RSA Requirements and Setup Encrypting data via HBAs Summary References 3 SQL Password Security SQL Server Password Security Extended Protection SPNs Strong Passwords Contained Database Logins in SQL Server 2012 Encrypting Client Connection Strings SQL Reporting Services Application Roles Using Windows Domain Policies to Enforce Password Length Windows Authentication Group Policies Windows Domain Requirements to Use Domain Policies to Manage SQL Authentication Logins Contained Databases Contained Databases and Auto Close db_owners Can Now Add New Users to the Instance Password Policies and Contained Users Summary References 4 Securing the Instance What to Install, and When? SQL Authentication and Windows Authentication Editing the master.mdf File Using a Debugger to Intercept Passwords Purchased Products Password Change Policies Auditing Failed Logins Renaming the SA Account Disabling the SA Account Securing Endpoints Stored Procedures as a Security Measure Access to Base Tables Isn’t Required Enabling Cross Database Chaining Minimum Permissions Possible Instant File Initialization Linked Servers NTLM Double Hop Problems Securing Linked Servers Using SQL Server Management Studio for Linked Server Security Configuration Using T-SQL for Linked Server Security Configuration Only Allowing Some Groups to Use a Linked Server Using Policies to Secure Your Instance SQL Azure Specific Settings Instances That Leave the Office Securing “Always On” Securing Contained Databases Contained Databases and Always On Summary 5 Additional Security for an Internet Facing SQL Server and Application SQL CLR Extended Stored Procedures Protecting Your Connection Strings Database Firewalls Clear Virtual Memory Pagefile User Access Control (UAC) Other Domain Policies to Adjust Summary 6 Analysis Services Logging into Analysis Services Granting Administrative Rights Granting Rights to an Analysis Services Database Securing Analysis Services Objects Data Sources Cubes Cell Data Dimensions Dimension Data Mining Structures Summary 7 Reporting Services Setting up SSRS Service Account Web Service URL Database Report Manager URL E-mail Settings Execution Account Encryption Keys Scale-Out Deployment Logging onto SQL Server Reporting Services for the first time Security within Reporting Services Item Roles System Roles Adding System Roles Adding Folder Roles Reporting Services Authentication Options Anonymous Authentication Forms Authentication Security Within Reporting Services Report Server Object Rights Changing Permissions on an Object Hiding Objects Summary 8 SQL Injection Attacks What is an SQL Injection Attack? Why are SQL Injection attacks so successful? How to Protect Yourself From an SQL Injection Attack NET Protection Against SQL Injection Protecting Dynamic SQL Within Stored Procedures from SQL Injection Attack Using “EXECUTE AS” to Protect Dynamic SQL Impersonating a Login Impersonating a User Removing Extended Stored Procedures Not Using Best Practice Code Logic can Hurt You What to Return to the End User Database Firewalls Test, Test, Test Cleaning Up the Database After an SQL Injection Attack Other Front-End Security Issues The Web Browser URL is Not the Place for Sensitive Data Using xEvents to Monitor For SQL Injection Summary Reference 9 Database Backup Security Overwriting Backups Deleting Old Backups Media Set and Backup Set Passwords Backup Encryption LiteSpeed for SQL Server Red Gate SQL HyperBac Red Gate SQL Backup Third-Party Tape Backup Solutions Transparent Data Encryption Securing the Certificates Compression and Encryption Encryption and Data Deduplication Offsite Backups Summary References 10 Storage Area Network Security Securing the Array Locking Down the Management Ports Authentication User Access to the Storage Array Locking Down the iSCSI Ports LUN Security Moving LUNs Deleting LUNs Snapshots and Clones Securing the Storage Switches Fiber Channel iSCSI Fiber Channel over Ethernet Management Ports Authentication Zone Mapping Summary 11 Auditing for Security Login Auditing SQL Server 2005 and Older SQL Server 2008 and Newer Using xEvents for Auditing Logins Capturing Login Information Event Loss Settings Viewing Login Audits Auditing sysadmin Domain Group Membership Data Modification Auditing Change Data Capture Configuration Querying Changed Data Using xEvents For Data Modification Auditing Using SQL Server Audit for Data Modification Data Querying Auditing Schema Change Auditing Using Extended Events for Schema Change Auditing Using Policy-Based Management to Ensure Policy Compliance C2 Auditing Common Criteria Compliance Summary References 12 Server Rights SQL Server Service Account Configuration One Account for All Services SQL Server 2012’s AlwaysOn One Account Per Sever One Account for Each Service Using Local Service Accounts for Running SQL Server Services Credentials SQL Server Agent Proxy Accounts OS Rights Needed by the SQL Server Service Windows System Rights SQL Server’s NTFS Permissions OS Rights Needed by the DBA Dual Accounts OS Rights Needed to install service packs OS Rights Needed to Access SSIS Remotely Console Apps must die Fixed-Server Roles User Defined Server Roles AlwaysOn Instance Wide Permissions Fixed Database Roles Fixed Database Roles in the msdb Database User Defined Database Roles Default Sysadmin Rights Vendor’s and the Sysadmin Fixed-Server Role Summary 13 Securing Data Granting Rights Denying rights REVOKEing rights Column Level Permissions Row Level Permissions Summary A External Audit Checklists PCI DSS PCI Checklist Sarbanes-Oxley Sarbanes-Oxley Checklist HIPPA HIPPA Checklist Summary Reference Index A B C D E F G H I J K L M N O P R S T U V W X Z Copyright

SQL server is the most widely used database platform in the world, and a large percentage of these databases are not properly secured, exposing sensitive customer and business data to attack.

In Securing SQL Server, 2e, readers learn about the potential attack vectors that can be used to break into SQL server databases as well as how to protect databases from these attacks. In this book written by Denny Cherry, a Microsoft SQL MVP and one of the biggest names in SQL server today, readers learn how to properly secure a SQL server database from internal and external threats using best practices as well as specific tricks the authors employ in their roles as database administrators for some of the largest SQL server deployments in the world.

"Denny Cherry is what would happen if Bill Gates and AC/DC got together to create a sibling. He's a bare-knuckles, no holds-barred technologist, and you can bet that if he tells you that something does or doesn't work, he's speaking from experience. Active in the community, his passion is sharing. You'll enjoy this book."--Buck Woody, Senior Technology Specialist, Microsoft

• Presents hands-on techniques for protecting your SQL Server database from intrusion and attack.
• Provides the most in-depth coverage of all aspects of SQL Server database security, including a wealth of new material on Microsoft SQL Server 2012 (Denali).
• Explains how to set up your database securely, how to determine when someone tries to break in, what the intruder has accessed or damaged, and how to respond and mitigate damage if an intrusion occurs.

Written by Denny Cherry, a Microsoft MVP for the SQL Server product, a Microsoft Certified Master for SQL Server 2008, and one of the biggest names in SQL Server today, Securing SQL Server, Second Edition explores the potential attack vectors someone can use to break into your SQL Server database as well as how to protect your database from these attacks. In this book, you will learn how to properly secure your database from both internal and external threats using best practices and specific tricks the author uses in his role as an independent consultant while working on some of the largest and most complex SQL Server installations in the world. This edition includes new chapters on Analysis Services, Reporting Services, and Storage Area Network Security. Presents hands-on techniques for protecting your SQL Server database from intrusion and attack. Provides the most in-depth coverage of all aspects of SQL Server database security, including a wealth of new material on Microsoft SQL Server 2012 (Denali). Explains how to set up your database securely, how to determine when someone tries to break in, what the intruder has accessed or damaged, and how to respond and mitigate damage if an intrusion occurs SQL server is the widely used database platform in the world, and a large percentage of these databases are not secured, exposing sensitive customer and business data to attack. This book helps readers learn about the potential attack vectors that can be used to break into SQL server databases and how to protect databases from these attacks.