A Fine Slice Of SQL Server
Arun Sirpal tries out something new in SQL Server 2019:
Yes, a really nice new shiny feature where we have the ability to suspend and resume the encryption scan for TDE – Transparent Data Encryption which is available in SQL Server 2019. (Tested against the latest version CTP 2.4)
Arun shows the syntax you can use to suspend and resume should you need to do that.
Comments closedAbhinav Garg and Anna Shrestinian explain how you can use VNET injection with Azure Databricks:
To make the above possible, we provide a Bring Your Own VNET (also called VNET Injection) feature, which allows customers to deploy the Azure Databricks clusters (data plane) in their own-managed VNETs. Such workspaces could be deployed using Azure Portal, or in an automated fashion using ARM Templates, which could be run using Azure CLI, Azure Powershell, Azure Python SDK, etc.
With this capability, the Databricks workspace NSG is also managed by the customer. We manage a set of inbound and outbound NSG rules using a Network Intent Policy, as those are required for secure, bidirectional communication with the control/management plane.
This is a good article if the defaults won’t get past corporate security.
Comments closedJonathan Kehayias reminds us to back up those SQL Server Reporting Services encryption keys:
If you run SQL Server Reporting Services, part of your DR plan needs to include a backup of the encryption key for SSRS. This sadly is an all to often overlooked part of the solution, even though it is incredibly easy to do. If you don’t have a backup of the encryption key during a restore, the report server will never be able to decrypt the encrypted content (connection strings, passwords, etc) stored in the database, and your only recourse would be to delete the encrypted content and recreate it manually or through a redeployment of datasources.
Jonathan includes a couple of links to good resources. Your backups are only good if they include all of the keys and certificates you used. But keep those certificates stored someplace other than where the backups are stored.
Comments closedDuncan Greaves explains the concepts behind l-diversity:
There are problems with K-anonymous datasets, namely the homogeneous pattern attack, and the background knowledge attack, details of which are in my original post. A slightly different approach to anonymising public datasets comes in the form of ℓ -diversity, a way of introducing further entropy/diversity into a dataset.
A sensitive data record is made of the following microdata types: the ID; any Key Attributes; and the confidential outcome attribute(s). ℓ -diversity seeks to extend the equivalence classes that we created using K-anonymity by generalisation and masking of the quasi-identifiers (the QI groups) to the confidential attributes in the record as well. The ℓ -diversity principle demands that, in each QI-group, at most 1/ ℓ of its tuples can have an identical sensitive attribute value.
L-diversity is not perfect either, but Duncan gives a good explanation of the topic.
Comments closedPriyaj Kumar takes us through the process of building a Virtual Private Cloud in AWS:
AWS provides a lot of services, these services are sufficient to run your architecture. The backbone for the security of this architecture is VPC (Virtual Private Cloud). VPC is basically a private cloud in the AWS environment that helps you to use all the services by AWS in your defined private space. You have control over the virtual network and you can also restrict the incoming traffic using security groups.
Overall, VPC helps you to secure your environment and give you a complete authority of incoming traffic. There are two types of VPCs, Default VPC that is by default created by Amazon and Non-Default VPC that is created by you to suffice your security needs.
Now that you have an idea of how VPC works, I will take you through the different services offered by Amazon VPC.
Read on to see how to set one up.
Comments closedMeagan Longoria does a bit of investigation into data privacy and Power BI visuals:
One thing that makes understanding data privacy in custom visuals easier is the designation of a certified custom visual. One of the requirements for certification is ” Does not access external services or resources, including but not limited to, no HTTP/S or WebSocket requests go out of Power BI to any services.”
You can find the list of currently certified custom visuals on this page. Custom visuals are also identified in the marketplace by a blue star with a check mark.
Read on for some good investigative analysis.
Comments closedMichal Poreba shares some lessons from trying to set up Docker and SQL Server to use gMSA accounts:
While in the end I was able to make it work on Windows Server 2016, 1803, 2019 and 1809 I wasted some time trying to make it work with docker 17.06. Unsuccessfully. Docker 18.09.1 and 18.09.2 worked every time.
There are some reports of intermittent problems with specific OS updates breaking stuff, like the one here but I wasn’t able to reproduce it. I wonder if the updates changes something else that it causing problems, in other words is it the problem with the update itself or the update process?
Read on for several helpful tips, as well as dead ends to avoid.
Comments closedService Accounts are a requirement for installing and running a SQL Server. For many years Microsoft has recommended that each SQL Server service be run as a separate low-rights Windows account. Where possible, the current recommendation is to use Managed Service Accounts (MSA) or Group Managed Service Accounts (gMSA
). Both account types are ones where the account password is managed by the Domain Controller. The primary difference being that MSA are used for standalone SQL instances, whereas clustered SQL instances require gMSA. In this post, we’re going to use PowerShell to create Group Managed Service Accounts, and then deploy them for use on multiple SQL servers that will be hosting an Availability Group.
Click through for more explanation as well as several scripts showing how to create and use them.
Comments closedDuncan Greaves has some thoughts about safeguarding privacy in shared databases:
The difficulty with privacy (or more correctly, information confidentiality) in database terms is that databases are supposed to maintain huge amounts of information, and processing and recording data is difficult, if not impossible without them. Public bodies especially, have difficulty in defining and maintaining the boundaries of information disclosure that they should provide, whilst maintaining the utility of the information for the improvement of welfare and services.
Privacy is contingent on first having a correctly secured database. Additional privacy controls are required when sensitive data leaves the protected trust perimeter of the database to be utilised by third parties.
Click through for more detail.
Comments closedThe good news is that it is not an unreasonable requirement and it has been done before. The solution is to use Group Managed Service Accounts (gMSA) and Credential Spec Files. A number of people have already documented their efforts. Some were more successful than others.
Click through for a detailed guide to getting this working.
Comments closed