A Fine Slice Of SQL Server
Matthew McGiffen shares some advice:
Database backups continue to work without change when you have TDE enabled. The only difference is that the backups contain encrypted data that cannot be read without the certificate and private key. There are a couple of points that are worth discussing though.
Click through for several notes, including a warning to those still on SQL Server 2016 and woefully under-patched.
Comments closedAaron Bertrand notes some changes:
In 2015, during the SQL Server 2016 beta, I explored a new feature is this article, Always Encrypted. This feature finally allowed us to encrypt data at rest and on the wire, and I showed how beneficial this was and how much more secure your data could be. I also explained that, as a new feature, some limitations made it difficult to use and, sometimes, impossible to adopt.
Several major versions of SQL Server later, how has this feature evolved, and is it easier to use today?
Read on for the answer. Aaron also covers secure enclaves, a big topic for Always Encrypted users.
Comments closedMatthew McGiffen answers an age-old question:
Microsoft states that enabling TDE usually has a performance overhead of 2–4%. That doesn’t sound like very much, and personally I wouldn’t let it bother me if I want to make sure my data is encrypted at rest. However, you may have heard other sources saying that it’s actually a lot more than that – and the performance impact is a high price to pay for the level of protection offered. So, what’s the truth?
It turns out the answer is a bit more complex than simply saying “x%,” though as a first approximation, I’d still say that the 2-4% is a good starting point. For what would move you off of that 2-4%, read the whole thing.
Comments closedThe Big Data in Real World team expands beyond localhost:
You might encounter the below error message when your Kafka consumers or clients connect to Kafka broker for ingestion or consumption.
Connection to node -1 (/127.0.0.1:9092) could not be established. Broker may not be available.
Read on to see what the problem is and how you can fix it.
Comments closedDany Hoter keeps it all on the Azure backbone:
The PBI developer creating datasets and reports need to connect to the ADX cluster using Power BI desktop.
To establish such a connection, the user’s IP address should be allowed access to the private end point.
The access should be tested using Kusto Web explorer (KWE) to make sure that the cluster can be reached.
If KWE can connect , Power BI desktop should also connect successfully and a report using the cluster in Direct Query or import can be created.
That’s the goal, and Dany shows us the way to do it.
Comments closedSteve Steadman reminds us of a SQL Server feature:
The Scan For Startup Procs feature in SQL Server allows you to specify a list of stored procedures that will be automatically executed whenever the database engine starts. This can be useful in certain scenarios, such as when you want to perform tasks such as restoring a database or performing maintenance tasks when the database engine starts.
“Scan for startup procs” is a configuration option in Microsoft SQL Server that determines whether the server should scan for and execute stored procedures that are marked as “startup procedures” when the server starts up.
I’ve used this to good effect in the past, but there is a fundamental problem with this approach: it’s easy to forget about these, potentially leading to a difficult search for why some action took place. If you only let sysadmins add or change startup stored procedures, then I’d consider this just as little a security risk as xp_cmdshell: if the attacker already has sysadmin, the attacker can simple enable the feature, so there’s no real value to denying yourself the capability if it makes sense in your environment.
Comments closedSecurity is an ongoing journey of incremental progress and maturity, and not a static destination. The Cloud Adoption Framework provides security guidance for this journey by providing clarity to the processes and best practices. This guidance is based on real world experiences of our customers, of Microsoft’s own security journey and lessons learned, and the work with other organizations like NIST (National Institute of Standards and Technology) or CIS (Center for Internet Security).
The outcome is manifested in the Cloud Adoption Framework Secure Methodology which provides a vision of the complete end state of your security journey and follows the Zero Trust principle (assume breach, verify explicitly, use least privilege access).
This assessment gives you the opportunity to self-assess your security journey of your cloud adoption against this secure methodology.
Read on to learn more about how CASRs work and how you can perform one yourself.
Comments closedChad Callihan makes an update:
When recently troubleshooting an issue, I needed to update a database record to test application functionality. Because the table had an Always Encrypted column, some extra steps were needed to make the UPDATE successfully. Let’s look at the error encountered and how it was resolved.
Click through for the error and see how Chad got around the problem. This is definitely one of those head-scratcher solutions, where you can kind of understand why it’s necessary but still think the required process is dumb.
Comments closedEnrique Lopez de Lara shares a few ways that Snowflake allows us to protect data in its system:
The role hierarchy in the previous section defines what can be done on different objects and by whom. However, it doesn’t restrict which records within a table a user can see or which values should be masked within a column. That’s where the data governance policies in this section come into play.
All data governance policies and tags are stored in the PROD_DB_GOV database under three schemas: MASKING, ROWACCESS and TAGS. Putting all the policies and tags in a single database allows us to centralize them and better restrict access to them. Please note that only the GOV_ADMIN role has read/write permissions on it.
These are, for the most part, very similar to what we’re used to in relational databases: application and system roles, row-level security, and data classification.
Comments closedMichael Howard provides an explanation:
Invariably, when I ask people what Transport Layer Security (TLS) does, they say something like, “it protects my credit card info when I buy things online.”
The response is not incorrect, but it’s not the whole story, either, and let’s just ignore the server-side credit card protection requirements and PCI compliance for a moment.
TLS provide three security services, with an optional fourth.
Click through for those services and some of the ways we can improve our security posture when connecting to (or hosting!) a SQL Server instance.
Comments closed