Press "Enter" to skip to content

Category: Security

Generating an Email List from Active Directory Users

James Livingston takes us through an interesting solution to a common problem:

If you’ve ever performed some impactful maintenance on a SQL Server, you probably notified users. If you’re great at documentation and already know exactly who to contact, this script isn’t for you. If you don’t have a user email list, this script will create it for you!

I used to manage 500 SQL Server instances and there was daily maintenance\changes going on constantly. I wrote this PowerShell script to automatically create an email list for me. This PowerShell script gathers the login information from an instance of SQL Server and then pulls their email address from Active Directory.

Read on to see the script in action.

Comments closed

On Self-Signed Certificates

Sean Gallardy isn’t too concerned about self-signed certificates in SQL Server:

This happens at startup and is completely transparent to both end users and administrators alike, apparently not so much to your security team utilizing the latest and greatest security tools available (yes, that’s sarcasm). One day you may get a notice that “SQL Server is using a self-signed certificate (issue #1) and that the key length/algorithm isn’t what our internal security team allows (issue #2). You must fix asap or be out of compliance!” Oh no, the dreaded compliance threat.

Sean is…not impressed with these issues and explains why.

Comments closed

Fun With Secure Enclaves

Ned Otter continues a series on SQL Server 2019 Always Encrypted with Secure Enclaves:

In the first post of this series, we explored the requirements for using Always Encrypted with secure enclaves, as well as some of the limitations.

For this post, we’ll be using Powershell to install and configure the HGS server (required for “attestation”) as well as executing the steps required to configure the SQL 2019 server to work with HGS.

Read on for a few disclaimers and a detailed setup article.

Comments closed

SQL Server 2017 and Column-Level Encryption

Steve Jones notes a change between SQL Server 2016 and SQL Server 2017 around column-level encryption:

I discovered recently that there was a change made in SQL Server 2017 to the way that symmetric key passphrases are hashed. There’s a KB article that notes the fix, but basically the passphrases used to be encrypted with SHA1. That’s cryptographically insecure, so the algorithm was updated to SHA2.

This is a problem, and can cause some issues. I’ll show the issue and then how to get around it.

There’s not much detail in the KB article about what happens afterward: if you need to keep the trace flag on forever or if it eventually migrates everything over to using SHA2 for hashes.

Comments closed

Changing Synapse Analytics Resource Classes with Azure Automation

John McCormack wins today’s edition of Cloud Bingo:

I was a approached by a data warehouse developer who wanted to be able to take advantage of the feature in Azure Synapse Analytics (Formerly Azure SQL Data Warehouse) called ‘Resource classes’. Resource classes allow users in certain roles to have access to extra compute resources for their queries. This can increase their max memory per query (although there is an overall trade-off on concurrency). Unfortunately, due to this being managed via roles, the person looking to change resource classes needs to be in the db_owner role so they can manage database role membership. As I didn’t want to add the developer to the db_owner role, a different solution was required.

John gives us a nice answer to this problem. Click through for the script.

Comments closed

Linked Server Error 7314

Reitse Eskens troubleshoots a linked server error:

This morning started with a bit of a nuisance. I got a message from one of our customers that a night job failed with an error.

First thing i checked after logging in was if the database and table exist on the server. Because the error states that de table doesn’t exist on the database or that there are insufficient rights. The table exists and the user to connect to the database has the rights to do so.

That didn’t do the job, so read on to see what the cause and solution were.

Comments closed

Always Encrypted with Secure Enclaves

Ned Otter has started a new series on Always Encrypted with Secure Enclaves in SQL Server 2019:

SQL 2019 supports an enhanced version of Always Encrypted, known as “Secure Enclaves”. What is an enclave? It’s like a consulate: “….a state that is enclosed within the territory of another state”.

It takes the form of a protected region of memory within the SQL Server environment, requiring special credentials for access. Data in the secure enclave lives in an unencrypted state.

However, as I’ll discuss later in this series, depending on how your organization implements Always Encrypted with Secure Enclaves, it might not be as secure as you had hoped.

That’s pretty ominous. The first part is a fairly high-level overview which gets you familiar with enclaves.

Comments closed

TDE Encryption Scan Internals

On the Microsoft Tech Community blog, goramesh shares with us how the initial encryption process works for Transparent Data Encryption:

Now, once encryption is turned ON for a database, all the existing user data on the data files should be encrypted. To do this, SQL Server starts something called a TDE Encryption Scan. It is basically a scanner, which goes through each page of each data file to ensure its encrypted. When the scanner completes its scan across all the files, that’s when we say that the database is ‘encrypted’. How the TDE Encryption scan works is crucial because of the effects it can have on the user workload. Let me explain. 

Read on for the explanation.

Comments closed

Changes to EC2 Metadata Service

Praveen Sripati takes a look at changes to the AWS EC2 Instance Metadata Service following attacks against Capital One and dozens of other organizations:

Captial One Bank (1) and 30 different organizations were hacked around end of July, I have written a blog (1) around the same time on how to recreate the hack in your own AWS account and also a few mitigations around the same. Now, AWS has made a few changes to the AWS EC2 Instance Metadata Service (IMDS) around the same (12). AWS re:Invent 2019 session (1) around the same has also been planned on December 5th, 2019. Will update with the link once the recording of the session has been uploaded.

The old/existing approach is called IMDSv1 and the new one IMDSv2. Although IMDSv1 solves a few problems like not storing the access keys on the EC2, it bought its own headaches which lead to the hacks.

Click through to see what these problems were and how they led to IMDSv2.

Comments closed

Visual Tools and Dimension Security Slowdown in SSAS

Chris Webb hits an interesting edge case with SQL Server Analysis Services Multidimensional:

Recently I was involved in troubleshooting a mysterious Analysis Services Multidimensional performance problem for a customer: the team worked out that certain queries run by certain users were extremely slow, and that these users were members of roles where dimension security was applied, but the amount of slowdown – queries going through the role were taking over 10 minutes compared to a few seconds when run as an administrator – was unlike anything I had seen before. It turned out that the cause was having the Enable Visual Totals box checked on every attribute on the dimension where security was applied, not just the attributes whose members were secured.

Read on for a reenactment of the problem.

Comments closed