A Fine Slice Of SQL Server
Tom Collins compares and contrasts:
A common question from developers & data owners is what benefits does Always Encrypted offer over column level encryption aka cell level encryption? First thing to understand is what are the basic differences between the two methods – Column-level encryption vs Always encrypted
For as much as I appreciate Always Encrypted, it seems I use column-level encryption about an order of magnitude more often.
Comments closedTom Collins needs to encrypt some data:
Using a symetric key , how can I encrypt a column in a SQL Server database table? And what are some of the common pitfalls related to setting up column level encryption
Read on for the process. Even with other options available like Always Encrypted, I keep coming back to this because it’s simple, contained in-database, and works well.
Comments closedTracy Boggiano continues a series on things to do at a new job as a DBA:
In my previous post, I expounded on my first 30 days I had at four jobs in the last four years. and how to setup your jobs box. I commented and got quoted on the fact that if it’s documented I don’t support it. So, these are methods of getting things documented, some including just having to have meetings, others running code.
One I believe in having a Central Management Server (CMS) where you can register your servers. Put them in as many groups as you desire but have core group such as Dev, Test, QA, UAT, Prod, Prod Sec, etc. The rest could be by application name if needed. I always have a set of names that are for the DBAs to use to do our work, other teams can have theirs for their work, i.e., deploying code.
If you aren’t using dbatools yet you should be. While not every shop can use to manage everything it is works every well for most tasks and that includes scanning the network for SQL Instances. Because unless you could into a well oiled machine there will be instances they don’t know about and one day someone will come knocking asking to fix it. Warn your security team before you run this.
Read on for examples of how you can find instances (assuming the security team is okay with it!), some of the information you’d want to document, and more. I would also recommend the most recent episode of the SQL Data Partners podcast, in which we talk to Jen and Sean McCown about documenting and managing your SQL Server inventory.
Comments closedMarco Russo and Alberto Ferrari take advantage of object-level security:
To be more technical, object-level security can be applied to tables and columns, but it cannot be applied to measures directly. If a measure – whether in a direct or indirect way – references a column or a table that is not accessible under the current security context, the measure becomes invisible as well. This way, it is guaranteed that if a column must be invisible to a group of users, its content cannot be inferred by looking at the result of measures based on said column.
Concretely, there may be cases where a measure should be hidden from a group of users without removing the visibility of existing data structures. Let us look at a simple example first. We define the Sales Amount measure as the product of Sales[Quantity] by Sales[Price]. You also have a Discounted Sales measure that applies a set discount Sales Amount; now how can you hide Discounted Sales from a group of users without hiding the initial Sales Amount measure? By hiding either Sales[Quantity] or Sales[Price], you would hide both measures. Because the discount is set inside the Discounted Sales measure and not stored in the model, it looks as though you cannot hide just the measure. However, it we create a dependency in Discounted Sales on an empty hidden table specifically created to generate that dependency, we can hide Discounted Sales by hiding that table.
Read on to see how.
Comments closedKenneth Fisher asks, who audits the auditors?:
I love database audits. They are simple, easy to use, effective, not overly resource intensive, and can be turned on and off at need once created. That said, they do have a few gotchas. If you want every user put public as the principal. And if you don’t, and you put in an AD user, be aware that if that user will be created (along with a matching schema) when you create the Database Audit Specification.
Read on for Kenneth’s experience and a way to clean up these potentially-added users.
Comments closedTom Collins says this time, it’s mutual!:
In a recent meeting i heard the term MTLS , what does this mean and what is the difference between Transport Layer Security (TLS) versus Mutual Transport Layer (MTLS)?
Read on to see the differences in behavior between the two techniques.
Comments closedAaron Bertrand clarifies two concepts:
The underlying problem is that servicing complex software is, well, complex. Microsoft simplified this for our little corner of the world when they announced that SQL Server 2016 would be the last release to get service packs. We still have Cumulative Updates (CUs) and General Distribution Releases (GDRs) to deal with, but they tend to only cause confusion around Patch Tuesday (or the – cough – odd time a CU breaks things). Before I explain, let’s define these:
Read on for the definitions and why the GDR path exists.
Wait, I thought the German Democratic Republic (GDR / DDR) re-unified with the Federal Republic of Germany (FRG / BRD) in 1990… Ah, the lengths I go to for an awful joke.
Comments closedReitse Eskens has some routing issues:
To make sure the on-premises connection uses the VPN and the private endpoint, we need to make sure the on-premises DNS (it’s always DNS) recognizes the traffic and redirects it to the VPN connection. But whatever we tried on the firewall, the traffic kept going the wrong way. It did have something to do with the on-premises DNS setup in the end.
When we tried to connect to the Azure SQL instance on IP-address, it threw an error because the instance wasn’t found. You can only connect to it with the FQDN (dbname.database.windows.net)
Click through to see what the problem was and how Reitse solved it.
Comments closedHadi Fadlallah takes us through xp_cmdshell:
In brief, xp_cmdshell is a system stored procedure in SQL Server. It allows executing Windows shell commands from the SQL Server environment. While commands are passed as an input string, the shell’s output is returned as rows of text.
The xp_cmdshell takes two parameters; one required and one optimal:
Hadi does a good job of showing us what security is in place protecting malicious use of xp_cmdshell and how you can add a person to the list of users.
Comments closedGilbert Quevauvilliers needs a report, stat!:
In a previous tweet on twitter, I had elaborated on how I had extracted the RLS Roles with the details and then exported it into a CSV file which then allowed the organization to keep an audit of the RLS for the dataset.
In the steps below I will show you how I did this.
In my previous blog post I explained how to export data from a Power BI report to a CSV file here: Exporting a Power BI Visual data to a CSV File in SharePoint
Read on to see how, as well as a few notes on what it takes to get this report.
Comments closed