A Fine Slice Of SQL Server
The 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 closedAneesh Lal Gopalakrishnan stops an attack:
ASP.NET MVC and ASP.NET Core are traditionally some of the most used platforms to build financial web applications, such as banks and hedge funds. From a statistical standpoint, these platforms are trusted more than their counterparts, such as Express or NodeJS, for financial web applications. In addition, it is easier to fix CSRF issues in ASP.NET Core than in ASP.NET MVC because of the better tools and support available. We will investigate techniques to fix CSRF issues in ASP.NET MVC.
About 10-15 years ago, CSRF was one of the top threats (in 2007 and 2010, it was #5; in 2013, it was #8), but then it dropped off the list. The reason is that, basically every platform in existence put in anti-CSRF tokens automatically, so you rarely see it work anymore except for really old websites.
Comments closedDavid Fowler gives it to us straight:
xp_cmdshell is an extended SQL stored proc that allows users to run Windows command prompt commands from within SQL. Sound scary? It might, but is xp_cmdshell really a security risk?
Well a lot of people think so, many DBAs and IT departments will insist that it’s always disabled and many auditors and pen testers will raise it a significant vulnerability if they see it enabled on any of your SQL Servers.
But is it really that much of a security risk?
Click through for David’s thoughts, which match my own quite well here. Either xp_cmdshell is not the problem because you explicitly needed to make bad decisions in order for it to hurt you, or xp_cmdshell is not the problem because a bad person got access to a sysadmin account and hurt you. In neither case was xp_cmdshell the proximate cause.
Comments closedMarc Lelijveld and Vytautas Kraujalis lock things down, over and over and over:
Imagine, you have everything setup and well secured in your data lakehouse, or data warehouse if you will. Then a user starts consuming the data in Power BI and imports all data according to the security applied to that users’ permissions. Once the data is imported, all data can easily be shared to others who might have other permissions on the same dataset. Potentially, this leads to a breach of data to people who should not have accessed this data at all. Ideally, you replicate the security from the source into Power BI, but without setting up everything by hand.
In this blog post, you will learn how you can automate the replication of security from source to your Power BI data model in just a few steps. A blog post based on an actual client case and written by Vytautas Kraujalis and myself.
Click through for an explanation and a link to the GitHub repo containing all of the scripts.
Comments closed