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

More Network Restrictions

This past year has seen quite a rise in ransomware attacks. Certainly some big ones (Baltimore, Florida) are reported in the media, but there are lots of smaller ones that don't make the news. As I've been attending some events in the last couple months, I've talked to various people that disclose they've had ransomware issues. Most of those didn't make the news, but the events did cause IT staffs to scramble, work long hours, and forgo other tasks that might be improving the organization or helping customers.

We know that there will be attacks on our organizations in the future, and some of them may be successful. Almost everyone is regularly attacked, though most are repelled with simple firewalls and better coding to prevent SQL injection. There are other things we can do, and certainly other groups in IT that need to worry about systems, but I suspect we'll start to see one more change in how we work.

Recently I was accepted to speak at SQL Saturday Memphis and really enjoyed the trip. However, when practicing one of my demos early in the am, I had trouble connecting to some remote resources. It wasn't obvious what the issue was, and since I wasn't in control of the endpoint for demos, I worried that the instratructure had an issue. Not what you want to see on a Saturday before a demo.

Eventually I narrowed this down to port 1433 being blocked. Not a big deal as I had a VPN, but certainly something to be concerned about for a database professional. However, then I thought about the ransomeware and other security issues many organizations have had. Maybe some restrictions are a good thing.

I wrote about networking segmentation, as did Joey D'Antoni, and I think this is where we will move in the future. After the SQL Slammer worm, plenty of organizations changed networking policy to hide port UDP 1434, which was good. I think some have gotten lazy, and the security issues we are seeing today might change things.

That's going to mean less access for people to connect to machines. That also means we need to think about DevOps style automation pipelines for code. We need to learn more about how to let machines run scripts for us and limit access. That way if our machine is attacked, we won't necessarily be able to spread the issues to servers. We'll still have plenty of other machines that might get hacked, but hopefully we can protect our servers.

Security has become a bigger part of the job for all IT pros, and certainly critical for database pros. I hope that most of you embrace more stringent security and not try to circumvent it. Use automation, proxies, and more to keep your systems secure. And if they don't quite work as you'd want them to, learn how to adjust them and work with the system, not against it.

Steve Jones - SSC Editor

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

Redgate Database Devops
 
  Featured Contents

Using Graph Theory To Group Records

Glen Cooper from SQLServerCentral

Learn a little basic graph theory in this article that replaces a GROUP BY clause.

Read Excel File in SQL Server with OPENROWSET or OPENDATASOURCE

Additional Articles from SQLServerCentral

In this tip we look at how to read an Excel file from within SQL Server using OpenRowSet and OpenDataSource along with the possible errors you may encounter and how to fix these issues.

From the SQL Server Central Blogs - Geneology with SQL Graph – I

Diligentdba 46159 from Mala's Data Blog

I have been working a lot of SQL Graph related queries and applications of the graph data concept to the extent possible within SQL Server’s graph capabilities. Genealogy, or...

From the SQL Server Central Blogs - SQL in the City Back in London

Steve Jones - SSC Editor from The Voice of the DBA

It’s just a couple weeks until the SQL in the City Summit comes back to London. This is our second Summit this year in London, and I’m honored to...

 

  Question of the Day

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

 

Checking on IFI

I am connected to my remote SQL Server 2017 instance with SSMS and want to quickly check if Instant File Initialization is enabled. What is the easiest way to do this?

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)

Creating a Skeleton Data Frame

I want to create a data frame for scheduling SQL Saturday sessions with just column names. My idea is to set these columns: SessionTitle, SessionTime, SpeakerName, RoomAssignment. I plan to put this in the variable MySchedule.

Which code will create this?

 

Answer: MySchedule <- data.frame("SessionTitle"=character(), "SessionTime"=character(), "SpeakerName"=character(), "RoomAssignment"=character())

Explanation: To get an empty data frame with column names, each column is passed in a comma separated list, with the equals (=) sign and a datatype. Ref:

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
2012 - 17 Performance Issue -   Good afternoon everyone, This may sound familiar, but we are yet to find a solution. We are in the middle of a platform upgrade from 2012 - 2017 utilising some vastly superior hardware. However we have found that performance has degraded, rather than the expected improvement on the tests so far. For example, when […]
SQL Server 2017 - Development
Datetime data migration. - Hi All, We are loading data from one of our source table in that table createddate column datatype is char(22)  and value is 2012-06-03 04:11:49:16. And in my target table the column datatype is datetime and we are using convert datetime function to convert char to datetime. But the date is converting to 2012-06-03 04:11:49:016. […]
Always Encrypted with SQL Server Data Project - Hi, We have a small database (just 3 tables) where we wish to use Always Encrypted to encrypt the data in a few of the fields in each table.  I've managed to successfully encrypt the data using the SSMS wizard and powershell scripts,  and I can read/write from our application. I'm now looking for some […]
SQL Server 2016 - Administration
Change SQL Agent service account and password on 2 node active-active cluster - What are the steps to change SQL Server agent service account and password on 2 node active -active. Thanks in advance.
SQL Server 2016 - Development and T-SQL
Gathering Business Requirements and Determining Database Schemas - What is simplest way to go about gathering business requirements and determining database schemas?
insert into permanent table using cte - Good morning all I would like to add the result of a multiple query in a table the goal is to have this formathere is my request ;with cte as ( blablablab ) INSERT INTO newtable select * cte here is my request with echantillontemp as ( select Ref_Contrat = v.Contract_ID + '-' + format('0'+ […]
Administration - SQL Server 2014
assist needed for the SQL Server dump - Hi Guys, I got 14 dumps in last two days, I tried to use WinDbg to check what is going, but I don't get a clear information from the output, can anyone help pinpoint the cause? I know it is very likely failed at sqlmin!CChangeTrackingDatabase::CleanupNextTable+711, but not sure is it failed at specific database, or […]
Development - SQL Server 2014
A bit of mathematics... - Hi all, I'm just doing some work on my own bank statements imported from CSV.   Two salient columns, MoneyIn (int), MoneyOut (Int). This is the formula I'm trying to calculate: ((Revenue - Outgoings) * .81) - Dividends Paid = Result I've been able to Select (Select Sum ([Money In]) As 'Revenue' from SmytheData.dbo.Transactions3) - […]
Need Help: On Query with Case Statement - Hi, As part of development task I have to write a SQL logic for the below 6 scenarios, I have achieved desired result in my query using Case Statement but I want to know is there any other options(ways) where I can write query without using Case Statements to achieve the same result using sub-query […]
SQL Server 2012 - T-SQL
Question on triggers - Hi, I have, as most triggers are, a single row trigger; that is it will only update one row, and that works fine. However, someone now runs a SQL Query that updates multiple rows at once, and I have not been able to see how to get this to do both. Here is the trigger […]
transactions(Try, commit and rollback) - Ignore post
Reporting Services
Moving from RDL to RDLC files in SSRS - For any custom reporting in an accounting software program m,y company uses we have to use the RDLC flavor of SSRS reports.  I can find numerous tools, tips, samples, you name it for SSRS reports that use the RDL file but the RDLC is almost non-existent.   Does anyone know if there is a way […]
Pull dynamic reportpath of RDL into SQL inside the RDL. - Hello, Long story of not best practice that I have inherited and am slowly trying to change. (No judgement please). I have a set of approx. 1000 report server reports that have SQL embedded in them (Insert sad face). Some of the reports are locking each other and sometimes taking down databases. My idea is […]
SSDT
Value of extended property in T4 script returns a quoted, escaped string? -   Hi, I have a T4 script which reads extended properties. The value of these properties seems to be escaped. Example: I have a table person with an extended property called propertyName that has the value propertyValue. When I read this value I get: N'propertyValue'. The value that appears in the T4 script is the escaped value. […]
Integration Services
how to check if file contains only column headers without data - Hi All, i have a task to check file (excel & text) before loading to a table. checking condition: if file only contains header(columns) without any data then fail the file. I have filename & source file path variables already - any idea how to handle this in script task? much appreciate!  
 

 

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

 

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