Chapter 5 Windows Functions in SQL Server 2012

Since SQL Server 2005 there has been an increasing use of windowing functionality (e.g. ranking) with each new version of SQL Server. The new Windowing functions in 2012 allow you to perform complex analytical functions in a much simpler manner.

The chapter starts with a look at Sliding Windows, a demo is provided of what is possible in SQL Server 2005 where calculations can be performed over rows in each partition, and this is compared with SQL Server 2012 where we can look at specific rows within partitions relative to the ‘current’ row i.e. perform calculations across a sliding window of data.

Various new analytical keywords are explained, with examples, including LAG/LEAD, FIRST_VALUE/LAST_VALUE, PERCENTILE_COUNT, PERCENTILE_DISC, PERCENT_RANK and CUST_DIST

If you want to know about the latest SQL changes in relation to Windowing functions, this chapter is certainly helpful. In the earlier section of the chapter, although source code is given, it might have been useful if outputs were also given and discussed.

Chapter 6 SQL Server Security 101

In theory security is simple, just ensure users/processes have appropriate permission to objects. The aim of the chapter is to provide a simple and concise description of how the permissions hierarchy works, and then proceed to implement the principle of least privilege.

The chapter first puts SQL security into context, which includes securing physical hardware and networks, Windows security, auditing within SQL Server, data encryption, and then finally granting SQL Server permissions. You can see SQL Server permissions are only a small part of a big picture.

The chapter continues with a brief overview of SQL Server security architecture. Definitions of securables, permissions, and principles are given. The security hierarchy is illustrated via an image from Books Online (http://technet.microsoft.com/en-us/library/ms191465.aspx), which describes the principals and securable available at different levels. More detail is provided on the principals hierarchy (windows local groups, AD groups, SQL Server login, Server-level role, database user, database role, and application role), and securables (server, database, schema, and object). The 3 permission keywords GRANT, DENY, and REVOKE are briefly described.

This is followed by a section on the Principle of Least Privilege, the aim being that a user or process has the bare permission necessary to perform the required task, and no more. A step-by-step walkthrough of each of the security principals is provided.

This chapter provides a useful introduction to the principles of SQL Server security, together with some good example usage. The principle of least privilege is one that should be followed closely. There’s a good tip on testing permissions by using EXECUTE AS to run as another user/process.

Chapter 7 What Changed? Auditing Solutions in SQL Server

When systems misbehave, it’s often because something has changed. The purpose of this chapter is to examine some of the common auditing solutions i.e. SQL Trace, SQL Audit, in-house auditing via Event Notification or Triggers. Options specifically excluded are: C2 auditing, Policy Based Management, Change Data Capture, and Change Tracking.

The chapter starts with a look at the basics of auditing, namely raising and recording events together with an understanding of event classes. SQL Trace has 21 event categories and 180 event classes. The Extended Events framework is the basis of the SQL Audit tool and has even more events.

The chapter examines SQL Trace, providing a step-by-step walkthrough of setting up a server-side trace for DDL auditing (i.e. what objects have changed). A useful cross reference to chapter 4 and its use of SQL Trace, compliments this chapter. There’s a brief look at the default trace, which automatically records several DDL changes.

SQL Audit is a SQL Server 2008 feature, which records when an object has been accessed or modified. The chapter shows a step-by-step example of creating a SQL Audit object (stores the information), a database audit specification (database level audit), and a server audit specification. The audit output can be viewed via the SQL Server Log Viewer, or the fn_get_audit_file function. The pros and cons of SQL audit are discussed, the primary disadvantage seems to be the limited filtering capabilities (completely absent in 2008).

The last section examines the creation of your own in-house auditing solutions, using event notifications or triggers. This has the advantage of overcoming the limitations of existing tools. Event notification involves the use of Server Broker – a step-by-step example is provided and discussed. A DDL and logon triggers solution is provided, again with a step-by-step example. The pros and cons of each method are discussed. The chapter ends with a brief look at some third party solutions, including Idera’s SQL Compliance Manager and IBM’s Guardium – these tend to be very scalable.

This chapter provided a useful overview of several different technologies with the common aim of auditing. Helpful code and walkthroughs are provided. It’s nice to see the Default Trace get a mention, this really can be a life-saving in troubled times. A tool mentioned in chapter 15 is Sean Elliot’s Full Server DDL Auditing solution – it should have been cross referenced here.

Chapter 8 SQL Injection: How it works and how to thwart it

The chapter opens with a chilling observation from Imperva’s September 2011 “Hacker Intelligence Initiative” report, stating that 83% of successful data breaches from 2005 to 2011 were due to SQL Injection. The aim of this chapter is to understand how a SQL Injection occurs, and how to prevent it.

SQL injection involves an attacker injecting SQL code into an application in a manner that was not foreseen by the developers. For example, a website that has a product search screen could be compromised to get a list of customer details, perhaps including credit card information.

At the heart of SQL Injection is dynamic SQL. Dynamic SQL typically takes user input from a web page and dynamically builds SQL to query the database. However, in the case of a SQL Injection attack, the attacker terminates the expected SQL statements and creates new SQL to obtain data they want.

Various methods of getting this additional data are discussed, including UNION ALL, listing database tables, running xp_cmdshell , connecting via FTP to another server to download malicious software. Some sample ASP.NET is provided to illustrate the working of SQL Injection.

Various options to combat SQL Injection attacks are discussed, including blacklists (cause problems and don’t work), white lists (often essential, sometimes don’t work), and parameterized queries – the latter is the best and most effective protection. Stored procedures by definition are parameterized and are often at the vanguard of protection, just be careful they don’t use dynamic SQL internally. Other defences include using the functions QUOTENAME and REPLACE, and ensuring you only have the minimum permissions you need – often sites run SQL as dbo or even sa.

A few automation tools are discussed, these detect site vulnerabilities, and include sqlmap and Metasploit. While these tools can be used to highlight concerns, they may also have more nefarious uses… the chapter ends with a large list of resources and websites for further information.

This was another very interesting, if offbeat, topic. It shows the danger SQL Injection attacks pose, illustrating how it occurs and how it can be prevented. The real solution involves good programming practices and using parameterized queries. I was surprised there was no link to the chapter SQL Server Security 101 when discussing the need for appropriate permissioning.

Chapter 9 Using Database Mail to Email Enable your SQL Server

The aim of this chapter is to enable and configure Database Mail, and demonstrate its use in applications together with troubleshooting/monitoring.

The chapter begins by enabling Database Mail, either using sp_configure or via the GUI. This is followed by a step-by-step walkthrough of setting up Database Mail via the Database Mail wizard. In the wizard you create a Database Mail account and also manage the security profile.

Example SQL code is provided to use Database Mail, in your stored procedures you call msdb.dbo.sp_send_dbmail with various parameters (profile name, recipients, subject etc). It is also possible to send a mail that contains the results of SQL execution.

Another common use of Database Mail is to report the status of SQL Server Agent jobs. Example screenshots show how to link a SQL Server agent job to a mail profile. Similarly, mail can provide a real-time notification system i.e. if the database gives an error of severity 18, then email someone.

The last section discusses common Database Mail problems and their solutions. Additionally, various system views are discussed to interrogate metadata i.e. what has been processed, what problems have occurred. Finally maintenance of the Database Mail log table is discussed.

This was an easy chapter to read, with plenty of step-by-step examples to support the points being made (i.e. mailing SQL Agent job status, notification of system problems and within applications). There’s a routine included (dbo.email_alerts) that contains hard-coded details (e.g. email addresses), this would have been more useful if the hard-coded details were supplied as parameters. The text contains the word ‘bleUnless’ this should read ‘Unless’.

Chapter 10 Taming Transactional Replication

SQL Server replication allows you to copy data and database objects from one database to another, thus keeping the databases in synch. Replication is typically used to offload work to another server. There are various types of replication, of which transactional is the most common.

The chapter opens with a look at replication architecture, making comparisons with the publishing industry. Various terms are defined including: publisher, article, distributor, and subscriber. The various replication agents and the flow of changes between the publisher, distributor and subscriber are described.

There’s a useful section on the reasons for using replication, which relate to improving scalability and availability. Typical uses include: move data to a data warehouse, offload work to subscribers (e.g. reporting), integrate data from disparate sources. Reducing workload seems the most common reason. This is followed by an equally interesting section on when not to use replication i.e. want synchronous writes to subscribers or instantaneous updates. Transactional replication involves keeping databases in synch in a timely manner. Other types of replication are briefly discussed, including snapshot replication, and merge replication.

The chapter continues with a deeper dive into the details of transactional replication, discussing the role of the publisher, distributor, and subscribers, and all the various replication agents (snapshot, log reader, distributor etc). The requirements needed to implement transactional replication are also discussed (e.g. tables in publication must have primary keys).

There is a detailed step-by-step walkthrough of how to set up transactional replication. Plenty of screenshots are given, and since the database used is AdventureWorksDW2012, it should be easy to follow along with. The final sections of the chapter take a look at performance tuning, monitoring, and troubleshooting – with plenty of screenshots, links for further information, and useful code.

This is the longest chapter in the book, and in many ways, the most complete. Providing details of what transactional replication is, why you want to use it, how to implement it, and how to tune, monitor and troubleshoot problems. It was a reassuring read, with everything explained clearly. There’s some useful links included in the body of the chapter, and useful blogs/forums at the end.