You are previewing Microsoft SQL Server 2012 Security Cookbook.
O'Reilly logo
Microsoft SQL Server 2012 Security Cookbook

Book Description

Over 70 practical, focused recipes to bullet-proof your SQL Server database and protect it from hackers and security threats.

  • Practical, focused recipes for securing your SQL Server databse

  • Master the latest techniques for data and code encryption, user authentication and authorization, protection against brute force attacks, denial-of-service attacks, and SQL Injection, and more

  • A learn-by-example recipe-based approach that focuses on key concepts to provide the foundation to solve real world problems

In Detail

In 2011, a big corporation suffered a 23-day network outage after a breach of security that allowed the theft of millions of registered accounts on its gaming network. A month later, hackers claimed in a press release to have stolen personal information of 1 million users by a single SQL injection attack. In these days of high-profile hacking, SQL Server 2012 database security has become of prime importance.

"Microsoft SQL Server 2012 Security Cookbook" will show you how to secure your database using cutting-edge methods and protect it from hackers and other security threats. You will learn the latest techniques for data and code encryption, user authentication and authorization, protection against brute force attacks, denial -of-service attacks, and SQL Injection, securing business intelligence, and more.

We will start with securing SQL Server right from the point where you install it. You will learn to secure your server and network with recipes such as managing service SIDs, configuring a firewall for SQL Server access, and encrypting the session by SSL. We will then address internal security : creating logins to connect to SQL Server, and users to gain access to a database. We will also see how to grant privileges to securable objects on the server or inside the database.

After having managed authentication through logins and users, we will assign privileges inside a database using permissions. We will then learn about symmetric keys, asymmetric keys and certificates, which can be used to encrypt data or sign data and modules with a choice of cipher algorithms, as well as creating hash representations of data.

Then we will cover methods to protect your database against brute force attacks, denial-of-service attacks, and SQL Injection. Finally we will learn about auditing and compliance and securing SQL Server Analysis Services (SSAS) and Reporting Services (SSRS).

Table of Contents

  1. Microsoft SQL Server 2012 Security Cookbook
    1. Table of Contents
    2. Microsoft SQL Server 2012 Security Cookbook
    3. Credits
    4. About the Author
    5. About the Reviewers
    6. www.PacktPub.com
      1. Support files, eBooks, discount offers and more
      2. Why Subscribe?
      3. Free Access for Packt account holders
      4. Instant Updates on New Packt Books
    7. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
        1. Downloading the example code
        2. Errata
        3. Piracy
        4. Questions
    8. 1. Securing Your Server and Network
      1. Introduction
      2. Choosing an account for running SQL Server
        1. How to do it...
        2. How it works...
        3. There's more...
          1. How to give the Log on as a service right to an account
          2. How to do it in Windows Server Core
          3. Creating a domain account to use as a service account
        4. See also
      3. Managing service SIDs
        1. How to do it...
        2. How it works...
      4. Using a managed service account
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Removing a managed account
        5. See also
      5. Using a virtual service account
        1. How to do it...
        2. How it works...
      6. Encrypting the session with SSL
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      7. Configuring a firewall for SQL Server access
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Find specific ports used by SQL Server
          2. Do it by script
      8. Disabling SQL Server Browser
        1. How to do it...
        2. How it works...
        3. There's more...
      9. Stopping unused services
        1. How to do it...
        2. How it works...
      10. Using Kerberos for authentication
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      11. Using extended protection to prevent authentication relay attacks
        1. How to do it...
        2. How it works...
        3. See also
      12. Using transparent database encryption
        1. How to do it...
        2. How it works...
        3. There's more...
      13. Securing linked server access
        1. How to do it...
        2. How it works...
        3. There's more...
      14. Configuring endpoint security
        1. How to do it...
        2. How it works...
        3. There's more...
      15. Limiting functionalities – xp_cmdshell and OPENROWSET
        1. How to do it...
        2. How it works...
        3. There's more...
          1. You cannot prevent a sysadmin member from using xp_cmdshell
    9. 2. User Authentication, Authorization, and Security
      1. Introduction
      2. Choosing between Windows and SQL authentication
        1. How to do it...
        2. How it works...
      3. Creating logins
        1. How to do it...
        2. How it works...
        3. There's more...
        4. Checking the state of a login
          1. Disabling a login
          2. Changing a SQL login password
          3. Copying SQL logins between instances
        5. See also
      4. Protecting your server against brute-force attacks
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Audit sa connection and sa failed connection attempts
        4. See also
      5. Limiting administrative permissions of the SA account
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
          1. What to do when you have no administrator account
      6. Using fixed server roles
        1. How to do it...
        2. How it works...
      7. Giving granular server privileges
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Allowing logins to run a SQL trace
        4. See also
      8. Creating and using user-defined server roles
        1. How to do it...
        2. How it works...
        3. There's more...
      9. Creating database users and mapping them to logins
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Who is dbo?
          2. What is a guest user?
          3. Using system functions to identify users and logins
      10. Preventing logins and users to see metadata
        1. How to do it...
        2. How it works...
      11. Creating a contained database
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. How to convert a database to contained
      12. Correcting user to login mapping errors on restored databases
        1. How to do it...
        2. How it works...
        3. There's more...
    10. 3. Protecting the Data
      1. Introduction
      2. Understanding permissions
        1. How to do it...
        2. How it works...
        3. There's more...
          1. How does WITH GRANT OPTION work?
          2. What is the REFERENCE permission?
        4. See also
      3. Assigning column-level permissions
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
      4. Creating and using database roles
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Msdb roles
        4. See also
      5. Creating and using application roles
        1. How to do it...
        2. How it works...
        3. There's more...
      6. Using schemas for security
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Getting rid of useless pre-existing schemas
          2. How does name resolution work?
      7. Managing object ownership
        1. How to do it...
        2. How it works...
        3. There's more...
      8. Protecting data through views and stored procedures
        1. How to do it...
        2. How it works...
        3. There's more...
      9. Configuring cross-database security
        1. How to do it...
        2. How it works...
        3. There's more...
      10. Managing execution-plan visibility
        1. How to do it...
        2. How it works...
        3. There's more...
      11. Using EXECUTE AS to change the user context
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Using EXECUTE AS CALLER
    11. 4. Code and Data Encryption
      1. Introduction
      2. Using service and database master keys
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Regenerating the Service Master Key
      3. Creating and using symmetric encryption keys
        1. How to do it...
        2. How it works...
        3. There's more...
          1. What is the scope of a symmetric key?
      4. Creating and using asymmetric keys
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      5. Creating and using certificates
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Separating encryption from decryption
          2. Using an Extensible Key Management provider
      6. Encrypting data with symmetric keys
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Writing less decryption code
      7. Encrypting data with asymmetric keys and certificates
        1. How to do it...
        2. How it works...
      8. Creating and storing hash values
        1. How to do it...
        2. How it works...
        3. There's more...
      9. Signing your data
        1. How to do it...
        2. How it works...
      10. Authenticating stored procedure by signature
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Removing the private key
      11. Using module signatures to replace cross-database ownership chaining
        1. How to do it...
        2. How it works...
      12. Encrypting SQL code objects
        1. How to do it...
        2. How it works...
    12. 5. Fighting Attacks and Injection
      1. Introduction
      2. Defining Code Access Security for .NET modules
        1. Getting ready
        2. How to do it...
        3. How it works...
      3. Protecting SQL Server against Denial of Service
        1. How to do it...
        2. How it works...
      4. Protecting SQL Server against SQL injection
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
      5. Securing dynamic SQL from injections
        1. How to do it...
        2. How it works...
        3. There's more…
      6. Using a SQL firewall or Web Application Firewall
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Web Application Firewalls
    13. 6. Securing Tools and High Availability
      1. Introduction
      2. Choosing the right account for SQL Agent
        1. How to do it...
        2. How it works...
      3. Allowing users to create and run their own SQL Agent jobs
        1. How to do it...
        2. How it works...
      4. Creating SQL Agent proxies
        1. How to do it...
        2. How it works...
      5. Setting up transport security for Service Broker
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Troubleshooting errors
          2. Using the TRANSPORT option for routing
      6. Setting up dialog security for Service Broker
        1. Getting ready
        2. How to do it...
        3. How it works...
      7. Securing replication
        1. How to do it...
      8. Securing SQL Server Database Mirroring and AlwaysOn
        1. Getting ready
        2. How to do it...
        3. How it works...
    14. 7. Auditing
      1. Introduction
      2. Using the profiler to audit SQL Server access
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Deprecated events
      3. Using DML trigger for auditing data modification
        1. Getting ready
        2. How to do it...
        3. How it works...
      4. Using DDL triggers for auditing structure modification
        1. How to do it...
        2. How it works...
        3. There's more...
      5. Configuring SQL Server auditing
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
      6. Auditing and tracing user-configurable events
        1. How to do it...
        2. How it works...
      7. Configuring and using Common Criteria Compliance
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      8. Using System Center Advisor to analyze your instances
        1. How to do it...
        2. How it works...
      9. Using the SQL Server Best Practice Analyzer
        1. How to do it...
        2. How it works...
      10. Using Policy Based Management
        1. How to do it...
        2. How it works...
        3. There's more...
    15. 8. Securing Business Intelligence
      1. Introduction
      2. Configuring Analysis Services access
        1. How to do it...
        2. How it works...
        3. There's more...
      3. Managing Analysis Services HTTP client authentication
        1. How to do it...
        2. How it works...
        3. There's more...
      4. Securing Analysis Services access to SQL Server
        1. How to do it...
        2. How it works...
      5. Using Role-Based Security in Analysis Services
        1. How to do it...
        2. How it works...
        3. There's more...
      6. Securing Reporting Services Server
        1. How to do it...
        2. How it works...
        3. There's more...
      7. Managing permissions in Reporting Services with roles
        1. How to do it...
        2. How it works...
        3. There's more...
      8. Defining access to data sources in reporting services
        1. How to do it...
        2. How it works...
        3. There's more...
      9. Managing Integration Services password encryption
        1. How to do it...
        2. How it works...
        3. There's more...
    16. Index