A Fine Slice Of SQL Server
Max Trinidad can keep a secret:
Finally, I came up with a practical example using the Powershell Secret Management module for storing SQL credentials. This is an excellent way of keeping your SQL connection strings information out of your scripting code. This way we just have it stored in our Vault.
Read on for a walkthrough and demonstration.
Comments closedSQL 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 closedAnyways, 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.
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 closedStephane 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 closedAndrea 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.
Jack Vamvas demystifies a key element of Kerberos-based authentication:
Could you give me a brief step through the process of the Service Principal Name (SPN) and the role as part of Kerberos based authentication to SQL Server?
Read on for the answer.
Comments closedGreg 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 closedArun Kumar Natva takes us through the process of encrypting data at rest in Cloudera Data Platform:
HDFS Encryption delivers transparent end-to-end encryption of data at rest and is an integral part of HDFS. End to end encryption means that the data is only encrypted and decrypted by the client. In other words, data remains encrypted until it reaches the HDFS client.
Each HDFS file is encrypted using an encryption key. To prevent the management of these keys (which can run in the millions) from becoming a performance bottleneck, the encryption key itself is stored in the file metadata. To add another layer of security, the file encryption key is stored in encrypted form, using another “encryption zone key”.
Read on to learn more and to see how it all fits together.
Comments closedMike Wood takes us through service endpoints in Azure:
In previous installments of my “Securing Azure SQL Database” series, I covered Azure SQL Database firewall rules and private endpoints—the first of which is a way to help reduce the public exposure of your database endpoint and the second being a means to remove all public access if necessary. Each option has unique benefits, and some scenarios might call for a mix of the two options.
In this blog post, I’ll cover a third option for securing Azure SQL Database—service endpoints. This option is similar to private endpoints in that you restrict public access and only grant access to the database through your Virtual Network (VNet).
Read on to learn more.
Comments closed