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

Don't Lose Data Without a Hacker

The GDPR has been law since 2016 and been enforced since mid 2018. California has the CCPA in law, but not being enforced. In any case, it's 2020 and we have lots of tools and knowledge about securing systems. We aren't perfect, and certainly Microsoft isn't, but we should be avoiding simple mistakes.

For those of us in the US, or outside CA, we might be less worried, but if you're in the EU, you should be concerned. Here is a mistake by Virgin Media, with data exposed for months. Someone configured a database, likely set it up for access remotely, and didn't do a good job.

It does start to feel that we ought to have some sort of security smoke test in every organization that checks for exposed databases in our networks. Perhaps we even ought to have this for all subscriptions with vendors that host services for us in the cloud. That might be good for IT people, but what about shadow IT, or the average person that just wants to share data with co-workers?

No one ought to be able to configure a file share, an S3 bucket, Azure BLOB Storage, or anything similar by clicking in some sort of control panel. Honestly, I get that vendors want to make things easy, but we need security over data in all organizations. There ought to be a "configured" click to share button that runs a series of scripts to ensure we have secure controls over resources. File shares might be hard, but for databases, there's no excuse.

I do know that for some people, it's a pain when they can't create databases. I deal with customers regularly that have this restriction on developers, and it's problematic. However, we need better security, and really, we need less real data being shared so widely. As an industry, we need better dev data sets, and we need better security protocols over any production data.

Steve Jones - SSC Editor

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

 
 Featured Contents

Restore SQL Server database using data management provider REST API

Pablo Echeverria from SQLServerCentral

Learn how to restore sql server database using data management provider Rubrik REST API

Verifying Object Exists and Dropping Object with One Statement

Additional Articles from Database Journal

For years you probably have been writing code similar to the code below to verify an object exist prior to dropping it.

Build and fill a SQL Server Database using SQL Compare CLI and a Batch Script

Phil Factor from SQLServerCentral

Phil Factor provides a powerful DOS batch script which, when coupled with SQL Compare CLI, allows you to build databases from source, during development, and fill them with the specific datasets required for testing.

From the SQL Server Central Blogs - Query Store for Workload Replays

jsterrett from John Sterrett

This month’s T-SQL Tuesday is hosted by Tracy Boggiano. Tracy invites us all to write about adopting Query Store. Today, I wanted to share my favorite but a very...

From the SQL Server Central Blogs - Power BI Security

James Serra from James Serra's Blog

A common topic I have been discussing recently with customers is the security around Power BI. Basically, how to prevent users seeing data they shouldn’t. So I’ll discuss the...

 

 Question of the Day

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

 

More Memory Optimized Table Limitations

In SQL Server 2017, which of these database options is not supported when you use Memory-optimized tables?

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)

Filtering Data

I have a simple data frame that looks like this:

> passing.2019
     Player Yards Conf age
1   Winston  5109  NFC  26
2  Prescott  4902  NFC  25
3      Goff  4638  NFC  26
4    Rivers  4615  AFC  38
5      Ryan  4466  NFC  34
6    Wilson  4110  NFC  31
7     Brady  4057  AFC  42
8      carr  4054  AFC  28
9     Wentz  4039  NFC  27
10  Mahomes  4031  AFC  24

I want to reduce this down to those players in the AFC that are older than 30. Which of these functions will do this?

Answer: filter(passing.2019, age > 30, Conf=="AFC")

Explanation: In the dplyr library, you can use the filter() command to do this. Ref: filter() - https://www.rdocumentation.org/packages/dplyr/versions/0.7.8/topics/filter

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
Partitioned data files growing 5x after SQL 2017 Upgrade - Hi All, We recently upgraded one of our OLTP systems from SQL 2014 to SQL 2017, and after the upgrade I observed that our main data disk was quickly filling up. Ran a query to see if perhaps we were ingesting a large volume of new data for some reason, but based on the row […]
Sql to oracle replication - I am looking for large data transfer from sql server to oracle on a daily basis and incrementally And the Sql server will be configured as always on. I have looked into few options but want to know more from the experts about pros and cons. Sql server Replication to oracle is an option but […]
SQL Server 2016 - Administration
sql server Always on patching - Always on(SQL server 2016) is configured between four standalone servers Server1,Server2,Server3,Server4. These servers contains one availability Group(AG01) which contains 3 databases. How to apply SQL server patch for these servers? Do we need to apply patch for all servers at same day or on two different days?  These servers are production servers.
Turning off Lock Partitioning - what's the worst that can happen? - Hello, does anyone have experience of turning off lock partitioning? During online index rebuilds on our SQL Server 2016 (SP2 CU8) Availability Group, we are experiencing difficulties with an unpredictable blocking chain on the read-only secondary nodes, and I have established that this is due to Lock Partitioning. WAIT_AT_LOW_PRIORITY gives us a an excellent chance […]
SQL Server 2016 - Development and T-SQL
Query Tuning Question - Is query tuning the responsibilty of a Developer or is it a combined responsibility of DBA and SQL Developer.
DBA activities - The exam page fetches data from SQL Server. We are expecting 15K exams by this month end. What should i do so that databases  performance will be good and it doesnt crash
Administration - SQL Server 2014
Upgrade from 2008 R2 to 2014 Replication Error - We did an in-place upgrade and everything seems to be working fine except the single distribution to another (also 2014) server has a replication error on the snapshot agent. The error is "Invalid object name MSredirected_publishers" which is strange because no redirection has been set up and there is no such table in the distribution […]
Development - SQL Server 2014
SSIS is changing the hour part of datetime returned from a stored procedure - I have a an ssis package where I am calling a stored proc from. When I run the stored proc in ssms the value returned is '2020-03-06 16:41:47.040'. When I run the sproc in an execute sql task and set the result to an ssis variable of type datetime it changes the value to 3/6/2020 […]
SQL Server 2012 - T-SQL
How to update a Tables with randum Null parameters - Hi, I have a number of parameters (see below ) that come into a Stored procedure. In the application that this is called from the user may have only some of these (or maybe just one of these) being updated at a given call. Now I could do this with a series of IF statements […]
SQL Server 2019 - Administration
Credibility of TABLE_SCHEMA column in INFORMATION_SCHEMA objects - So, this is a problem that many of us have been aware of for some time. That, as per the documentation, TABLE_SCHEMA might be incorrect: ** Important ** Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of a object is to query the […]
SQL Server 2019 - Development
AS400 as Linked Server - I am getting this error when trying to do a select from the Linked server ((( Msg 7356, Level 16, State 1, Line 1 The OLE DB provider "IBMDASQL" for linked server "" supplied inconsistent metadata for a column. The column "" (compile-time ordinal 8) of object "" was reported to have a "DBTYPE" of […]
Adjusting CTE MAXRECURSION intelligently - We have a Stored Procedure that uses a recursive CTE and, from time to time, the Stored Procedure fails because the maximum recursion of 100 (the default) has been reached. We'd like to adjust this value this to ensure that: a.  The Stored Procedure never fails for that reason b. The MAXRECURSION value is set […]
Reporting Services
How to Pass more parameters to Custom dll (C#) SSRS - I am calling .net dll sample method from SSRS report, i need to pass 20 Parameters to that method. Could any body please let me know what is the best way to pass 20 Parameters from SSRS  to .net dll =Code.Query.Sample(Parameters!Param1.Value, ......................Parameters!Param20.Value)  
SSRS 2016
Converting parameters to fix performance problems - We recently upgraded to SQL Server 2017 from SQL Server 2012.  This includes a change from SSRS 2012 to Power BI Reporting Services 2017 (Sept 2019) and we are now having a weird parameter problem.  I am not sure if it is SSRS related or Power BI RS related or database related, and not sure […]
SQL Server 2005 General Discussion
Double PK values with leading zeros - Hi Because we need to read info from 3rd-party databases which uses dbf-files, we have a 32-bit SQL Server Express 2005. It is working for several years without problems, until somewhere in last month we have a problem with one table we read in. The table has string field which is indexed as an unique […]
 

 

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

 

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