Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

Segments for Protection

I haven't worked in many high security environments. While I have worked in a nuclear power plant, we had tremendous physical security and air gapped control systems, but cyber-security was still fairly loose from the perspective of internal systems. If anyone got to a local machine, they could have done some serious hacking and likely found lots of open information on our LAN. This was pre-Internet days, and I wonder if security has improved in those plants with the advent of the Internet and http protocols. I certainly hope so.

I thought back to that environment as I was reading a network security piece from DCAC on some of the problems they have seen with ransomware attacks. There are all sorts of things you can do to protect and educate users, but ultimately those humans are going to be a weak link. As a result, locking down network, and potentially account, access to servers is important.

In the piece, Joey talks about needing to use a special laptop, two keycards, and jump hosts to connect to a server for one customer. I have another customer that has a similar arrangement, using a jump box that's in a data center to connect to servers. Nothing runs from his desktop, with no way to connect to the actual DMV other than using RDP to visualize SSMS on a remote machine. In their systems, Microsoft has implemented some very secure SAW and PAW systems to allow access to Azure systems in a secure way for their employees, while protecting customers and auditing all actions.

These are all cumbersome, annoying, and painful solutions. They slow down the ability to get things done, but they also limit the mistakes people make. Who among us has logged onto the wrong machine and run a script? Worse, who might have granted access on the wrong instance before realizing it and adding the privilege on the right server. After doing that, did you remove the mistake from the first instance? Maybe, but experience has shown me many people plan to remove things later and then forget to actually perform the action.

This seems especially relevant today. I got a message as I was writing this from a user on one of the SQLServerCentral social network platforms. This user wanted to know if they could restore their database with only a log file as their data file was encrypted with ransomware. That's a bad day at work, and likely one that occurred because every machine on the network can connect with every other one. Convenient, but insecure and certainly a problem today.

This is one place the cloud forces you to be better. Everything is locked down from the start because Microsoft (Azure) and Amazon (AWS) do not trust the customers on their internal networks. They have to segregate everything, and force us to explicitly configure things ourselves. They make it easy, but we still need to do the work.

It's a scary time to work on systems, with constant scanning, probing, and attacks taking place on systems. It's also a time where we have more tools, templates, scripts, and capabilities for defense. We should use those to better protect our systems from ourselves.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

 
Redgate University
  Featured Contents

SQL Server Agent for SQL Express on Linux

Thom A from SQLServerCentral

This article will show you how to use crontab to schedule tasks that you want to run on a SQL Server on Linux instance when no Agent is configured.

Resolving could not open a connection to SQL Server errors

Additional Articles from MSSQLTips.com

Sometimes you may have issues connecting to SQL Server and you may get messages such as could not open a connection to SQL Server. In this tip we look at different things you can check to resolve this issue.

From the SQL Server Central Blogs - Implicit Conversion Fail

SQLRNNR from SQL RNNR

Implicit conversions are not uncommon in the computing world and can be viewed as kind of a fail-safe for when we don't quite follow decent practices when designing the...

From the SQL Server Central Blogs - What You Need to Know About Data Classifications in Azure SQL Data Warehouse

Meagan Longoria from Data Savvy

Data classifications in Azure SQL DW entered public preview in March 2019. They allow you to label columns in your data warehouse with their information type and sensitivity level....

 

  Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Accessing Always Encrypted Data

I have a column that is encrypted with Always Encrypted on SQL Server 2017. I want to query the table in SSMS, but I only can see encrypted data. I should be authorized to see the decrypted data, but I see this in SSMS v17.x. I run this code:
SELECT CustomerID
     , CustomerName
     , CustomerEmail
     , SecureCreditLimit
     , Active FROM customers
I get these results:
CustomerID  CustomerName    CustomerEmail SecureCreditLimit Active
----------- --------------- ------------- ----------------- -------
1           Steve           0x01AB3C223A6 0x018E0FB43ED     1
2           Andy            0x01B1B7A29F3 0xE060FAB9FED     1 
3           Kyle            0x01309196F71 0x01DCF7F90041E9  0
What should I do in order to see the decrypted data?

Think you know the answer? Click here, and find out if you are right.

 

 

 

  Yesterday's Question of the Day (by Thomas Franz)

Temporary data and Differential Backup size

I fill and empty some temporary staging tables inside my database between a FULL backup and a DIFFERENTIAL backup. Will this increase my differential backup size?

This is the type of work I am doing:

IF DB_ID('test') IS NULL CREATE DATABASE test
GO
USE test
GO
-- each row will be 4011 byte long, so only 2 records will fit onto one page
CREATE TABLE dbo.test_for_backup (id INT NOT NULL IDENTITY PRIMARY KEY, txt CHAR(4000) NOT NULL)
GO
-- create the FULL backup
BACKUP DATABASE [test] TO DISK = N'C:MSSQLBackuptest1.bak' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

-- insert 10k records, it will fill 5000 pages a 8kB = 40,000 kB = 3.9 MB
INSERT INTO dbo.test_for_backup (txt)
SELECT CAST(ROW_NUMBER() OVER (ORDER BY t1.n) AS VARCHAR(10)) AS txt
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) AS t1(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) AS t2(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) AS t3(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) AS t4(n)

-- remove the records again
TRUNCATE TABLE dbo.test_for_backup

-- what do you think, how many pages the DIFFERENTIAL backup will process?
BACKUP DATABASE [test] TO DISK = N'C:MSSQLBackuptest_diff.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Answer: No, only a few (about 120-150) system pages will be processed

Explanation: A differential backup will process only modified pages. When you insert the records 5k pages will be modified, but when you delete / truncate the rows again, the same 5k pages will be unallocated again (and removed from the dirty-pages-list). So there are no modified pages (from your staging table) that needs to be backed up and the DIFFERENTIAL backup will process only about 120 to 150 system internal pages, but not the 5k temporary storage. In reverse this means, that - when you restore a database from a FULL / DIFFERENTIAL backup - the physical file on the HDD/SDD will be different from the original one (since 5k pages were written to the drive after the insert, but not overwritten with x00 after deleting / truncating the table). So if you restore the database with another name, shut down the SQL server and compare the *.mdb files, you will find a lot of differences in the spaces, that are not allocated at the moment. Nice to know: if you create a FULL backup, insert the rows, create a DIFF backup (will process ~ 5150 pages this time), delete or truncate the table and create another DIFF backup, the second DIFF will process again only the 150 system pages (e.g. the GAM / SGAM pages, where it stores, which pages are allocated at the moment and where it had just disallocated the 5k pages) Ref: Backup - https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql

Discuss this question and answer on the forums

 

 

 

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.


SQL Server 2017 - Administration
Legacy Cardinality Estimation - hello , what is your best practice for the Legacy Cardinality Estimation parameter The default value of the Legacy Cardinality Estimation is OFF What will be the impact if I proceed to activate it ? thanks
Install failed ... but didn't? - I finally sat down to install 2017 on a clustered server and got a failure message that said "setup attempted to perform an unauthorized operation." So I opened up the detail.txt file and found the relevant section: (01) 2019-06-10 12:48:56 Slp: ---------------------------------------------------------------------- (01) 2019-06-10 12:48:56 Slp: Running Action: AS_Telemetry_Install_StartupFinalize_Cpu64 (01) 2019-06-10 12:48:56 Slp: Action Data: […]
SQL Server 2016 - Administration
CImageHelper::Init () Failed load of symbol file with path = - This error has occurred 2-3 times each night for the past 4 nights CImageHelper::Init () Failed load of symbol file with path = A:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\;A:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\BINN Error Code 0xc0000004 This is SQL 2016 enterprise 16 cores, 384 GB RAM. It's a three node cluster in an availability group.  This is the […]
Upgrade from SQL Server 2008R2 to SQL Server 2016 sp 2 performance issues - We have just upgraded our production SQL Server from 2008R2 to 2016 sp 2 and are experiencing some performance issues on our background processes.  We did not change the compatibility levels on our databases so they are still 100. The Legacy Cardinality Estimation is off and so is the query optimizer fixes. We left the […]
setup log shipping or replication from 2014 to 2016 - Hi all,   I have an on premise VM on which SQL server 2014 standard edition running which has databases in 3 TB approx, its a prod environment which is used to run mostly analysis and report kinda query. it gets input data from third party replication tool continuously.   There  is one more VM […]
SQL Server 2016 - Development and T-SQL
Retry logic for deadlock in while loop - Hi DBAs, I have a procedure to perform certain operations (insert and update in a table) in while loop with if...else conditions. At times, the operation fails with deadlock for any iteration. I am planning to implement retry logic but want to know how to do it with while loop. Please help me. In simple […]
How to update a table that causes temporary duplicate index - I have a table with a column that stores a set of sequential numbers and is an index with no duplicates. i need to know how to update those number by adding a select value to it. example: the column in question has 0,1,2,3,4,5,6,7,8,9,10.... lets say i need to add 5 to that column for […]
Administration - SQL Server 2014
Locating SSIS packages within SSISDB - A colleague is away travelling and an SSIS package they manage has started failing. I have opened up SSIS on the server and cannot see this package listed under either within file system or MSDB. When I look at the failing SQL job the step executes the package at a location that begins with /ssisdb/ […]
SQL Server 2012 - T-SQL
SQL ROW COUNT AND CTE HELP PLEASE - Hello, I am new with this I need help with this CTE, I need a report of all the Invoices paid, I am using 3 tables here but this CTE with ROW COUNT is taking the duplicates await. Usually you paid vendor twice a month or three times. so I need to see all records […]
Reporting Services
How to increase the timeout against a MySQL database - I've been tasked with writing a SSRS report against a MySQL database. This is the first time I've ever worked with MySQL, so new experiences for me. With the help and recommendation from one of my colleagues, I've written a SSRS report against the largest table in the database, as a proof-of-concept type of thing. […]
SSRS 2016
change the color each time the name of the server changes - hello , I have a report ssrs which gives me the state of the backup I would like to change the color of the line at each I change the name of the server who has an idea on a tip that can answer my need    
Header alignment issue - Hello, I have used a rectangle in report header and a tablix in my report body. If I fix the alignment in report preview, report pdf gets misaligned and vice versa. I have tried following- Set report width according to page width and margins manually setting the position Using table instead of a matrix   […]
Powershell
Powershell script to get the local adminstrators liston multiple windows servers - Hi   Could you please provide a PowerShell script to loop through multiple servers and get the list of local administrators of all servers? invoke-command {net localgroup administrators} -comp The above powershell command lists from one server. please provide a script to run on multiple servers.   Thanks San      
Integration Services
PSFTP using SSIS - hi   i m using psftp utilitie to post file on sftp server using execute process task.Its running fine on my local machine when i schedule job in sql server agent , it just keep running and block whole DB. is there a permission issue, we r using a service account to schedule job that […]
SSIS 2016 Excel stops working - Hello, I am using SSIS 2016 and Visual studio 2015 to create an SSIS package that just gets an excel document, and imports it into a SQL table, using the connection a variable (hope this makes sense or using correct vocabulary). However almost every other day or week, 1 column from the excel keeps erroring […]
 

 

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.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

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