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

Securing Code Early

Last year I started to get alerts from Microsoft Repos that someone had put a piece of security information in their code that pertained to one of my Azure services. At first I was worried, but then I realized this was the public version of AdventureWorks we maintain in Azure. We’ve published the login so people can test code against this if they want, and I started ignoring the warnings. Well, not ignoring. I still glance over them to verify the issue, but I’m less concerned.
 
That doesn’t mean that you shouldn’t be concerned about sensitive information in repos. I saw this quote: ” Bots are crawling all over GitHub seeking secret keys, a developer served with a $2,375 Bitcoin mining bill found.” This follows a sentence that says “It once caused Uber to leak the contact details of 75m users“. These are from an interesting look at a way to secure code that might leak API keys. The idea is that you secure code on local commits and prevent secrets from being stored in your VCS.
 
That’s a great idea. Can we prevent passwords in SQL code or ASP.NET config files? Can we actually start to teach developers to use secrets and other run-time mechanisms and prevent them from hard coding anything into a VCS? Perhaps, but we have a lot way to go and certainly more tool chains need to be updated to prevent what is a simple, but common, mistake.
 
We need to get better at security, and we are. I see more Static Code Analysis tools being used in all sorts of companies, and I’m glad when they start to impact developers. While I get that changing your favorite method of writing a query or procedure is a pain, often we can reduce potential problems by enforcing some standards and avoiding poor coding practices. We do need to have exceptions since a rule for code might really be a general guideline 99% of the time with a few edge cases.
 
Catching issues early in development is one of the goals. Having things like inline SCA in SQL Prompt (live demo), or continuous testing in Visual Studio, scanning with Sonarqube, and other tools are improving our code, while allowing us to build applications faster. There is plenty of other work to be done, especially for database code, but we are improving as an industry.
 
If you haven’t used any of these tools and you write code, start learning a bit about them. You’ll likely appreciate their benefits once you get over the learning curve. You’ll also start writing better code.

Steve Jones - SSC Editor

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

 
  Featured Contents

Canine Corgi Completion Contest

Steve Jones - SSC Editor from SQLServerCentral

Steve is putting on a new contest for you with some fun prizes. Put on your creative hat and submit an entry. Contest ends April 21, 2019.

Stairway to AlwaysOn

Stairway to AlwaysOn Level 6: Analyse and Deploy an AlwaysOn Availability Group

Perry Whittle from SQLServerCentral.com

In level 6 of this stairway, we will look deeper into Availability Groups and how to set them up along with a Listener.

Configuring Azure SQL Databases Using Azure Resource Manager Templates

Additional Articles from Database Journal

Template-based provisioning simplifies deployment and promotes principles of DevOps and Infrastructure as Code, making it the recommended method for implementing cloud-based services. However, its benefits extend beyond initial implementation, since you can apply it to configuring and maintaining existing Azure SQL Database instances. In this article, you explore an example of this approach.

Monitoring SQL Server Performance: What’s Required?

Additional Articles from Redgate

A monitoring tool must provide us with an understanding of the often-complex performance patterns that databases exhibit when under load, so that we can predict how they will cope with expansion or increase in scale. It must also helps us spot the symptoms of stress and act before they become problems that affect the service, and understand better what was happening within a database when an intermittent problem started.

Automating Builds from Source Control for the WideWorldImporters Database

Additional Articles from Redgate

Kendra Little shows how to get the WideWorldImporters database into version control, using SQL Source Control, and then set up an automated database build process, using Azure DevOps with SQL Change Automation.

Free eBook: SQL Server Transaction Log Management

Press Release from Redgate

When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA’s reputation depends on a deeper understanding of the transaction log, both what it does, and how it works.

From the SQL Server Central Blogs - SQL Homework – April 2019 – Practice dealing with corruption

Kenneth.Fisher from SQLServerCentral

Over the last almost 2 years (wow how time flys when you are having fun) we’ve practiced all kinds of … Continue reading

From the SQL Server Central Blogs - What’s a Key Lookup?

SQLEspresso from SQLServerCentral

One of the easiest things to fix when performance tuning queries are Key Lookups or RID Lookups. The key lookup operator occurs when the query optimizer performs an index…

 

  Question of the Day

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

 

Getting the next value

I have written this function in Python:
def getn(n):
    val = 0
    while val < n:
        yield val
        val += 1
If I call this:
x = getn(100)
I do not get a result. How do I get the value from my function?

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

 

Redgate SQL Source Control
 

 

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

Renaming a login

I am concerned about security on my SQL Server 2017 instance. One of the things I've heard  is that I should not use the "sa" login, and should both disable and rename it. How can I rename my sa login?

Answer: ALTER LOGIN [sa] WITH NAME = [Thanos];

Explanation: You can rename SA and the ALTER LOGIN command is used with the NAME option. You do not use quotes here, but use brackets or just the name. Ref: ALTER LOGIN - https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-login-transact-sql?view=sql-server-2017

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.


Very slow, giving bad gateway errors and QOTD show as unanswered when they are - Coming to the new site for the first time today it is extremely slow for me and clicking on the QOTD section keeps giving a Bad Gateway Error and when I can see the questions, ones I have answered say Answer this but when I click on them it says they are already answered – […]
Using CTE in Scalar Function - I am trying to use a CTE (Common Table Expression) within a ScalarFunction . Am I able to do this. I am get several red squiggly line errors right now.  (“Incorrect syntax near Declare” ,”Invalid column name DateEffective”,”Invalid Object Name inceptionCTE”, “Invalid column name PriorPolicy”) Is there a way to do this and make SQL […]
Random DateTime Range - I understand my topic title is pretty generic, but what I cant seem to find on the internet is how to generate a small table of random date and time values for the year 2019. An example is 2019-01-01 14:01:00.0000000 2019-01-02 14:01:00.0000000 2019-03-01 06:13:48.0000000 2019-04-01 05:47:40.0000000 2019-04-13 04:06:51.0000000 2019-04-15 03:23:28.0000000 I am sure it is […]
Create Key with distinct values - Hi everyone, I currently have two tables: A. Entity Name, Entity EIN, Amount B. Fiscal Year, Entity Name, Entity EIN I want to create a foreign key that reference A to B (i.e. B as control table) using Entity Name and Entity EIN only. However, I couldn’t create a primary key with Name and EIN […]
Need to extend length of PK - I have inherited a database, and have found a bit of a flaw that is preventing me from updating it.  Let me first start by saying I know a little about SQL, but mainly from access, and a few searches here and there. The problem is that the primary key (for office codes) was set […]
Always on – Entered Synchronised\Suspect - Hi all, My AG database on the primary has just suddenly gone in to a synchronised\suspect state and is currently in accessible. I’ve checked disk space and that’s all good. Any idea’s on how I can resolve this? Thanks, Nic
New Topic test - Looking for this to apeear in various feeds.
Issues/fixes I would like to see -  You can’t link your account to multiple emails.  An issue I brought up many times.  I don’t want to associate this site with my work email, just in case something happens to my job.  And I don’t want to be checking my home email while at work. No calendar with links to the daily newsletters.  […]
Estimated Number of Rows 4,056,100,000,000,000,000,000,000 ! - I inherited a long convoluted query that rebuilds a summary table every morning. It recently started taking much longer than usual. Looking at the execution plan, there is a string of “Nested Loops” and “Compute Scalar” and the estimated # of rows shows crazy increases with each one. There have not been any code changes […]
Article discussion link point to Data Compression Double Take - The discussion link in today’s Corgi Completion Contest article is pointing at the Data Compression Double Take article instead of the discussion for the CCC article.
Dimension [Fact Internet Sales] : Create hierarchies in non-parent chi - Hi, Please Assist? I am trying to deploy a cube and it gives me a few errors. How do i correct? I am using the AdventureworksDW2012.            
Database suspect mode - Database suspect mode but i can’t  remove mirror or set emergency mode now, due to database configured mirroring. How can we remove mirror in principal server? pl. suggest Thanks
Create table with synonyms - Hi all   I’ve having a bit of a tidy up of our functions as we have the same function all over the place (same name/code/etc.).   The function (for grabbing the name of any UK bank holidays) is part of our DIM_Date table.   The function is called “fn_Get_UK_Holiday” and that appears in several […]
Internal Query Processor Error: The query processor could not produce a query .. - Hi all, I have hit a problem in SQL server 2016 and I don’t have access to 2017 to see if the same problem occurs there to. If someone would be so kind as to try the below query in a SQL 2017 environment and report back here I would be very grateful. In SQL […]
2016 CU and SU - Hi, Does SQL 2016 CU4 and up contain the previously released SUs (4293802,4458621)?? Thanks Claude
 

 

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

 

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