Press "Enter" to skip to content

Category: Security

Wrapping Get-Credential in Powershell

Published 2022-08-26 by Kevin Feasel

Stuart Ainsworth writes a script:

Just a quick blog; am working on a script that requires credentials to run against a REST API, and a developer wanted to convert that script to use command-line parameters. I built this script (and quick test) to show that the command-line parameters create the same object as the Get-Credential object.

The one thing that has me on edge a little is that the password comes in as plaintext. I do understand that sometimes this is the best route, though I’d put the pressure on devs to make sure there isn’t a route in which plaintext passwords get encrypted before transmission, even in a local environment like this.

Comments closed

The Importance of Dev Environment Security

Published 2022-08-24 by Kevin Feasel

Kenneth Fisher shares an opinion:

I was asked a rather interesting question during an interview recently. It went something like this:

Your resume says you’ve implemented security standards. What do you do if a development team comes to you and says “We have to have a SQL Server Id in production (or other restricted permission) because that’s the way we tested it and we are going into production now.”?

And in general my response is “You give it to them. It’s too late to stop them at that point.”

Kenneth goes on to elaborate.

I prefer a compromise solution, which requires more than 2 environments. Dev is a sandbox. Developers don’t know what they need and generally need more space to work than the final product itself will need. A wide-open dev environment is something I don’t mind. In some organizations, a local dev environment may be sufficient for this task—that is, SQL Server (or whatever) installed on the developer’s personal machine or brought in via Docker container.

However, there must be at least one pre-production environment in which developers have prod-level rights. Ideally, it would not be the last step of integration prior to prod but instead something like a QA environment which is part of a CI/CD process.

I should also point out that module signing helps with this problem: stored procedures may contain rights that devs shouldn’t have but are necessary to the way an application functions. Access to execute those stored procedures is something the DBA team can control and put some limits around, so in Kenneth’s example, truncation could be handled via stored procedure. That procedure handles the truncation operation and opens the door to further checks, such as a list (or table) of tables which the app is allowed to truncate, logging of operations, etc.

Comments closed

Granular Permissions in SQL Server 2022

Published 2022-08-24 by Kevin Feasel

Andreas Wolter notes some permissions changes:

In the last 3 years, the SQL Security team has put more emphasis on enabling customers to use SQL Server while adhering to the Principle of least Privilege (PolP). As part of that effort, all new features in the next SQL Server release: SQL Server 2022, can be controlled with more granular permissions. SQL Ledger is a good example of such a new feature. It comes with separate permissions for creating (ENABLE LEDGER) vs dropping ledger tables (ALTER LEDGER); a separate permission to view ledger content (VIEW LEDGER CONTENT) for auditing purposes; and another independent permission to generate a ledger digest (GENERATE LEDGER DIGEST).

Besides new features, a lot of effort also went into improving the existing set of permissions, namely by making them more granular. This has happened in 3 distinguishable areas:

Check out the changes they’ve made.

Comments closed

The Mechanics of Transparent Data Encryption

Published 2022-08-22 by Kevin Feasel

Etienne Lopes takes us through the process of using Transparent Data Encryption:

Securing data has always been important but as time goes by, more and more data is available all around us, some of it is considered sensitive data and it becomes a major concern to protect it somehow, in fact in certain cases it is legally mandatory to comply with certain regulations (like GDPR). SQL Server offers a few options regarding data protection (either by means of encryption or obfuscation), TDE being one of them.

In this post I’ll explain what is TDE along with its use cases and I’ll use a thorough demo to show how to implement it in a database and how it works

I do tend to give TDE disrespect (disrespect that I think it deserves) but it does allow you to check a compliance box without enormous cost. The problem is, I don’t think it moves the needle in terms of proper security when the attacker has admin status on the machine hosting SQL Server and other techniques (e.g., encrypting backups, encrypting specific columns) are better at preventing security issues in other common data scenarios. I’m just not sure there’s a case where TDE helps and there isn’t already a better solution.

Comments closed

Security Practices for Delta Sharing

Published 2022-08-05 by Kevin Feasel

Andrew Weaver, et al, share some advice:

When you enable Delta Sharing, you configure the token lifetime for recipient credentials. If you set the token lifetime to 0, recipient tokens never expire.

Setting the appropriate token lifetime is critically important for regulatory, compliance and reputational standpoint. Having a token that never expires is a huge risk; therefore, it is recommended using short-lived tokens as best practice. It is far easier to grant a new token to a recipient whose token has expired than it is to investigate the use of a token whose lifetime has been improperly set.

Click through for eight such tips.

Comments closed

Preventing Data Exfiltration form Managed Instances

Published 2022-08-05 by Kevin Feasel

Niko Neugebauer wants to hang on to that data:

Data exfiltration is a technique that is also sometimes described as data theft or data extrusion, that describes the unauthorized extraction of data from the original source. This unauthorized extraction can be executed either manually or automatically by the malicious attacker.

As part of your Network Infrastructure, you might have tightened your security to make sure you have all the bells and whistles to lock down your Azure SQL Managed Instance to be accessed only by your application and not exposed to the Internet or any other traffic. However, this doesn’t stop a malicious admin from taking a backup or creating a linked server to another resource outside your enterprise subscription for extracting the data. This action would be data exfiltration. In a typical on-premises infrastructure, you can lock down network access completely to make sure that the data never leaves your network. However, in a cloud setup, there is a possibility that someone with elevated privileges can export data or perform some other malicious activity targeting their own resources outside your organization, compromising your enterprise data. Hence, it is very important to understand the different data exfiltration scenarios and make sure that you are taking the right steps to monitor for and prevent such activities.

Click through for a table which shows common exfiltration scenarios and things you can do to reduce the risk of exfiltration. With access, though, there’s always going to be a risk of exfiltration: even in a SCIF, you can get away with shoving records into your pants if you’re famous enough.

Comments closed

Executing as User or Login

Published 2022-08-01 by Kevin Feasel

Kenneth Fisher puts on a mask:

I use impersonation a lot. It’s a really easy way to check if someone has the permissions they are supposed to. That said, a co-worker recently had an interesting question. They were testing permissions on a synonym.

Msg 916, Level 14, State 1, Line 3
The server principal “Domain/NetworkName” is not able to access the database “OtherDB” under the current security context.

Read on to see what caused the issue and how you can fix it.

Comments closed

Azure Active Directory Authentication in SQL Server 2022

Published 2022-08-01 by Kevin Feasel

Mirek Sztajno has an interesting announcement:

Enabling Azure AD authentication opens access to the Azure cloud identity system. Azure AD is used by many cloud services and unifies all local authentication mechanisms used by Microsoft products providing one central identity repository and authentication management system available to different platforms, including Azure SQL and SQL Server on-premises. The variety of available authentication methods including single sign-on (SSO) and multifactor authentication (MFA), provides strong security support in the authentication area for different services used internally by Microsoft and by external customers. Azure AD authentication is the recommended authentication method for Azure SQL and SQL Server.

Looks like it does require Azure Arc, which has a fairly small per-instance monthly charge. Click through for the details. That said, you will be able to use this feature on-premises and in other clouds, not just in Azure VMs.

Comments closed

Breaking Changes in Azure Data Explorer

Published 2022-08-01 by Kevin Feasel

Gabi Lehner announces a change:

The current_principal_is_member_of() function checks if the principal who runs the query is a member in any of the users, apps or groups provided as arguments.

Up until now, it was allowed to specify the AAD group details in multiple forms, including the display name of the AAD group, without specifying the tenant id or name, for example current_principal_is_member_of(“mygroup”).

I have to say, that’s a pretty big security flaw.

Comments closed

Cross-Subscription Key Vault Access

Published 2022-07-26 by Kevin Feasel

Andrew Coughlin sets up secure Key Vault access:

Let’s first discuss the setup of what we will be discussing in this blog post.  I will have two subscriptions assigned to the same Azure AD Tenant.   Within each Azure subscription I will have a resource group in each.  I will create the Azure Key Vault in one subscription / resource group and then I will create a virtual machine in the other subscription / resource group.  This is just for example purposes; I could utilize other azure services that can use managed identities.   I could also create a service principal for my application to use to get keys or secrets.

In this example we would be using private endpoints.  Are you looking for how to do this with public endpoints?  Check out my recent post on how to do that here .

When in doubt, private endpoints are the right choice. They’re probably the right choice when not in doubt as well.

Comments closed