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

Daily Coping Tip

Write down start your day with the most important thing on your to-do list

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from The Action for Happiness Coping Calendar. My items will be on my blog, feel free to share yours.

Code Supply Chain Security

There have been a number of attacks in the last few years on source code. In fact, I saw a new one this week for an e-commerce WordPress plugin. This time hackers got access to the distribution server for the company, Fishpig, and altered the plug-ins that their customers download.

A few years ago this was big news, with the SolarWinds exploit. There was also an attack on PyPy, a popular Python package that many people include in their code.  There have been no shortages of problems in npm packages as well. I'm sure this has happened in other software packages, which is scary. In the days of DevOps where we publish code from a repository, an exploit against your developers might go unnoticed. Then again, maybe not.

Would any of us notice new code in a file share or a folder on our system? We might just compile a large project without realizing it. At least with DevOps, we have the opportunity to include security scans and code analysis checks, some of which could look for known patterns of exploits. I know some companies use these, and often compromised or vulnerable packages are stopped by the automated pipelines.

In the US, various security agencies have released a set of recommendations, as has the Open Source Security Foundation. Both of these are designed to help developers secure their supply chain against attacks. This is likely going to be a continuous problem for software vendors in the future as it's much easier to attack one vendor whose software many people use than each individual company. I shudder to think about what happens if someone manages to get a ransomware package into a vendor's codebase.

Ultimately, there will still be problems. Many new projects begin with poor practices precisely because they're experiments and the authors don't know if others will find the software valuable.  While we can have good templates and security controls, I'm not hopeful. To me, the best solution for stopping code is to have patterns detectable by security checks in the pipeline. Checks that can be expanded and enhanced as new issues are determined.

Of course, that means the makes of security software need to ensure their supply chain is protected as well.

Steve Jones - SSC Editor

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

 
 Featured Contents
SQLServerCentral Article

Copy SSIS Environment variables to another server

Kunal Rathi from SQLServerCentral

Migrate SSIS environment variables from one SQL Server to another in just a few clicks using SSIS Catalog Migration Wizard.

External Article

The Basics of Inserting Data into a SQL Server Table

Additional Articles from SimpleTalk

Before data can be read from of a SQL Server database table, the table needs to contain rows of data. One of the most typical ways to get data into a table is to use the INSERT statement.

External Article

How to Apply Clustering in a Scatter Plot to Visualize Segments in Power BI Desktop

Additional Articles from MSSQLTips.com

Learn how to build scatter plots in Power BI and use the clustering option to automatically find clusters within the report data for easy visualization.

Blog Post

From the SQL Server Central Blogs - Storytelling with Data – Enertia User Conference 2022

DataOnWheels from DataOnWheels

Thank you to everyone who attended my session at the Enertia User Conference in Las Vegas earlier this month! It was a blast meeting everyone, and I can’t wait...

Blog Post

From the SQL Server Central Blogs - Query Tuning At PASS Data Community Summit

Grant Fritchey from The Scary DBA

The all new, in-person, PASS Data Community Summit is in just a few weeks. Since I’m shortly going to be publishing a 100% rewritten book on query tuning, I...

 

 Question of the Day

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

 

Checking Digital Signatures

I have some character data in a table that is signed by a certificate. The signature is also stored in a table. How can I check that the data hasn't changed?

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 Percentile Comparison

Both PERCENTILE_CONT and PERCENTILE_DISC produce a percentile value from a set of data. One interpolates the result and one picks a value from the dataset.

If I have this table:

CREATE TABLE PercentileCont
( PCID      INT NOT NULL IDENTITY(1, 1)
, Category  VARCHAR(20)
, SomeValue INT);
go

INSERT PercentileCont
  (SomeValue, Category)
VALUES
  (10, 'Finance')
, (20, 'Finance')
, (30, 'Finance')
, (40, 'Finance')
, (50, 'Finance')
, (60, 'Finance')
, (70, 'Finance')
, (80, 'Finance')
, (90, 'Finance')
, (100, 'Finance')
GO

What happens with this query?

SELECT
  category
, SomeValue
, PERCENTILE_CONT (.5) within GROUP(ORDER BY somevalue) OVER (partition BY category) EightyPercent_Cont
, PERCENTILE_DISC (.5) within GROUP(ORDER BY somevalue) OVER (partition BY category) EightyPercent_Disc
FROM PercentileCont
GO

Answer: The values for EightyPercent_Cont is larger than the value for EightyPercent_Disc

Explanation: The value returned for percentile_cont is 55 and for percentile_disc is 50. The calculation that finds the values for the 50th percentile for the CONT function will interpolate a value between the row rows above and below this. Those are the 50 and 60 values, which average out to 55. The DISC function picks an exact value. There is a good explanation of the formula behind this at: What Does Percentile Cont Do?

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
SQL Server Windows NT - 64 bit" in Task Manager: CPU high -   Hello ,   The SQL Server Windows NT - 64 bit" in Task Manager: CPU high  56,961 MB , the overall server CPU is now 95 % kindly advise the solution.
subscriber part of ALWAYSON - Hi. Publisher stop sending transaction to AOAG primary replica whenever AG failover.  It seems push publisher does not re-directed to subscription database while AG failover. Replication monitor found error messages as below. please suggest me how resolve this issues? Command attempted: if COLUMNPROPERTY( OBJECT_ID('MSreplication_subscriptions'),'distribution_agent','AllowsNull') <> 1 BEGIN alter table MSreplication_subscriptions alter column distribution_agent sysname null […]
SQL Server 2016 - Administration
Shrink database issue - In my database, I have few temp tables that total up to 50GB in size. I already removed the data from the table. When I set the shrinkdatabase in my maintenance plan, it runs successfully but doesn't shrink the database. I tried dbcc shrinkfile(1,truncateonly) and got the same result. However, from sql management studio when […]
SQL Server 2016 - Development and T-SQL
Grant user permissions to a view but the base tables live in another database - I have two databases on the same server.  I have a service account I want to have access to SELECT off some view in the "Staging" database.  The view calls on tables in our Finance database.  If I run the SELECT as myself or other "normal" users it works fine because those accounts have access […]
The ORDER BY clause is invalid in views - Hi, I'm trying to run the below SQL code but I'm getting this error: An error occurred while checking the query syntax. Errors: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. I'm still learning SQL so any […]
SQL 2012 - General
SQL Server Licensing Question - I'm in the process of replacing a sql server 2012 with 2019. How do CALs work if pieces of the database are publicly accessible? How do you purchase CALs for that? Or do you just only buy the core model in that case? We only have 2 actual employees who will be working with the […]
SQL Server 2019 - Administration
Change tracking - Date a row changed - We have change tracking enabled on one of our databases and I am looking for a query where I could get the timestamp/date on when a row changed.  Any help is greatly appreciated.  Thanks a lot.
Data replication tools and review - Hello expertise, Could you please suggest best tool for database replication? data replication on transactional basis and using for filtering option like DELETE transaction not replicate to target database due to data should maintain long term in target database instance. Thanks.    
SQL Server 2019 - Development
How to run a query multiple times with different values in date - I'm very new to the sql and am wondering what is the easiest way to run the same query multiple times with different dates (ideally is the last day of each month). Can I create a loop so that I don't need to manually change the value in Date? Values in date are 31Jan2022, 28Feb2022, […]
Migration of simple SPs from 120 to 150 COMPATIBILITY_LEVEL issue. - Migration of simple SPs from 120 to 150 COMPATIBILITY_LEVEL issue. Execution time for the first time run of any SP is close to 3 min. After the first execution, it takes ms to repeated execution. The execution plan is identical in 120 and 150. Recompile did not help. SPs are sitting on wait: PAGEIOLATCH_SH for […]
Referencing a SQLCMD variable in a Post Deployment script using SqlPackage - Ive got a Database project running in JetBrains Rider. The project publishes a DACPAC to create or update a target database. Now I have a set of reference data scripts which run (in Create mode) and these are reference through the Script.PostDeployment which looks like this: :r .\LoadDeviceConnectionType.sql :r .\LoadUser.sql :r .\LoadConversionType.sql :r .\LoadChannelType.sql :r […]
SQL Azure - Administration
encrypt the database in azure - How can we encrypt the database in Azure. Research story.   Thank you
Amazon AWS and other cloud vendors
Version Controlling Lambdas - I know there are lambda versions. But I want to know if this can be done using GitHub, can we integrate it with CodePipeline and create something like a centralised repo for all our Python lambdas and then update those functions with every push.
Reporting Services
SSRS Reporting - Anyone can give me a sample program on how to pass parameters from SSRS RDL Report to ASP.net Core or C#? I have a project with multiple users in multiple groups. The idea is for users to generate reports only from their group and they cannot generate reports from other groups. Like when a user […]
How to show currency symbol in column header - In our reports we display the currency ($) symbol in the column header for example Amount ($) or Points Value ($) etc. If we were to "globalize" our reports, how can we pick up the currency symbol from the Culture UI or the registry (for example S-1-5-18 Local system)? I tried this but it did […]
 

 

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

 

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