Problems displaying this newsletter? View online.
Database Weekly
The Complete Weekly Roundup of SQL Server News
Hand-picked content to sharpen your professional edge
Editorial
 

Protecting Data from the Inside

It may seem unkind to suggest that your data is at risk from authorized users, either accidentally or maliciously, but insider threats are one of the major causes of data breaches.

Let me give an example. In 2011, Britain's News of the World newspaper folded after 168 years of publication when various immoral or illegal practices of its journalists were revealed. The Wikipedia entry 'News International phone hacking scandal' gives an excellent summary.

Journalists, it was alleged, were equipped with phone numbers of people, working in large companies and government departments. For a fee, employees of these organizations would then access their database systems to divulge useful information about individuals. The journalists were, in this case, most interested in celebrities, politicians and the aristocracy but everyone was at risk. Some journalists boasted that a day's work on the phone could produce details of the personal life about almost anyone, often before the police.

One journalist investigated by the police in 2002 reportedly bought information from former and serving police officers, customs officers, a VAT inspector and bank employees who had access to the data. They also employed 'blaggers' who would telephone the Inland Revenue, the Vehicle Licensing Agency, banks and phone companies, and deceive them into releasing confidential information from their records.

However impervious to external attack your application and database may be, the data that your organization holds may still not be safe. It is not an option to trust solely in the probity of your internal users, or to rely on their good sense with data.

How on earth do you guard against the theft of data within an organization? Firstly, determine what sensitive data you hold and why, and where it is stored or cached. If you hold sensitive data, then you must be able to identify individual authorized users of it. Next, you need a system that monitors usage that is entirely separate from the database system. It must log all accesses to the sensitive data. With all this in place, you can introduce a configurable alerting system that looks for any sort of unusual usage.

The database must use full access control, so as to prevent any SQL queries from accessing tables that have sensitive data in them. I always recommend an interface of stored procedures and functions to allow applications to access this type of data, and to make monitoring easier. Through this interface, you can restrict access to sensitive data to just the minimum the user needs to complete their task. There should be a fine-grained range of database roles that match the roles of members of the organization, so that no user is able to access either more data than is necessary for the business operation, or data that is irrelevant or inappropriate. Data exports must be restricted to aggregations done to the finest level of detail needed for reporting.

A seasoned curator of data will never be a popular figure in the organization, because insider data breaches are seldom publicized and the extent of the threat is greatly underestimated, but it is certainly there.

Phil Factor

Join the debate, and respond to the editorial on the forums

 
Redgate Database Devops
The Weekly News
All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit.
Vendors/3rd Party Products

Recreating Databases from Scratch with SQL Change Automation

Phil Factor starts with the basics how to rebuild a set of development database from scratch, using SQL Change Automation, and then demonstrates how to check for any active sessions before rebuilding, import test data using BCP, and secure passwords if connecting to the target with SQL Server credentials.

Using the SQL Compare command line with Argfiles

Phil Factor demonstrates how using XML argfiles to pass parameters to SQL Compare cuts out a lot of the tedious scripting involved in modifying a database schema comparison and deployment process, as required for each target database.

SQL Compare 14.0: A Command Line Interface on Linux and support for SQL Server 2019

SQL Compare is the industry standard tool for comparing and deploying database schema changes, and version 14.0 now provides a Command Line Interface (CLI) on Linux (available as a beta, at time of writing). Teams working with SQL Server on Linux, or across multiple platforms, benefit from seamless development processes, driven by SQL Compare, without the expense or ‘friction’ of needing to maintain a Windows VM on their Linux servers.

Administration

Recovery of database is 0% complete (approximately 1000000 seconds remain)

Andy Mallon explains what happens during database crash recovery, and the need for patience.

SQL Server Needs Health Views

Erik Darling Proposes that, in a centralized schema, maybe called health, there should be stored all the details of Backups (schedule, failures), CHECKDB (last good, failures), Agent Job Failures/Operators Notified, and so on.

Azure SQL Database

Azure SQL Database Price and Performance vs Amazon RDS

GigaOm recently published a performance benchmark of Azure SQL Database as compared to Amazon’s RDS service, but Joey D'Antoni wants to do a raw pricing benchmark - it turns out Azure SQL Database is cheaper.

JSON in your Azure SQL Database? Let’s benchmark some options!

Silvano Coriani explore the options for loading and retrieving JSON data, in Azure SQL Database, and benchmarks the results.

Computing in the Cloud (Azure, Google, AWS)

ADS – Did I Save My Changes?

After working on a query for a long time, we want to make sure that we save the changes we have made. I have lost hours of work over the years because I didn’t save the changes. Azure Data Studio has a few features that can help prevent this from happening.

Conferences, Classes, Events, and Webinars

Tactics for classification using SQL Data Catalog

Wednesday October 23 16.00-17.00 BST/ 10.00-11.00 Central - Discover different tactics enabled by Redgate’s latest new product - SQL Data Catalog – to build a clear picture of your SQL Server estate, enabling you to ensure that the correct data is protected in the correct way.

What we learnt at PASS Summit

Wednesday November 13 16.00-17.00 GMT/ 10.00-11.00 Central - PASS President Grant Fritchey is joined by fellow Microsoft MVPs Kendra Little, Steve Jones and Kathi Kellenberger to discuss their highlights and learnings from PASS Summit 2018, the largest Microsoft Data Platform conference.

How to make your 2020 monitoring strategy a success

Wednesday November 20 16.00-17.00 GMT/ 10.00-11.00 Central - Are you ready for growth in 2020? Join Tony Maddonna, Microsoft Platform Lead & SQL Server Enterprise Architect at BMW Manufacturing and Redgate's Arneh Eskandari to discover how to prepare for successful estate growth in 2020

SQL in the City Summits

Redgate is hosting an upcoming series of exclusive Summits, in cities worldwide, to help senior data professionals deliver value quicker, while keeping their data safe. Find out more and register for your nearest event

SQLSaturday #934 - Rio de Janeiro 2019

On Nov 23, 2019, a free day of SQL Server training in Brazil

SQLSaturday #895 - Parma 2019

On Nov 23, 2019, a free day of SQL Server training in Parma, Italy.

SQLSaturday #925 - Porto, Portugal 2019

On Nov 23, 2019, a free day of SQL Server training...

Redgate Database Devops
Database Design, Theory and Development

SQL and Scope

Understanding scoping rules is a basic skill for developers. In this article, Joe Celko gives a bit of the history of scoping in early programming languages and shows how scoping applies to SQL queries as well.

DevOps and Continuous Delivery (CI/CD)

Introduction to DevOps: Database Delivery

Before including the database in your DevOps pipeline, you’ll need to decide if you are going to use a state-based or migrations-based approach. In this article, Robert Sheldon explains the difference between them and discusses the benefits of each.

Establishing a DevOps Organisation

DevOps might sound intimidating if your organisation has not yet begun on its DevOps journey. In this article, Rahul Varshneya explains some of the benefits and reviews the stages all DevOps organisations go through to achieve it.

DocumentDB/Key-Value/Graph/other NoSQL Databases

Geneology with SQL Graph – I

How many first/second/third cousins does this person have? If you have the genealogical data, SQL Graph can give the answers.

ETL/SSIS/Azure Data Factory/Biml

Delta: A Data Synchronization and Enrichment Platform

It is a commonly observed pattern for applications to utilize multiple datastores where each is used to serve a specific need. Now the challenge becomes how to keep these datastores in sync.

How Netflix microservices tackle dataset pub-sub

In a microservice architecture such as Netflix’s, propagating datasets from a single source to multiple downstream destinations can be challenging. These datasets can represent anything from service configuration to the results of a batch job, are often needed in-memory to optimize access and must be updated as they change over time.

How to test ETL Processes in production

Ed Elliott concludes his 4-part series by explaining what's involved in "testing in production”.

HA/DR/Always On/Clustering

Availability Groups and Redo Latency, Data Committed on a Primary isn’t Visible on a Synchronous Secondary

If you’re running availability groups it may seem like a great idea to offload your read only reporting workloads to a secondary. But, redo latency means that the data might not be instantaneously available on the synchronous secondary node.

Upgrading SQL Server using Availability Groups – Checklist

A checklist, and some useful queries, for upgrading a pair of physical SQL Servers that hosted a single availability group of several terabytes of data, with minimal downtime.

Performance Tuning SQL Server

Understanding Event Loss with Extended Events

Jonathan Kehayias on how and when Extended Events will discard an event that has been generated during data collection.

Using SQL Server Performance Objects

Thomas LaRock on the importance of the details that provide context to the PerfMon metrics, helping users understand what they are measuring, and where to find those details.

SQL Server Needs Performance Views

Erik Darling wants every database to have in it views to fully assemble data from the mess of DMVs that accompany query store, plan cache use, index use and so on.

Getting Smart About Fixing Key Lookups

Creating smart indexes, or occasionally rewriting the query, to fix performance e problems related to key lookups.

PowerPivot/PowerQuery/PowerBI

Why Does Power BI Query My Data Source More Than Once?

Of you find that Power Query is indeed querying your data source multiple times, the most common explanation is that it’s doing a “background refresh”.

Showing the top 5 products and Others row

How to add an additional “other” row to the selection obtained using the Top N filter in a Power BI report.

Updated Data Profiling in Power BI Desktop

One of the big things that was lacking with the Data Profiling feature was the text length statistics, leading to incorrectly sized string columns in data warehouses. Well, the wait is over, text lengths are now available.

Power BI Gateway — When Your PowerBI.com Login Doesn’t Match Your Active Diectory User Principal Name (UPN)

With some data sources, such as Analysis Services, you want to pass the username of the person running the report back to the server executing the query, but if your Power BI login does not match a UPN in our local Active Directory, then the lookup will fail…

Power BI dataflows and query folding

Matthew Roche tries to clear up a few confusions surrounding use of dataflows and query folding.

PowerShell

Making SQL Agent Jobs Availability Group aware with dbatools

How to you make sure that your SQL Server Agent jobs only do work on the instance that currently owns the Availability Group.

PowerShell Notebooks in Azure Data Studio

Create and share live PoweShell code!

Getting SQL Server installation date with PowerShell using dbatools

Finding out when SQL Server was installed across all your instances.

Product Upgrades and Releases

SQL Server 2019 Standard Edition Feature Limitations Are Out

Microsoft have published the features list by edition, for SQL Server 2019 (but are still editing it!)

Reporting Services

Branding PowerBI Report Server & SSRS 2017

One of the benefits of moving beyond 2014 (with SSRS) is that the appearance of the web portal (company logo & color scheme) can be customized (branded) using the same files that are used for branding PowerBI Report Server (PBIRS).

SQL Server Security and Auditing

Find Embedded SQL Server Logins in Jobs, Linked Servers or SSISDB

A database login or user (SQL Login, Oracle User, etc.) is actively being used and embedded in code, but the password is about to change. How can you find all of the places where it exists with SQL Server such as Jobs, Linked Servers and SSIS information stored on the server?

Security News and Issues

Cracking the Passwords of Early Internet Pioneers

Lots of them weren't very good. Weakest of all was the password for Unix contributor Brian W. Kernighan: "/.,/.," representing a three-character string repeated twice using adjacent keys on a QWERTY keyboard.

T-SQL

Impact of UTF-8 support in SQL Server 2019

In SQL Server 2019, there are new UTF-8 collations, that allow you to store your UTF-8 data natively. But what is the actual storage impact, and how does this affect memory grants and query performance?

What Does PERCENTILE_CONT Do?

Kathi Kellenberger takes on a logic puzzle in trying to understand how the windowing function, PERCENTILE_CONT, works.

Using indexed views? What is an imprecise or non-deterministic convert?

A short but interesting side-road into deterministic values, and why it is important to get your data types correct.

What the heck is the SQL Server Version Store?

Andy Malllon takes a high-level look at what it is, what it does, what it isn't

Are Stored Procedures Faster Than Stand-Alone Queries?

Does a query embedded in a stored procedure execute faster than that same query submitted to SQL Server as a stand alone statement?

How to Think Like the SQL Server Engine: Adding a Nonclustered Index

Brent Ozar continues his series helping us to understand what indexes SQL Server needs to run queries efficiently, by thinking like it.

Finding Your Isolation Level

If you're troubleshooting SQL Server or Azure SQL Database performance issues that you think are related to concurrency, it's useful to know what isolation level is being used to execute a query. Bob Pusateri explains how to find out.

Ranking Functions in SQL Server

Suppose you are designing an SQL Server database application for a company’s CEO and you have to display the fifth most highly paid employee in the company. What would you do?

Testing Software

SQLCover 0.5 - Fixes, smaller features and an exciting surprise

Ed Elliott updates SQL Cover, his open source SQL code coverage library.

 
RSS FeedTwitter
This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. Note: This is not the SQLServerCentral.com daily newsletter list, and unsubscribing to this newsletter will not stop you receiving the SQL Server Central daily newsletters. If you want to be removed from that list, you can follow the instructions on the daily newsletter.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -