SQL Server - Netwrix

[Pages:20]Novem ber 2015

SQL Ser ver Secu r it y Pr im er

Tips f r om IT Pr os

Virt ualisat ion and Disast er Recovery Handling User Term inat ion

Secu r it y of SQL Ser ver :

3 Basic Ru les Building vs Buying

Qu ick Ref er en ce Gu ide:

SQL Server Audit ing

Usef u l How -t o:

Det ect ing SQL Dat abase Changes

Cont ent s

3 SQL Ser ver Secur it y Pr im er by Russell Smith

5 SQL Change M anagem ent : When t o Build, and When t o Buy? by Sarah Greesonbach

7 2 Oct ober M edical Dat a Breaches That Could Have Been Pr even t ed by Sarah Greesonbach

9 How Should IT Handle User Ter m inat ion

Novem ber 2015 SysAdm in Magazine

12

Vir t u alizat ion As a Par t of Disast er Recover y Pl a n

by Richard Muniz

16 How t o Det ect SQL Dat abase Changes

18 Quick Reference Guide: SQL Server Auditing

Novem ber 2015 SysAdm in Magazine

SQL Ser ver

Security Primer

by Russell Smith

specializing in the management and security of Microsoft-based IT systems, Russell is the author of a book on Windows security

and a contributing author and blogger

Not a day goes past it seems without a major security breach hitting the headlines, the most recent being British phone and broadband provider TalkTalk, where up to 4 million customers may have had their credit card details stolen. And what?s worse is that this is the third cyberattack the company has fallen prey to in the past 12 months, and has admitted that not all customer data is encrypted on its servers. Attacks against small companies have also increased sharply in the past two years, so it?s no longer safe to assume that just because you?re a small fish in a big sea, that nobody is interested in your data. Microsoft SQL Server provides the backend for many line-of-business applications and websites, so keeping it secure should be a priority, and while in the article that follows I?m just scratching the surface, I?ll discuss some important security best practices for SQL Server.

3

Novem ber 2015 SysAdm in Magazine

Au t h en t icat ion an d Ser vice Accou n t s

Unless you have legacy applications that require SQL Server Authentication, Windows Authentication is more secure. If SQL Server Authentication is required, the service account should be properly secured. The host server operating system must also be protected, including carefully controlling administrative access and ensuring it remains patched.

Depending on whether SQL Server will be installed on a standalone, member server, or cluster, you should consider the type of account used to authenticate SQL services, such as the Database Engine and SQL Server Agent. Microsoft provides a comprehensive guide to the account types that should be used for SQL services here, and accounts should always be configured using the principle of least privilege, i.e. granted the minimum level of permissions. To take advantage of virtual or managed service accounts, you?ll need to upgrade to Windows Server 2008 R2 or later.

Dat a En cr ypt ion

SQL Server 2008 introduced Transparent Data Encryption (TDE) for encrypting data at rest, i.e. entire databases and their associated log files on disk, although it?s worth noting this became an Enterprise Edition only feature in SQL Server 2012. Cell-level encryption is available in all editions of SQL starting with SQL Server 2005, but may require changes to applications and is more resource intensive than TDE. BitLocker, the full-volume encryption feature in Windows Server 2008, can be used in conjunction with TDE as a good defense-in-depth solution for encrypting data at rest, and also provides boot integrity protection if the server has a

4

Trusted Platform Module (TPM). But unlike BitLocker, TDE continues to provide encryption when databases are backed up to different volumes, and protects individual databases, rather than giving users access to entire volumes. Improvements in SQL Server 2014 enable organizations to encrypt databases using TDE while still achieving a respectable compression ratio for backups, which wasn?t usually the case in SQL Server 2008. Other improvements in SQL Server 2014 include the ability to better separate administration duties with 3 new server-level permissions, and enhanced encryption key management. However, if you need to meet PCI-DSS, HIPAA, or other common regulatory compliance standards, a hardware security module (HSM) should be used to store encryption keys separately from the encrypted data, and perform encryption operations so that private keys don?t need to be transferred to the server ?s RAM. And don?t forget to encrypt network traffic between SQL and application servers by enabling IPsec Policies in Windows Firewall, or using SSL if a client application communicates directly with SQL Server.

Redu ce t h e At t ack Su r f ace

If you don?t need SQL Server Reporting Services, then don?t install it, or any other component that?s surplus to requirements. The Surface Area Configuration tool was removed in SQL Server 2008, but you can still enable or disable Database Engine features using Surface Area Configuration in the View Facets dialog, which is accessible by right clicking a database in the SQL Server Management Studio console. Once configured, you can also export the settings and use Policy-Based Management to configure multiple SQL servers.

Novem ber 2015 SysAdm in Magazine

SQL Change Management:

When to Build, and When

to Buy?

by Sarah Greesonbach

Freelance tech-blogger

It?s no secret that change management is a priority for Microsoft SQL Server users. Databases and database servers are some of the most critical objects in an enterprise IT infrastructure. These assets store critical and confidential data that can disrupt operations and jeopardize jobs within the company if lost, stolen, or compromised. But when it comes time to secure the right change management tool for your organization, sometimes it?s hard to tell if it?s more efficient and cost effective to build your own tool or purchase a third-party tool. Here are three questions you should ask yourself before you decide to outsource your SQL change auditor or develop it in-house.

5

Novem ber 2015 SysAdm in Magazine

It May Be Possible, But Is It Worthwhile?

Can You Deliver the Features You Need?

The range of technologies you can employ with Microsoft SQL Server is quite wide. The .NET framework, PowerShell, and other programming and scripting languages have bindings for the SQL Server. However, it?s important to remember that the effectiveness of in-house development isn?t about what?s possible, it?s about what can be done in the given time with the given resources. Unless your company specializes in database server change auditing software, the time and resources necessary to do it right are going to be scarce.

Do You Have the Time to Develop a Product?

Even if the in-house solution is good, its development is certain to face problems. First, in-house software often has many authors, which makes it harder to support. You?ll also need to track uncentralized, organic changes to the solution without being able to make this a priority for your team members. Second, new software should never be released without extensive testing. If your team doesn?t have the time and expertise to dig into your solution and make sure it?s ready to be used for such a high priority, it doesn?t have the time to develop the solution at all.

It is a simple task for your team to develop tools that allow you to subscribe to events and SQL Server trace logs, handle text-based logs, and query for events. But can you achieve the additional support and heightened security of a third-party change management tool that is built for monitoring several types of compliance? When your priorities may change over time, a tool designed by a team dedicated to SQL change management is more efficient and flexible. While the answer to these questions will be different for different companies, the final result of change management is the same all around: a world class tool, an intentional strategy, and a well-trained team are the key to auditing and change management success. Consider your situation to choose a way to best assemble this combination.

6

Novem ber 2015 SysAdm in Magazine

2 October M edical

Dat a Br each es That

Could Have Been

Pr even t ed

by Sarah Greesonbach

Freelance tech-blogger

The IT industry fights to reduce and eliminate its overall risk for security breaches year in and year out. That?s why it?s so disheartening to read about recent breaches in the news. It always comes down to one concerning question: Could that have been me?

The most productive thing we can do is learn from the mistakes of others in the hopes that it will prevent a breach in our organization. Here are two recent minor threats and how they could have been avoided:

7

Novem ber 2015 SysAdm in Magazine

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download