A Fine Slice Of SQL Server
This introductory post explored the basics of cybersecurity and how it relates respectively why it requires data in motion powered by Apache Kafka. The rest of the series will go deeper into specific topics that partly rely on each other.
Threat intelligence is only possible with situational awareness. Forensics is complementary. Deployments differ depending on security, safety, and compliance requirements.
Click through for the article.
Comments closedGrant Fritchey reviews minimum requirements:
I was recently asked what permissions were needed to force plans in query store. I’m sure I knew at one point, but at the moment I was asked, I couldn’t remember to save my life. So, I went and looked it up. In the interest of sharing, especially for the poor person who I left hanging, here’s what I found.
Click through for the disappointing answer.
Comments closedAndy Mallon says it’s time to give up a couple of procedures:
We recently ran into some performance problems with our login sync, which is based on
sp_hexadecimalandsp_help_revlogin, the documented & recommended approach by Microsoft.I’ve been installing & using these two procedures since I started working with SQL Server, back at the turn of the century. In the nearly two decades since, I’ve blindly installed & used these procedures, first on SQL Server 2000, and then on every version since… just because that’s the way I’ve always done it. But our recent performance problems made me rethink that, and dive in to take a look at the two procedures to see if I could do better, which made me realize, OHBOY! WE CAN DO BETTER!!
Read on to understand how.
Comments closedAaron Bertrand recaps some recent installation issues:
I’ve created countless Docker containers running SQL Server since I first wrote about it back in 2016, but I recently had my first foray into configuring SQL Server 2019 on a real live Linux machine.
It did not go as smoothly as I expected, so I wanted to share the solution to a particular problem I haven’t seen described elsewhere.
First, let me retrace my steps.
Click through for a summary of the issues.
Comments closedEric Rouach shows off a pair of things:
Using as an example the AdventureWorks2014 database, the first script describes the process of encrypting the “CardNumber” column from the Sales.CreditCard table while keeping the data decryptable.
Our pre-requisite is the creation of a Master Key, a Certificate and a Symmetric Key.
Once having those created, we may proceed to the addition of a new column called “CardNumberEnc” (where the suffix “Enc” stands for “Encrypted”). This column has a VARBINARY(250) Data Type and is nullable.
Read on for an example of using column-level encryption, followed by how you’d decrypt the data. Then, Eric discusses hashing, though I disagree with the nomenclature of “encryption and make the data non-decryptable.” The reason is that encryption is, by its nature, a two-way process and necessarily requires the ability to decrypt. Hashing, meanwhile, is a one-way process without a direct means of reversal. Nomenclature aside, the examples are good and I appreciate Eric using one of the larger SHA2 hashing algorithms rather than MD5.
Comments closedKenneth Fisher keeps the receipts:
In my last post I talked about reasons why your permissions might go missing. One of the reasons, and in my experience, one of the more unusual reasons, is that a command was run that changed the permissions. SQL doesn’t natively log these. Well, technically it does. They can be found in the default trace. But I don’t generally consider that to be terribly useful because on a busy server what’s available in the default trace may not last long. Regardless I ended up creating a DDL trigger to collect any database level security commands run. There are other ways to do this but for various reasons I decided to go with a trigger. Primarily because I could create one piece of code and cover every database in the instance.
Read on to learn how to create an appropriate table and a trigger to log that data.
Comments closedI see a lot of scripts on the internet that use dynamic SQL, but leave people wide open to SQL injection attacks.
In many cases they’re probably harmless, hitting DMVs, object names, etc. But they set a bad example. From there, people will adapt whatever dynamic SQL worked elsewhere to something they’re currently working on.
Click through for a demonstration of the problem.
Comments closedJon Morisi troubleshoots an irksome issue:
Just a quick blog here about an issue I had with HDP-3.1.4.0. I recently was setting up a new user with specific rights in Ranger for Hive access. After creating the new policy and attempting to validate it, I received an error message stating that the hive user does not have use privilege. This error was produced even though I had just created the policy specifically granting those privilege’s.
Upon further review I noticed that the plugin was downloading the policy, but not applying it.
Read on to learn what the problem was and how Jon corrected it.
Comments closedYou have a data lake that contains employee and social feed data. You have data residing in an employee folder that is used by HR team members and twitter for live social feeds that is usually used by marketing folks. If you use SAS token or RBAC, you cannot control to the folder level.
How do you allow users to perform data exploration using synapse serverless with fine grain control on underlying storage.
Read on for one solution.
Comments closedAlex Stuart shows how to fail over SSISDB in SQL Server 2012 or 2014:
Hopefully not many people are still configuring SSIS instances on SQL 2012 or 2014 – especially HA instances – but if you are, this post is for you.
If you’re running SQL Server 2016 or above, having the SSIS catalog function correctly in an AG is supported by built-in functionality to manage the DMK (database master key). In 2012/2014 however there is no such support. Without intervention this makes SSISDB unable to be opened after a failover, because the DMK isn’t open – leading to errors such as “Please create a master key in the database or open the master key in the session before performing this operation.”
Read on to see how to resolve this error, and then how to do this automatically.
Comments closed