A Fine Slice Of SQL Server
You should never embed passwords or other “secrets” – sensitive data – in code. A better way is to put sensitive data into configuration, and load configuration from your code. Read on to find out how to do this in R Markdown (and Shiny).
Click through for one way to do this. Just make sure you .gitignore excluded .env files.
Aaron Bertrand provides a warning around dynamic SQL:
For October’s T-SQL Tuesday, Steve Jones asks us to talk about ways we’ve used dynamic SQL to solve problems. Dynamic SQL gets a bad rap simply because, like a lot of tools, it can be abused. It doesn’t help that a lot of code samples out there show that “good enough” doesn’t meet the bar most of us have, especially in terms of security.
In a series I started last year, I talked about ways to do <X> to every <Y> inside a database, focusing on the example of refreshing every view (in a single database or across all databases). I already touched on what I want to dig into today: that it can be dangerous to try to parameterize things that can’t be parameterized in the ways people typically try.
Read the whole thing. I do find it funny how often people aren’t allowed to install well-known, third-party stored procedures (like Aaron’s sp_ineachdb) but it’s perfectly okay to write terrible code which is vulnerable to exploit because it was written in-house and is therefore more trustworthy somehow.
I don’t want to dunk on security teams too much in this regard, as I understand and really do appreciate the principle, though it often has counterintuitive first- and second-order consequences.
Comments closedArshad Ali wants to secure some data being used in a Synapse Spark pool:
As a data engineer, we often get requirements to encrypt, decrypt, mask, or anonymize certain columns of data in files sitting in the data lake when preparing and transforming data with Apache Spark. The extensibility feature of Spark allows us to leverage a library which is not native to Spark. One such library is Microsoft Presidio, which provides fast identification and anonymization modules for private entities in text such as credit card numbers, names, locations, social security numbers, bitcoin wallets, US phone numbers, financial data, and more. It facilitates both fully automated and semi-automated PII (Personal Identifiable Information) de-identification and anonymization flows on multiple platforms.
In this blog post, I am going to demonstrate step by step how to download and use this library to meet the above requirements with Spark pool of Azure Synapse Analytics.
Read on to see how it works.
Comments closedSean Gallardy has an A+++ 10/10 would read again rant:
I get asked if I know anything about <newest SQL vulnerability as reported by random website>, quite often. Generally, my answer is that I don’t for two main reasons… the first being that none of them are actual vulnerabilities, and the second being that none of them are particularly new but merely items from the same bag of tricks everyone else uses and isn’t a buffer overrun/privilege escalation/etc. item. My normal response after taking a quite peek at whatever article is referenced is generally the same response as The Dude, “Yeah, well, you know, that’s just like uh, your opinion, man.”, as all of these items are purported to be vulnerabilities but yet none actually exploit any vulnerability.
Did you know that if you steal someone’s username and password from the sticky note on their monitor, you can use that to connect to a SQL Server? Amazing vulnerability there—it doesn’t even check that you’re the real person who should have those credentials!
Comments closedKenneth Fisher has access to many permissions:
What you have access to is not just what you have direct permissions to. The other day I needed to copy some backups from one location to another. Unfortunately my network id doesn’t have access to either location. Guess what does though. The service account running the SQL Server instance where the backups were taken. Now, since I’m a sysadmin on that instance when I use xp_cmdshell it uses that service account. I don’t have to know the password or log in as the service account, xp_cmdshell will do it for me.
Click through to learn more.
Comments closed– Enable AAD auth on Azure SQL server
– Conn to Azure SQL database via AAD admin
– Create contained user for the managed identity (using Azure VM name as contained username)
create user <vmname> from external provider;
alter role db_owner add member <vmname>;
Read on for the full set of steps.
Comments closedAnas Baig provides a primer on the California Consumer Privacy Act:
While the GDPR has some of the most crushing requirements, such as the brief 12-hour window to report a breach, CCPA takes a broader view. It goes even further to locate and protect what constitutes personal or private data as per the GDPR.
The primary difference between the two is that GDPR is more about prior consent, while CCPA is about opting out. GDPR binds businesses to ask for consent before having a consumer’s data stored and processed. On the other hand, CCPA requires businesses to enable consumers to opt-out at any point.
It allows consumers to access every piece of personal information saved and a complete list of third parties it is shared with. Moreover, consumers have the right to sue a company for violating privacy guidelines even if there has been no breach.
Considering how much we heard about GDPR, I’m surprised there’s been so little about CCPA.
Comments closedDebaditya Bhattacharyya reviews the impact of a Kafka security advisory:
The Apache Kafka® project announced on September 19, 2022 that a security vulnerability has been identified in Apache Kafka, CVE-2022-34917. After being informed of this, Instaclustr began investigating its potential impact on customers of our Apache Kafka offering. This vulnerability allows malicious, unauthenticated clients to allocate large amounts of memory on the brokers. This can lead to OutOfMemoryException in the brokers causing denial of service.
Read on to learn more about the impact and techniques for mitigation.
Comments closedGilbert Quevauvilliers plays to the audience:
With the recent announcement (Announcing Public Preview of Multiple Audiences for Power BI Apps | Microsoft Power BI Blog | Microsoft Power BI) it is now possible to create multiple audiences in a single App Workspace.
What this means you can now have a single app workspace but create a view for specific users (each view is known as an Audience)
This blog post will detail how to manage multiple audiences with AAD Security Groups, where the only requirement will be to update the app when new reports get created and to which audience to make them available.
There’s also a big warning on Gilbert’s post that you will not want to miss.
Comments closedKenneth Fisher ran into an interesting issue:
One of my co-workers came to me the other day and told me that they found their network id as a login on one of our SQL Server instances. Why is that note worthy? We make a point of using AD groups if at all possible. He did a little bit more work and tracked it down to a series of commands that he had run recently. I’ll be honest with you, this was starting to feel like a couple of other cases where I’ve seen database objects and principals show up unexpectedly. One was when I created a database object without setting a default schema, and the other was when I created a database audit specification to audit a user that didn’t have an associated database principal (access was through an AD group).
This time around, it was creating a server role. Read on for Kenneth’s thoughts and how you can avoid accidentally creating logins for individual Active Directory users.
Comments closed