Press "Enter" to skip to content

Category: Security

Determining the xp_cmdshell User

Published 2021-05-26 by Kevin Feasel

Kenneth Fisher asks the important Stockdale questions (Who am I? What am I doing here?):

It works when I run it this way™ but not when I run it through xp_cmdshell!

It’s a permissions issue. When you run xp_cmdshell you are running under the ??? account.

Little bit more detail. The extended stored procedure xp_cmdshell creates a windows command shell. This shell has to be run under a windows/active directory account. Obviously you can’t get access to a windows resource (a directory for example) using a SQL Server login. The trick is to be able to tell them what account xp_cmdshell is using within that shell. There are two possibilities here.

Read on to learn about those two possibilities.

Comments closed

Testing TLS Protocol Versions with cURL

Published 2021-05-25 by Kevin Feasel

Anthony Nocentino has a tip for us:

Ever need to set your web server a specific protocol version of TLS for web servers and need a quick way to test that out to confirm? Let’s check out how to use curl to go just that.

This code here uses curl with the parameters --tlsv1.1 --tls-max 1.1, which will force the max TLS protocol version to 1.1. Using the --verbose parameter gives you the ability to see the TLS handshake and get the output sent to standard out.

Also, check the comments for a very helpful addendum.

I should note that cURL is built into Windows 10 as of v1803, and it’s been a part of MacOS and Linux for a long, long time.

Comments closed

Column-Level Encryption in SQL Server

Published 2021-05-18 by Kevin Feasel

David Fowler takes us through a venerable (here by which I mostly just mean “old”) technique for encryption in SQL Server:

SQL Server gives us a few different options when it comes to encryption and I’m going to take a look at each of them at some point in this series but in this first post in the series I want to look at column level encryption.

Before we can even start thinking about encrypting our data, there are a few things that we’re going to need to set up first.

Although I joke about column-level encryption, David shows us just how easy it is to implement. It’s quite useful if you have just one or two columns in the database which need to be encrypted at rest and you don’t want to (or can’t) have the application handle it directly.

Comments closed

Building a Better sp_help_revlogin

Published 2021-05-11 by Kevin Feasel

Eitan Blumin remembers:

Anyways, with that obvious answer out of the way, let’s talk about something more interesting, like sp_help_revlogin.

Remember sp_help_revlogin? It’s that stored procedure that Microsoft published more than 20 years ago, that never found its way into the SQL Server built-in system procedures. Microsoft still maintains that same KB page till this day (by “maintains”, I mean copy-and-pasting it from one place to another as they change their KB platforms).

Read on to understand what this is and several ways of doing the same thing better, including a new sp_help_revlogin2 that Eitan has put together.

Comments closed

Registering an SPN for SQL Server

Published 2021-05-11 by Kevin Feasel

Jack Vamvas explains how to register an SPN for SQL Server:

I received a question from a colleague asking how to register a Service Principal Name (SPN) for SQL Server. The specific situation relates to upgrades and new SQL Server instances deployed onto new servers\server names . 

Defining an SPN results in an Active Directory name – allowing a client to uniquely identify  the service instance.  The SPN is comprised of a service name with a computer and user account resulting in a Service id. 

Read on for the process.

Comments closed

Securing Amazon Managed Streaming for Kafka

Published 2021-05-07 by Kevin Feasel

Stephane Maarek has some security advice for us:

AWS launched IAM Access Control for Amazon MSK, which is a security option offered at no additional cost that simplifies cluster authentication and Apache Kafka API authorization using AWS Identity and Access Management (IAM) roles or user policies to control access. This eliminates the need for administrators to run an unfamiliar system to control access to Apache Kafka on Amazon MSK, and learn intricate details and specific commands to manage Apache Kafka access control lists (ACLs).

This is a game-changer from a security perspective for AWS customers who use Apache Kafka: I recommend Amazon MSK customers use IAM Access Control unless they have a specific need for using mutual TLS or SASL/SCRAM authN/Z.

Read on to see how it works.

Comments closed

Availability Groups and Logins

Published 2021-05-04 by Kevin Feasel

Andrea Allred runs into a post-failover issue:

While doing a planned Availability Group failover, the application stopped talking to the database. After checking the SQL Server log, we found that all the SQL Logins were failing with an “incorrect password” error. The logins were on the server, the users were in the databases, and the passwords were even right, so what was wrong? It all comes down to SID’s (Security Identifiers).

Read on for the cause and the solution. I’d also recommend Sync-DbaAvailabilityGroup as a good dbatools cmdlet to use.

Comments closed

User-Defined Roles in SQL Server

Published 2021-04-30 by Kevin Feasel

Greg Larsen shows us how to create and use user-defined security roles in SQL Server:

When developing an application that accesses SQL Server, you need to set up a security model, so each user gets the access they need based on their job duties. Some applications require the same access for all users, while other application might require different security access rights based on the role a user has in the organization. The best practice for providing user access is to use role-based security. SQL Server provides user-defined Server and Database roles for defining security rules for different user security needs. This article will discuss how to use user-defined server and database roles to provide secure access to SQL Server resources.

This is something I think DBAs tend to under-utilize. Granted, a big part of why I think DBAs under-utilize it is that we often have Windows groups that we can use as the base for roles, but even so, it’s a good way to secure assets in the database.

Comments closed