Tom Collins answers a question:
How can I obtain a list of BUILTIN\Administrators members using Powershell ?
Read on for the answer and three separate examples.
Comments closedCategory: Security
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 closedBruno 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 closedJeff 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 closedBill Fellows hides SSIS DNA in a can of Barbasol shave cream:
The Azure Feature Pack for SSIS is something I had not worked with before today. I have a client that wants to use the Flexible File Task/Flexible File Source/Flexible File Destination but they were having issues. The Flexible File tools allow you to work with Azure Blob storage. We were dealing with ADLS Gen2 but the feature pack can work with classic blob storage as well. In my hubris, I said no problem, I know SSIS. Dear reader, I did not know as much as I thought I did…
Click through for a whopper of a story. But be sure to read to the very end, as you don’t want to stop at using TLS 1.0.
Comments closedI troubleshoot an unfortunate error message:
Based on this error message, the command is timing out after one minute. My initial thought is that there must be a network restriction preventing me from communicating with Key Vault, and I know what my first answer is.
This story even has a red herring, which means it’s a set of junior sleuths and a talking dog away from being a ’90s cartoon.
Comments closedI have a post borne from frustration:
I have an Azure Synapse Analytics workspace which uses a managed virtual network and includes data exfiltration protection. I also have a Spark pool. My goal is to import a few packages and use them in a Spark notebook.
Doing so is pretty easy from the Synapse workspace. I navigate to the Manage hub and then choose Apache Spark pools from the Analytics pools menu. Select the ellipsis for my Spark pool and then choose Packages.
From there, because I plan to update Python packages, I can upload a requirements.txt file and have Pip do its job.
But then it doesn’t… Click through to learn why, as well as the workaround for this. It’s stuff like this which makes me say data exfiltration protection is a feature administrators will (mostly) like and developers will hate. Especially because there’s no obvious indicator why this was happening in the error message itself.
Comments closedCatalin Cimpanu reports on a security problem:
Microsoft has notified earlier this month a select group of Azure customers impacted by a recently discovered bug that exposed the source code of their Azure web apps since at least September 2017.
The vulnerability was discovered by cloud security firm Wiz and reported to Microsoft in September. The issue was fixed in November, and Microsoft has spent the last few weeks investigating how many customers were impacted.
Click through for the full report, with the upshot that if you run Azure App Services on Linux, you were probably affected.
Comments closedAndreas Wolter has some guidance for us:
Microsoft published guidance regarding Log4j 2 vulnerability for customers using Azure Data services. Please find the latest information here:
Microsoft’s Response to CVE-2021-44228 Apache Log4j 2 – Microsoft Security Response Center
The published list shows affected products only.
For SQL Server, even components which use log4j, the version is old enough that it is not affected by the series of exploits, bugs, and exploits of bugs which were introduced to try to fix the prior round of exploited bugs. The big exception is Big Data Clusters and if you happened to install log4j on your own.
Comments closedDavid Alcock wants to move a login:
Migrating SQL databases is fun, depending on your definition of fun that is. The process can involve having to move things such as login details that have been around for that long that nobody has a clue what they are anymore.
With domain accounts that’s pretty straightforward, the passwords are managed in Active Directory and not held in SQL Server and it’s just a case of recreating the account on the new instance.
SQL authentication is different and migrating an account as is means you also have to recreate the password as is which could be difficult if you didn’t know what the password should be. It’s worth saying at this point that the preference should always be to use domain accounts, they’re more secure and much more manageable and migrations are ideal opportunities to refactor things to be better but for the sake of this article let’s proceed with the scenario of recreating a SQL authentication login with an unknown password, and we’ll need to get creative.
And as you’d expect, dbatools makes a dramatic appearance.
Comments closed