Microsoft SQL Server 2012 Security Cookbook -- Table of Contents -- Microsoft SQL Server 2012 Security Cookbook -- Credits -- About the Author -- About the Reviewers -- www.PacktPub.com -- Support files, eBooks, discount offers and more -- Why Subscribe? -- Free Access for Packt account holders -- Instant Updates on New Packt Books -- Preface -- What this book covers -- What you need for this book -- Who this book is for -- Conventions -- Reader feedback -- Customer support -- Downloading the example code -- Errata -- Piracy -- Questions -- 1. Securing Your Server and Network -- Introduction -- Choosing an account for running SQL Server -- How to do it... -- How it works... -- There's more... -- How to give the Log on as a service right to an account -- How to do it in Windows Server Core -- Creating a domain account to use as a service account -- See also -- Managing service SIDs -- How to do it... -- How it works... -- Using a managed service account -- Getting ready -- How to do it... -- How it works... -- There's more... -- Removing a managed account -- See also -- Using a virtual service account -- How to do it... -- How it works... -- Encrypting the session with SSL -- Getting ready -- How to do it... -- How it works... -- There's more... -- Configuring a firewall for SQL Server access -- How to do it... -- How it works... -- There's more... -- Find specific ports used by SQL Server -- Do it by script -- Disabling SQL Server Browser -- How to do it... -- How it works... -- There's more... -- Stopping unused services -- How to do it... -- How it works... -- Using Kerberos for authentication -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Using extended protection to prevent authentication relay attacks -- How to do it... -- How it works... -- See also -- Using transparent database encryption.

How to do it... -- How it works... -- There's more... -- Securing linked server access -- How to do it... -- How it works... -- There's more... -- Configuring endpoint security -- How to do it... -- How it works... -- There's more... -- Limiting functionalities - xp_cmdshell and OPENROWSET -- How to do it... -- How it works... -- There's more... -- You cannot prevent a sysadmin member from using xp_cmdshell -- 2. User Authentication, Authorization, and Security -- Introduction -- Choosing between Windows and SQL authentication -- How to do it... -- How it works... -- Creating logins -- How to do it... -- How it works... -- There's more... -- Checking the state of a login -- Disabling a login -- Changing a SQL login password -- Copying SQL logins between instances -- See also -- Protecting your server against brute-force attacks -- How to do it... -- How it works... -- There's more... -- Audit sa connection and sa failed connection attempts -- See also -- Limiting administrative permissions of the SA account -- How to do it... -- How it works... -- There's more... -- See also -- What to do when you have no administrator account -- Using fixed server roles -- How to do it... -- How it works... -- Giving granular server privileges -- How to do it... -- How it works... -- There's more... -- Allowing logins to run a SQL trace -- See also -- Creating and using user-defined server roles -- How to do it... -- How it works... -- There's more... -- Creating database users and mapping them to logins -- How to do it... -- How it works... -- There's more... -- Who is dbo? -- What is a guest user? -- Using system functions to identify users and logins -- Preventing logins and users to see metadata -- How to do it... -- How it works... -- Creating a contained database -- Getting ready -- How to do it... -- How it works... -- There's more...

How to convert a database to contained -- Correcting user to login mapping errors on restored databases -- How to do it... -- How it works... -- There's more... -- 3. Protecting the Data -- Introduction -- Understanding permissions -- How to do it... -- How it works... -- There's more... -- How does WITH GRANT OPTION work? -- What is the REFERENCE permission? -- See also -- Assigning column-level permissions -- How to do it... -- How it works... -- There's more... -- See also -- Creating and using database roles -- How to do it... -- How it works... -- There's more... -- Msdb roles -- See also -- Creating and using application roles -- How to do it... -- How it works... -- There's more... -- Using schemas for security -- How to do it... -- How it works... -- There's more... -- Getting rid of useless pre-existing schemas -- How does name resolution work? -- Managing object ownership -- How to do it... -- How it works... -- There's more... -- Protecting data through views and stored procedures -- How to do it... -- How it works... -- There's more... -- Configuring cross-database security -- How to do it... -- How it works... -- There's more... -- Managing execution-plan visibility -- How to do it... -- How it works... -- There's more... -- Using EXECUTE AS to change the user context -- How to do it... -- How it works... -- There's more... -- Using EXECUTE AS CALLER -- 4. Code and Data Encryption -- Introduction -- Using service and database master keys -- How to do it... -- How it works... -- There's more... -- Regenerating the Service Master Key -- Creating and using symmetric encryption keys -- How to do it... -- How it works... -- There's more... -- What is the scope of a symmetric key? -- Creating and using asymmetric keys -- Getting ready -- How to do it... -- How it works... -- There's more... -- Creating and using certificates -- Getting ready.

How to do it... -- How it works... -- There's more... -- Separating encryption from decryption -- Using an Extensible Key Management provider -- Encrypting data with symmetric keys -- How to do it... -- How it works... -- There's more... -- Writing less decryption code -- Encrypting data with asymmetric keys and certificates -- How to do it... -- How it works... -- Creating and storing hash values -- How to do it... -- How it works... -- There's more... -- Signing your data -- How to do it... -- How it works... -- Authenticating stored procedure by signature -- How to do it... -- How it works... -- There's more... -- Removing the private key -- Using module signatures to replace cross-database ownership chaining -- How to do it... -- How it works... -- Encrypting SQL code objects -- How to do it... -- How it works... -- 5. Fighting Attacks and Injection -- Introduction -- Defining Code Access Security for .NET modules -- Getting ready -- How to do it... -- How it works... -- Protecting SQL Server against Denial of Service -- How to do it... -- How it works... -- Protecting SQL Server against SQL injection -- How to do it... -- How it works... -- There's more... -- See also -- Securing dynamic SQL from injections -- How to do it... -- How it works... -- There's more… -- Using a SQL firewall or Web Application Firewall -- Getting ready -- How to do it... -- How it works... -- There's more... -- Web Application Firewalls -- 6. Securing Tools and High Availability -- Introduction -- Choosing the right account for SQL Agent -- How to do it... -- How it works... -- Allowing users to create and run their own SQL Agent jobs -- How to do it... -- How it works... -- Creating SQL Agent proxies -- How to do it... -- How it works... -- Setting up transport security for Service Broker -- How to do it... -- How it works... -- There's more...

Troubleshooting errors -- Using the TRANSPORT option for routing -- Setting up dialog security for Service Broker -- Getting ready -- How to do it... -- How it works... -- Securing replication -- How to do it... -- Securing SQL Server Database Mirroring and AlwaysOn -- Getting ready -- How to do it... -- How it works... -- 7. Auditing -- Introduction -- Using the profiler to audit SQL Server access -- How to do it... -- How it works... -- There's more... -- Deprecated events -- Using DML trigger for auditing data modification -- Getting ready -- How to do it... -- How it works... -- Using DDL triggers for auditing structure modification -- How to do it... -- How it works... -- There's more... -- Configuring SQL Server auditing -- How to do it... -- How it works... -- There's more... -- See also -- Auditing and tracing user-configurable events -- How to do it... -- How it works... -- Configuring and using Common Criteria Compliance -- Getting ready -- How to do it... -- How it works... -- There's more... -- Using System Center Advisor to analyze your instances -- How to do it... -- How it works... -- Using the SQL Server Best Practice Analyzer -- How to do it... -- How it works... -- Using Policy Based Management -- How to do it... -- How it works... -- There's more... -- 8. Securing Business Intelligence -- Introduction -- Configuring Analysis Services access -- How to do it... -- How it works... -- There's more... -- Managing Analysis Services HTTP client authentication -- How to do it... -- How it works... -- There's more... -- Securing Analysis Services access to SQL Server -- How to do it... -- How it works... -- Using Role-Based Security in Analysis Services -- How to do it... -- How it works... -- There's more... -- Securing Reporting Services Server -- How to do it... -- How it works... -- There's more...

Managing permissions in Reporting Services with roles.