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

Quick Security Mistakes

How many of you have gotten an urgent request from someone in your organization? Maybe it's a new database, perhaps a quick, "simple" change to an application, possibly even a new server or share that someone can use to complete their work. It's something new that you need to do. When that happens, how many of you ensure that you follow all the same steps and protocols to comply with the urgent request?

A few of you do, but when we're in a hurry, many of us don't necessarily complete every step. We may shortcut something to get work done. We may have the best of intentions to go back and complete the work, but in a busy environment, it's easy to forget to complete that last step, which might be configuring security or running all or unit tests or even decommissioning some resource that a user is done using. Some of you will realize these are big missteps, and some of you will still make them.

Someone at the Department of Transportation in Coloroado (CDOT) made a mistake like this (thanks to DCAC and Joey D'Antoni for the story). They stood up a virtual machine, connecting it to their local network, but failing to properly secure it. In this case, the machine was exposed to the Internet and connected with a domain admin account. As you might guess, someone got into the machine and executed a ransomware attack on CDOT.

As Joey points out, a few mistakes were made here, but these are the types of mistakes that anyone can make. Lots of us follow a process over, and over, and over again. Until we don't. Until we're distracted, busy, or in a hurry. They we take a shortcut. Most of the time nothing happens, but most of the time is becoming less acceptable. All of the time is the standard, which is why we try to use a DevOps, GitOps, or other process that ensures all the steps are completed. Not most of them.

Do yourself a favor and build processes to handle your tasks with a script or the push of a button. Ensure the operations are logged and audited. Use these processes to be sure that setting up a new system, database, application, etc. is done in a consistent and secure manner. There's still plenty of work to do for all of us. These processes will grow and change over time, and need to be maintained. Use your brain for the hard problem solving task of building a process and let the computer execute it, the same way, to completion, every time.

Steve Jones - SSC Editor

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

Redgate SQL Monitor
 
  Featured Contents

Setting up Management Studio for remote SQL Server

aveek22 from SQLServerCentral

Learn how to configure SQL Server and Windows to allow others to connect to an instance remotely.

ASP.NET Core with GitOps: Orchestrating Your Containers with Kubernetes

Additional Articles from SimpleTalk

Kubernetes can be used to deploy, scale, and manage containers. In this article, Mircea Oprea builds on the previous example in the series to show you how deploy your contains in the Elastic Kubernetes Service in AWS.

From the SQL Server Central Blogs - Backwards Compatible Symmetric Keys in SQL Server 2017+

Steve Jones - SSC Editor from The Voice of the DBA

I discovered recently that there was a change made in SQL Server 2017 to the way that symmetric key passphrases are hashed. There’s a KB article that notes the...

From the SQL Server Central Blogs - When Too Much is Not a Good Thing

SQLRNNR from SQL RNNR

Explore how to avoid unwanted results that oft occur due to lack of attention to detail, use of internet examples, misunderstanding of the requirements; and always from granting way...

 

  Question of the Day

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

 

CHOOSing a value

I have code that looks like this:
DECLARE @i INT = 4;
SELECT CHOOSE(@i, 10,20,30,3.14,50,'Test')
What is returned?

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)

Default Schemas

A developer is unsure of what schema a table will be created under if they execute this code:

CREATE TABLE SalesDetail
(SalesOrderID int not null,
SalesNotes varchar(max)
);

How can the developer check their default schema under T-SQL?

Answer: SELECT SCHEMA_NAME()

Explanation: The SCHEMA_NAME() function is used with no parameter. Ref: SCHEMA_NAME - https://docs.microsoft.com/en-us/sql/t-sql/functions/schema-name-transact-sql?WT.mc_id=DP-MVP-7914&view=sql-server-ver15

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 - Development
Advice on User Schema - Hello All, I have not been here on Central for a bit. The website looks great !!. Most of my SQL Career has been spent querying data for reports. Recently I decided I wanted to learn front end development. So I have been watching tutorials on Blazor/ C#  etc. I have now gotten to the […]
What is the easiest way to replace one string with another sting in 1000 files? - I need to once in a while replace a string of about 200 characters in each text file (~ 2 Mb each) with another string of similar length? There are 1000+ files in the directory, and I want to loop through each, replace the string if found, save file, move to next one, and so […]
SQL Server 2016 - Administration
Intermittent issue with cluster failover when SQL Agent service is restarted - Hi everyone, Some background: Very recently, we upgraded both our SQL cluster and the servers that houses it. We're now on SQL Server 2016 and Windows Server 2016. I'm the 'database administrator'; not a real one because I don't do the upgrades or make many changes to our set up, etc etc. I'm basically here […]
Extended events - Q1. Lets say I want to audit DDL , DML, DCL, DQL statements Can we use extended events to create an auditing solution in SQL Server for such a requirement ? Or are there better/efficient not so resource intensive alternatives ? Thanks Q2. What is best way to audit any account which connects to a […]
SQL Server 2016 - Development and T-SQL
Date Dimension - I'm in the process of creating a date dimension table with the usual fields such as DayOfWeek, DayOfMonth, DayName, IsHoliday, various different formats of date, etc. Much of our reporting relies on trending data and comparing to previous month(s), quarter(s), or year(s) and also month-to-date, quarter-to-date, and year-to-date indicators. There would also be a need […]
Saving output as SQL file - If anyone is working today I have a problem I'm working on. I have a script which when run in SSMS, generates a SQL update script which is saved as an SQL file and run later.  I am trying to automate this processes and generate a SQL file. Based on what I've found in google […]
Incorrect Syntax near ) - Hi On below code i get above error select T0.DocEntry,T0.DocDate,T0.CardCode ,T1.ItemCode, ,Coalesce((Case When T4.Street = '' Then Null Else T4.Street + ' ' End) + ',' + CHAR(10) + ',' + (Case When T4.Block = '' Then Null Else T4.Block + ' ' End) + ',' + CHAR(10) + ',' + (Case When T4.ZipCode = […]
Administration - SQL Server 2014
DACPAC StoredProc update - Hi, I am struggling to push stored proc changes to database through dacpac. If I set the build action to build it's throwing errors, the statement is not recognized in this context and build fails. If I set it to none, build succeeds and no changes are pushed to database. How to fix this?
Development - SQL Server 2014
Difference between where and INNER JOIN comparing a value - what is the difference between : Declare Phone varchar(50) UPDATE e Set e.[State]=2 FROM Events e INNER JOIN AccoutsAndPhones c on e.ClientID=c.ClientID And c.phone=@Phone WHERE e.Schedule>GETDATE()   and Declare Phone varchar(50) UPDATE e Set e.[State]=2 FROM Events e INNER JOIN AccoutsAndPhones c on e.ClientID=c.ClientID WHERE e.Schedule>GETDATE() and c.phone=@Phone the c.phone=@Phone is in different places. is […]
SQL 2012 - General
Importing a CSV files in SSMS - Hi, My manger uses the SSMS import to import his CSV files. he has one file he just got that has, like most carriage retunes and line feeds in them. For some reason he wants to import the file with the carriage retunes, but not the line feeds. I think you can, during the import state the row […]
SQL Server 2019 - Administration
SSIS Catalog vs Lift & Shift SSIS in ADF - Hi, We used to be able to create SSISDB from Integration Services Catalogs within SSMS.  This option has been changed that we can only create SSIS IR which is the Lift & Shift Your ETL Workload with SSIS in ADF.  This option requires us to sign in and subscribe SQL Azure.  We would like to […]
Confused on 2019 and containers - H/A - Hi We currently have a SQL2014 environment with 2 on premise and one DR server in another state - all connected via High Availability. Looking towards the future - I'm looking for a way to eliminate or improve our DR site that is in another state. After upgrading to SQL2019 would it then be possible […]
SQL Server 2019 - Development
Check if date column are in order - Hi, Thanks in advance. I have a datetime column in a table. I want to run a query to get a true or false value.. true if the datetimes in the table are in date order, and false if they are not in date order. Thank you.
SQL Newbie Help - Why can I not create a new column and add 90 days to the date in column P_INDATE in MS SQL Server? I believe you can do this in MySQL. The P_INDATE has a data type of Date.  
Integration Services
Data Migration to S/4 Hana - Hi , I Would like to migrate the data from Flat files to the S/4 Hana database . Can i do that using the ODBC connection? If some one has implemented it or has any knowledge can you please share your experiences or steps to be implemented using SSIS ? I am assuming that we […]
 

 

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

 

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