Press "Enter" to skip to content

Category: Security

Using SQL Server Vulnerability Assessments

Published 2022-01-31 by Kevin Feasel

Manvendra Sing takes a look at SQL Server’s vulnerability assessment tool:

I will explain how to use and perform security testing using SQL Server vulnerability assessment in this article. I have explained basic understanding about multiple layers of security that we configure to protect our SQL Server instances in my last article. I have also explained about security testing at each layer we should perform to understand how secure our systems are. I would request you to read this article, Understanding security testing for SQL Server environments to learn more about them.

Security is a very critical area for any database environment. Database security measures help an organization to protect its data to maintain its privacy and integrity. Security testing should be done regularly to ensure all security policies are properly configured to protect the systems.

SQL Server also offers various features using which we can perform security testing for our databases. One of such features is SQL Server vulnerability assessment. This feature scans the database for which you are running it and displays all weaknesses along with their probable solutions.

Read on to see how you can run a vulnerability assessment, the types of results you can get, and how you might detail with some common issues.

Comments closed

Implementing Homomorphic Encryption with SEAL

Published 2022-01-28 by Kevin Feasel

Tsuyoshi Matsuzaki has a tutorial on using Microsoft SEAL:

Microsoft SEAL is a homomorphic encryption (HE) library, developed by Microsoft Research.

With homomorphic encryption (HE), the encrypted item can be used on computation without decryption. For sensitive data (such as, privacy data in healthcare), the customers can operate their own data without submitting private text to cloud service providers. (See below.)

Click through to see how it all works. Homomorphic encryption is a clever solution to an important class of data security problems and I’m happy to see walkthroughs like this be available.

Comments closed

Row-Level Security and Parallelism

Published 2022-01-28 by Kevin Feasel

Jose Manuel Jurado Diaz hits on an issue with row-level security:

Today, I worked on a service request that our customer reported that running a complex query this is executing in parallel but having more than 2 vCores in Azure SQL Database this query is not using parallelism.

During the troubleshooting process we suggested multiple tips and tricks, but any of them made that Azure SQL Engine uses parallelism: 

Being on-premises versus in Azure turned out to be a red herring and the solution was something maybe even more difficult to spot than triggers.

Comments closed

Checking a SQL Server Database’s Master Key

Published 2022-01-27 by Kevin Feasel

Steve Jones is trying to find where he left his keys:

I’ve been working with encryption in SQL Server for a long time, and have delivered quite a few presentations on the topic. Recently I was updating some code and wanted to check if a database had a master key created in it. This post shows how to do that.

Click through to see how you can create a database master key as well as how to drop it if necessary.

Comments closed

Combining Transparent Data Encryption and Instant File Initialization

Published 2022-01-27 by Kevin Feasel

Tom Collins performs a test:

Instant File Initialization (IFI) enabled  on  SQL Server, for data files only, leads to faster  execution on  certain file operations, as  it bypasses the zeroing procedure while reclaiming disk space. Instead, disk content is overwritten as new data is written to the files.

But do these benefits continue  when Transparent Data Encryption (TDE) is enabled ?   

Click through for the answer.

Comments closed

CIS Security Checks with dbachecks

Published 2022-01-21 by Kevin Feasel

Tracy Boggiano shows how to perform a security check based on CIS requirements:

Well back at the end of 2019 I finished writing most of the checks related to the CIS Center for Internet Security requirements.  I have yet to write a blog post on how to use them.  So, well here is how to go about using them, it’s mostly code so should be pretty simple to implement.  I’ve mentioned this several times over the past year in presenting on dbatools.

So first you need to have dbachecks.  So, let’s start with the basics just in case you haven’t heard of dbachecks.  dbachecks is PowerShell module that checks the configuration of your SQL Server against various test have been predefined.  By default, it exports the data to JSON, and we will be opening PowerBI to display the data because why that is pretty.  So, go download you a copy of Power BI from the Microsoft website and let’s install dbachecks first.

Read on to see what you need, the steps for this process, and what the results look like.

Comments closed

Logins and Users in SQL Server

Published 2022-01-17 by Kevin Feasel

Lee Markum disambiguates two security terms:

You’re a data professional learning about managing SQL Server and you’ve been asked to grant permissions for SQL Server to an individual or a group of individuals. What do you need to understand in order to accomplish this? I’ll be your guide to getting started with handling access to SQL Server.

Click through for the mandatory automobile analogy and a good way of laying out what logins and users are.

Comments closed

Capturing SQL Server Audit Events with Azure Monitor

Published 2022-01-14 by Kevin Feasel

Bruno Gabrielli connects Azure Montor to SQL Server Audit:

Today I am going to cover an interesting aspect on how to capture security audit events from both Azure and non-Azure SQL Server machines. Most of you probably know that SQL Server is capable of auditing security related information, such as access to a given database, record creation or deletion, configuration change and so on) according to the Audit configuration applied to a given instance or database.

In this post, we will not dig into SQL Server Audit configuration or capability. We will rather explore the steps and configurations necessary to collect data using Azure Monitor.

Read on for the process. You will need the appropriate agent for this, but that agent doesn’t necessitate that your machine be in Azure.

Comments closed

Permission Requirements for Temp Tables

Published 2022-01-07 by Kevin Feasel

Jeff Iannucci looks at permissions:

Managing permissions is a constant issue for Database Administrators, but rarely do DBAs consider permissions for tempdb. Everybody’s looking for something, but how often do you get requests for “access to read and write in the tempdb database”? Like…never?

OK, but what if you were asked the subject of this post in a job interview? Even if you’ve worked with SQL Server for ages, would you know how to answer this? Moreover, would you know why the answer should give you some concern?

Read on for the answers.

Comments closed