A Fine Slice Of SQL Server
I 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 closedAndrew Weaver, et al, take us through security practices for running Databricks on AWS:
In this article, we will share a list of cloud security features and capabilities that an enterprise data team can use to harden their Databricks environment on AWS as per their risk profile and governance policy. For more information about how Databricks runs on Amazon Web Services (AWS), view the AWS web page and Databricks security on AWS page for more specific details on security and compliance.
Click through for that list.
Comments closedKenneth Fisher asks the important Stockdale questions (Who am I? What am I doing here?):
It works when I run it this way™ but not when I run it through xp_cmdshell!
It’s a permissions issue. When you run xp_cmdshell you are running under the ??? account.
Little bit more detail. The extended stored procedure xp_cmdshell creates a windows command shell. This shell has to be run under a windows/active directory account. Obviously you can’t get access to a windows resource (a directory for example) using a SQL Server login. The trick is to be able to tell them what account xp_cmdshell is using within that shell. There are two possibilities here.
Read on to learn about those two possibilities.
Comments closedAnthony Nocentino has a tip for us:
Ever need to set your web server a specific protocol version of TLS for web servers and need a quick way to test that out to confirm? Let’s check out how to use
curlto go just that.This code here uses
curlwith the parameters--tlsv1.1 --tls-max 1.1, which will force the max TLS protocol version to 1.1. Using the--verboseparameter gives you the ability to see the TLS handshake and get the output sent to standard out.
Also, check the comments for a very helpful addendum.
I should note that cURL is built into Windows 10 as of v1803, and it’s been a part of MacOS and Linux for a long, long time.
Comments closedMax Trinidad can keep a secret:
Finally, I came up with a practical example using the Powershell Secret Management module for storing SQL credentials. This is an excellent way of keeping your SQL connection strings information out of your scripting code. This way we just have it stored in our Vault.
Read on for a walkthrough and demonstration.
Comments closedSQL Server gives us a few different options when it comes to encryption and I’m going to take a look at each of them at some point in this series but in this first post in the series I want to look at column level encryption.
Before we can even start thinking about encrypting our data, there are a few things that we’re going to need to set up first.
Although I joke about column-level encryption, David shows us just how easy it is to implement. It’s quite useful if you have just one or two columns in the database which need to be encrypted at rest and you don’t want to (or can’t) have the application handle it directly.
Comments closedAnyways, with that obvious answer out of the way, let’s talk about something more interesting, like sp_help_revlogin.
Remember sp_help_revlogin? It’s that stored procedure that Microsoft published more than 20 years ago, that never found its way into the SQL Server built-in system procedures. Microsoft still maintains that same KB page till this day (by “maintains”, I mean copy-and-pasting it from one place to another as they change their KB platforms).
Read on to understand what this is and several ways of doing the same thing better, including a new sp_help_revlogin2 that Eitan has put together.