A Fine Slice Of SQL Server
I have a post on Azure Bastion:
Azure Bastion is a service which acts as a managed RDP or SSH host, allowing you to use a web browser securely to connect to a virtual machine, even when that virtual machine does not have a public IP address. If you’re new to Azure networking, it may feel a little complicated, but let’s see how to configure and use Bastion.
Click through for a step-by-step guide on how to use the service.
Comments closedRonen Ariely takes us to crazy town:
From time to time someone come to the forum with an interview questions which are not a real scenario on live servers and in other cases the requirement on live servers should be implemented and you better re-design your system so you will not need this recruitment. What ever the reason is, you might want to know how the task can be done and this is what we have in the following request on stackoverflow question.
So.. if someone ask, let’s provide the answer…
I find this interesting in a macabre fashion. I’d really hate to be in a position where the information is useful, though.
Comments closedAsanka Padmakumara takes a look at the user defined managed identity:
If you are familiar with Managed Identity concepts in ADF, each ADF instance comes with own System Assigned Managed Identity (MI). We can use that MI to control ADF’s access to any data sources which support Azure AD based authentication. This is considered to be the most secured and recommended way of authenticating ADF with cloud systems. If not, you can use Azure Key vault to store credentials. Let’s take an example on to discuss how User Assigned Managed Identity helps for manage access within multiple ADF environment.
Click through to see how the user assigned managed identity makes life better.
Comments closedAnthony Nocentino will have none of your SQL authentication:
In this post, we’re going to walk through configuring Active Directory authentication for SQL Server on Linux. We will start by joining the Linux server to the domain, configuring SQL Server on Linux to communicate to the domain, and then use
adutilto create our AD users and set up Kerberos for SQL Server login authentication.
This does take a bit more effort than using Windows authentication, but if you want to use SQL Server on Linux, I’d consider it a worthwhile investment of time.
Comments closedTom Collins does not want guests barging into SQL Server unannounced:
A common SQL Server Security check is to identify if the guest id is enabled. By default , the SQL Server guest id is disabled , but for various reasons it can become enabled.
To identify the guest status you can either use the SQL Server Management Studio (SSMS) or through SQL code.
Click through to see how. I don’t think I’ve ever seen a use case for having the guest account enabled on a SQL Server instance.
Comments closedKenneth Fisher has a pop quiz for us:
I was honored to speak at Pass Summit last week (Thanks again Redgate), and if you’ve ever been to one of my sessions you’ll know there is always a “quiz” at the end. I.e. a crossword puzzle. Well .. here is the puzzle itself, and attached (at the bottom) is the answer key.
I answered “c” for all of the questions and it worked out really well. When in doubt, Charlie out!
Comments closedChris Webb explains that hiding a column isn’t the same thing as preventing access to a column:
In the last few months the following issue has been escalated up to the Power BI CAT team several times: customers have deployed reports into production and then found that users are able to see data they should not be allowed to see by using the “Show data point as a table” feature. The question is: is this a security hole? It isn’t, and in this blog post I’ll explain why and how you should think about security as something that happens on the dataset and not in the report.
My official response is “Hmm…” I don’t disagree with Chris, but I do understand how people might not know this and get blindsided because they think they’ve prevented someone from seeing a sensitive column. I think part of my reaction is that this functionality isn’t blaringly obvious to report developers, and so there’s a little bit of “How could you know this could happen?”
Comments closedWell, why?… perhaps you prefer not spinning more resources to segmentate the environment or decouple the workloads, but you still need to enforce data security across domains.
Lets look at how to secure an HR container in a shared Azure Synapse Analytics workspace that serves mixed workloads by using only RBAC permissions at the storage, and at container level.
It’s recommended to use a separate storage account. I will explain and demo why.
Click through for the demo and explanation.
Comments closedRandolph West has a series on ledger tables in SQL Server. First up is a primer on the topic:
System-versioned ledger tables leverage the same technology: there is a table with current data in it, and an underlying history table which keeps track of changes. However, it uses a cryptographic chain that provides digital forensic evidence of tampering. Yes, if you’ll pardon the use of this phrase, I’m talking about a blockchain.
This is not a cryptocurrency. No one is using expensive graphics cards to produce a fiat currency in someone’s basement. Instead, each transaction affecting the database in question is cryptographically hashed using a SHA-256 algorithm and then stored somewhere off-site.
Part two separates out the two types of ledger table:
This week we will look at the different types of ledger table: append-only and updatable.
Unlike temporal tables, a ledger table can be append-only which makes it immutable. You can only insert data and therefore it does not need a history table. In fact, you may be using append-only tables in your data warehouse already. While this is secure, it may not be practical.
Part three covers limitations:
Every choice we make is a trade-off. New features have limitations, and ledger tables are no exception.
Some of these limitations are perfectly sensible. For example, the whole point of ledger tables is to ensure that we can provide tamper evidence. This necessarily means you can’t turn it off once it’s enabled, unless you drop the database entirely — this is just one scenario where a full defence-in-depth strategy is required.
Part four is the one I’ve been waiting for—an explanation why you probably don’t need this:
After writing several posts about a neat feature in Azure SQL called system-versioned ledger tables, it reminded me about something I’ve wanted to say for a number of years now, outside of snarky tweets.
Here goes:
You don’t need a blockchain.
In the vast majority of use cases, you need a properly audited relational database system with ACID compliance and a good recovery strategy.
There are very specific use cases in which data hashes and ledger tables make sense.
Comments closedLee Markum has a starting point for DBAs in a new role:
You’ve just been hired into a DBA role at a new company, or you’ve been given the DBA keys at your current company. Maybe you’re a SysAdmin and your boss has informed you that you are now supposed to manage the SQL Servers as well as everything else on your plate. In any of these situations, you may have some confidence in your skills, but especially in the case of being a new hire, you have absolutely no true idea of what you’re walking into.
In these scenarios, where do you start? Start with these four areas.
Click through for the four areas. I completely agree with Lee on these for DBAs, including the order.
Comments closed