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

Ransomware and DevOps

Ransomware.

A scary topic and one that is apparently more common than I suspected. Before you go further, if you haven't restored a database backup in the last month, stop and go verify your DR plan works. That's one of the overconfident issues facing lots of government and businesses. While this might not help your entire organization, at least you'll have some confidence in your process.

This is a great article from Ars Technica and worth reading: A take of two cities: Why ransomware will just get worse. I'd recommend you read it and think about a few things. First, do you have insurance because things (substitute your own word here) happen? Second, have you really tested a DR plan for some sort of software issue like this? You might think about a way to restore systems in an air-gapped manner that prevents them from re-triggering encryption from a remote source, or maybe even in a scenario where you reset dates/times to prevent timer triggered issues.

Perhaps the bigger issue is are you actually patching and updating systems? Too many organizations can't, don't, or won't. The former means you aren't sourcing software properly. Either you're using vendors with poor practices or have a poor development process. Organizations that don't or won't bother prioritizing patching, especially security issues, are likely those that will have issues as more criminals find ransomware and other ways of attacking their systems. Software and environments continue to be more complex, which means that the less you ensure the system is patched, the more likelihood there is of a vulnerability in your environment.

DevOps and the cloud PaaS/SaaS platforms are attractive for a few reasons. One is that the platforms are constantly kept up to date, forcing you to move along with them. SaaS cloud vendors know this and are constantly patching and updating their software in order to keep it running. DevOps asks that we always have the ability to release, that we have the ability to patch on demand, not only at certain intervals. This is something I try to emphasize when talking about DevOps. It isn't necessarily about velocity, but it is about being able to release when you need to, whether that's today or next month. This is especially important for security issues.

I have had hope for a long time that insurance would drive software to higher quality, and I still do. With the attacks and issues of ransomware, and who knows what other techniques that will be developed, I still believe more companies will buy insurance. I then hope, because of selfish motives, the insurance companies will require frequent patching, regular vendor certification of new platform versions, and better development processes. If insurance drives DevOps, I'm all for it, but I'd prefer to decide to adopt it yourself and start making changes today.

Steve Jones - SSC Editor

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

Redgate SQL Provision
 
 Featured Contents

Politics makes for bad databases - plus calculate British holidays

RevOX11 from SQLServerCentral

In this piece, learn about changes in UK holidays and get a script to calculate them.

10 steps you can take to be compliant worldwide – free whitepaper

Additional Articles from Redgate

Learn how data protection legislation is changing around the world and the 10 steps database teams can take to ensure compliance and defend against data breaches.

From the SQL Server Central Blogs - Know How to Restore SQL Database Without Backup Easily

gana20m from Ganapathi's MSSQLLover

 Restore SQL Database Without Backup -Step By Step Guide 
Microsoft SQL server is one of the best and the widely used relational database management system. The primary function of this...

From the SQL Server Central Blogs - Getting Your SQL Server Backup Footprint

John Morehouse from John Morehouse | Sqlrus.com

Recently, I needed to be able to determine the total size for all the most recent full backups for certain servers. Luckily for me these particular servers were using...

 

 Question of the Day

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

 

Running a Docker Container

I want to practice working with SQL Server in a container for my development machine. Which of these variables must I specify when I execute the docker run command?

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

 

 

 Yesterday's Question of the Day (by Steve Jones - SSC Editor)

The CUME_DIST ranking

I have this code in SQL Server 2017:

WITH myTally(n)
AS
(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
 FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
  CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n)
)
SELECT TOP 100 n
INTO Rank100
FROM myTally;

I want to execute this code to get the number and the CUME_DIST ranking.

SELECT n
, CUME_DIST() OVER (ORDER BY n) AS cd
 FROM dbo.Rank100
 ORDER BY n

What is the value returned for n = 10

Answer: 0.1

Explanation: The CUME_DIST() function returns the ranking as a percentage. For 100 values, the 10th item is 10%, or 0.1. Ref:

Discuss this question and answer on the forums

 

Featured Script

Look up AD user properties using powershell

SQLPals from SQLServerCentral

I realize that this is a bit of a lengthy script for something that you can use a one liner in the power shell:

Example:

Get-ADUser

But I needed to highlight certain properties of given user and take certain actions based on the values. And the result is this power shell script.

try
{

Import-Module Activedirectory

# we will grab current domainuser if no specific user or domain is specified

$domain_name = ""
$username = ""

if ($username -eq "") {$username = $env:UserName}

"User Name: " + $username

""

if ($domain_name -eq "") {$domain_name = (Get-ADDomain).Name}

"Domain: " + $domain_name

$domain_controller = (Get-ADDomainController -Discover -DomainName $domain_name).HostName

"Domain Controller: " + $domain_controller

$domain_FQDN = (Get-ADDomain $domain_name).DNSRoot
"Domain FQDN: " + $domain_FQDN

$domain_DN = (Get-ADDomain $domain_name).DistinguishedName

"Domain Distinguished Name: " + $domain_DN

""
# lets look up the user in the AD
$get_aduser = Get-ADUser -Server $domain_FQDN -Properties * -Filter {sAMAccountName -eq $username}
If ($get_aduser -eq $Null)

{

"Attention: User $username not found in AD domain $domain_FQDN"
return

}
Else {"SUCCESS: User $username exists in AD domain $domain_FQDN"}

"Is password expired? " + $get_aduser.PasswordExpired
"Is user enabled? " + $get_aduser.Enabled
"Is user Locked Out? " + $get_aduser.LockedOut

$group_membership = Get-ADPrincipalGroupMembership $username

""
"Group Membership:"
"----------------"
$group_membership.Name

# now display all user properties
$get_aduser
}

catch
{
$_
}

More »

 

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
Need connect string to connect from Linux to SQL Server using an A/D group name - ISO way to setup a connection string to connect from Linux to SQL Server using an A/D group name. (we do NOT want to use a SQL Login!) - this link details the setup for a Linux connection to a SQL Server database: https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Linux - one of the final instructions during setup is to add […]
SQL Server 2017 - Development
How to split time into hourly slot using SQL (can use view,or stored proc or fun - I have data in table which has start date, end date and duration. I want to show hourly time slot. **logic**: - Condition 1. If start date =9:00 and end date = 11:00 then show the date as 09:00-10:00 10:00-11:00 It should repeat 2 times and all related column data will also repeat 2 times. […]
SQL Server 2016 - Administration
SQL Cluster - I am looking at a Cluster that has been built by a person that during the SQL Cluster installation he did not select all CSV's that related to the instance.  This has meant that the storage fails over independently which is not ideal.  there has been a case that two of the required volumes were […]
Admin puzzler - ok so Friday off I get a call from user doing  posting taking over an hour( should take 5 minutes).  No blocking do deadlock no high CPU.  Active sessions show higher reads than normal.  No open trans.  Go to mgmt. studio and when I try to open objects on main pharmacy database it times out.  […]
SQL Server 2016 - Development and T-SQL
Using @@ROWCOUNT To Return Second Recordset in Sproc - I'm working with a Stored Procedure that hopefully will return two recordsets to a Classic ASP application. Currently, when I test my Stored Procedure, @@ROWCOUNT always returns the count of 1, even when the count is not 1. Here is my code for the Stored Procedure (below). Would I need to return the @row variable […]
Problem to Display Field With OPENXML - Hello community, I am trying to Importing and Processing data from XML files into SQL Server tables, following the example post on this site : https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/ by Arshad Ali. This is the first time i use this technique, then i put only one filed to display, but i dont know why the result is always […]
Administration - SQL Server 2014
Auto Shrink on live DB - Hello, On one of our customers' live server there are both live and UAT DBs. The latter occasionally replicated from the live DB upon customer's request. All has been done and maintained by DBA's.  When such a copy generated I am normally required to truncate several of our biggest tables (some over 100 MBs), which […]
SQL Server 2005 Installation After 2014 as Default Instance on Win Server 2012 - Greetings! Did anyone notice the anomaly as in below screenshot? When tried to install SQL Server 2005(bare version 9.0 - No SP) after SQL Server 2014(version 12.0) is already installed, the SQL 2005 threw error showing SQL 2014 installation as SQL 2000. OS : Windows Server 2012 X64 SQL Server 2014: Developer X86 SQL Server […]
Development - SQL Server 2014
Stored Procedure with Parameters in Excel - Long Run time - I have a stored procedure with two date variables, Start date, End date. If I run this in excel, it takes forever and eventually deadlocks. ALTER PROCEDURE [dbo].[lost_sales_v2] @SD datetime, @ED datetime If I take the variables out of the procedure name and embed a set date range in the query, it returns in less […]
SQL 2012 - General
Using Correlated Queries with OPENQUERY - Good day everyone,   I'm new to this forum and was hoping I could get some help with what I'm trying to accomplish. I'm trying to delete old users from each respective database from a list I have. Now I'm using SQL Server 2012 to do this, but the databases i'm connecting to are in […]
SQL Server 2012 - T-SQL
Index Size Question - I'm trying to track down storage usage on our SQL Server 2012 instance.  I have a table with 11,703,018 rows and 10 indexes (9 non-clustered + PK) on it.  The PK is a clustered uniqueidentifier (I know not the greatest but i'm stuck with it for now) that only has the uniqueidentifier column included (size […]
Find time different based on Type by making group - I have data like attached picture, I want to make group each event when it starts from 1 and end till its max of eventId = 14 and calculate the time difference ... as shown in picture. If there are 8 EventType with 1 then it should have max of 8 eventType with EventType 14. […]
SQL Server 2008 - General
Unable to load text data in using BULK INsert with XML Format file - I have the following table   IF OBJECT_ID('[TempDB]..[#LoadData]') IS NOT NULL DROP TABLE [dbo].[#LoadData]; CREATE TABLE [dbo].[#LoadData] ( [RTOPartyID] INT NULL , [Prefix] NVARCHAR(255) NULL , [FirstNames] NVARCHAR(255) NULL , [LastName] NVARCHAR(255) NULL , [Suffix] NVARCHAR(255) NULL , [PartyType] NVARCHAR(255) NULL , [PlaceOfBirth] NVARCHAR(255) NULL , [TaxIdentificationNumber] NVARCHAR(50) NULL , [CrownServant] BIT NULL , [DateOfBirth] […]
Replication DB name - Hi, Supposing we would like to replicate a copy of a DB, called DB1 to another server. Does the database need to be called DB1 on the subscribers or does that not matter?
Powershell
Something akin to GROUP BY in SQL - So I have an array: $array = @() $Props = [ordered]@{Table="Table1"; Col1=1; Col2=2} $array += New-Object psobject -Property $Props $Props = [ordered]@{Table="Table2"; Col1=4; Col2=5} $array += New-Object psobject -Property $Props $Props = [ordered]@{Table="Table1"; Col1=3; Col2=7} $array += New-Object psobject -Property $Props $Props = [ordered]@{Table="Table2"; Col1=2; Col2=6} $array += New-Object psobject -Property $Props I want to […]
 

 

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

 

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