Press "Enter" to skip to content

Category: Security

xp_cmdshell And Non-Sysadmin Accounts

Published 2019-01-07 by Kevin Feasel

Lucas Kartawidjaja shows us how you can grant a non-sysadmin user the right to run xp_cmdshell:

Once we run the above T-SQL query, any account that is part of the sysadmin role in the SQL Server instance has the ability to run the xp_cmdshell extended stored procedure. On the background, when the user with sysadmin privileges runs the xp_cmdshell, it will execute the Windows command shell using the SQL Server Service Account (So if you are executing xp_cmdshell to access certain resource on the network, for example, and you are having permission issue, you might want to make sure that the SQL Server Service Account has permission to that resource).
Now, what if you have a non-sysadmin account that needs to run xp_cmdshell? In order to do that, we would need to do some additional configuration.

Granting non-sysadmins rights to run xp_cmdshell definitely rates as well above-average in terms of risk. I don’t have any problem with xp_cmdshell being turned on—especially considering that by default, only sysadmin accounts get it and sysadmin accounts can turn it on if it’s disabled, meaning it’s effectively always on for sysadmin. But when you start granting non-sysadmin accounts the ability to shell out, you have to be even more careful of protecting that SQL Server instance.

Comments closed

Switching Azure Portal Accounts

Published 2018-12-26 by Kevin Feasel

John Morehouse is happy with a change to the Azure Portal:

This means that I could have multiple email accounts that I have to use in order to sign into the portal.  Using a password manager such as 1Password, not usually a big deal and more of an annoyance rather than a headache.
Within the past month or so, Microsoft has updated the portal to allow me to easily switch accounts.  Previously you had to log out of the portal and then log back in.

This is quite convenient. Prior to this change, switching to a different account could goof with other sites I had open (like if I was sending an Outlook e-mail through one account, switching the Azure Portal signed-in account would log me out from Outlook). It’s still not a perfect experience but it’s a lot better.

Comments closed

Always Encrypted With Secure Enclaves

Published 2018-12-18 by Kevin Feasel

Jakub Szymaszek announces secure enclaves support with Always Encrypted in SQL Server 2019:

The only operation SQL Server 2016 and 2017 support on encrypted database columns is equality comparison, providing you use deterministic encryption. For anything else, your apps need to download the data to perform the computations outside of the database. Similarly, if you need to encrypt your data for the first time or re-encrypt it later (e.g. to rotate your keys), you need to use special tools that move the data and perform crypto operations on a different machine than your SQL Server computer. These restrictions are not an issue if equality comparison is all your applications need and if the tables containing your sensitive data are small. However, many types of sensitive information, e.g. a person’s name or phone number, often require richer operations, including pattern matching and sorting, and it’s not uncommon for sensitive data to be too large to move outside of the database for processing.
To address the above challenges, Always Encrypted in SQL Server 2019 is enhanced with secure enclaves. A secure enclave is a protected region of memory that appears as a black box to the containing process and to other processes running on the machine, including the operating system. There is no way to view the data or code inside the enclave from the outside, which makes enclaves ideal for processing sensitive data. There are several enclave technologies that differ in how enclave isolation is accomplished. SQL Server 2019 preview uses a Windows Server technology called Virtualization Based Security (VBS), which relies on Hypervisor to protect and isolate enclaves.

They’re going further with Always Encrypted than I thought would be possible.  The first release of Always Encrypted had me asking “Why would I use this over running an encryption or decryption function in my app code?”  I think secure enclaves starts to answer that question.

Comments closed

Preventing Credential Compromise When Using AWS

Published 2018-12-06 by Kevin Feasel

Will Bengtston walks us through techniques Netflix uses to protect credentials in AWS:

Scope

In this post, we’ll discuss how to prevent or mitigate compromise of credentials due to certain classes of vulnerabilities such as Server Side Request Forgery (SSRF) and XML External Entity (XXE) injection. If an attacker has remote code execution (RCE) or local presence on the AWS server, these methods discussed will not prevent compromise. For more information on how the AWS services mentioned work, see the Background section at the end of this post.

Protecting Your Credentials

There are many ways that you can protect your AWS temporary credentials. The two methods covered here are:

  • Enforcing where API calls are allowed to originate from.

  • Protecting the EC2 Metadata service so that credentials cannot be retrieved via a vulnerability in an application such as Server Side Request Forgery (SSRF).

Read the whole thing if you’re an AWS user.

Comments closed

Cross-Availability Group Login Management

Published 2018-12-06 by Kevin Feasel

David Fowler walks us through a problem about orphaned users and Availability Groups:

Now, I’m pretty sure that most of us will have been in the position where, after a fail-over we get inundated with calls, emails, Skype messages and carrier pigeon drops letting us know that so and so can no longer access the database.

When you look into it, you either find that the login never existed in the first place, so you create it or that it was there but the database user has become orphaned from it (happens when the login SID doesn’t match the SID of the database user, Adrian wrote about orphaned users in Dude where’s my access?).

You remap the orphaned user and everything is good again…  that is until the next time you failover and once again you’ll be hit with the same orphaned user problem.

Click through for the explanation and a permanent fix for this issue.

Comments closed

Configuring Kafka Streams For Least Privilege

Published 2018-12-05 by Kevin Feasel

Gwen Shapira explains how we can assign minimal rights to Kafka Streams and KSQL users:

The principle of least privilege dictates that each user and application will have the minimal privileges required to do their job. When applied to Apache Kafka® and its Streams API, it usually means that each team and application will have read and write access only to a selected few relevant topics.

Organizations need to balance developer velocity and security, which means that each organization will likely have their own requirements and best practices for access control.

There are two simple patterns you can use to easily configure the right privileges for any Kafka Streams application—one provides tighter security, and the other is for more agile organizations. First, we’ll start with a bit of background on why configuring proper privileges for Kafka Streams applications was challenging in the past.

Read the whole thing; “granting everybody all rights” generally isn’t a good idea, no matter what your data platform of choice may be.

Comments closed

Working With Firewall Rules From Azure SQL Database

Published 2018-12-04 by Kevin Feasel

Arun Sirpal shows us how we can use T-SQL to set and modify firewall rules within Azure SQL Database:

For this post I want to actually show you the TSQL code to do this, hopefully it will become a good reference point for the future. Before we step into the code lets understand the differences between database level and server level rules.

For server level rules they enable access your entire Azure SQL server, that is, all the databases within the same logical server. These rules are stored in the master database. Database level rules enable access to certain databases (yes you could also run this within master) within the same logical server, think of this as you being more granular with the access where they are created within the user database in question.

Personally, I try and always use database level rules, this is especially true when I work with failover groups.

Click through for instructions on how to work with both server and database level rules.

Comments closed

Understanding Power BI Service Administrator Permissions

Published 2018-12-03 by Kevin Feasel

Melissa Coates walks us through Power BI permissions:

Based on the tests I’ve been doing, I’ve observed that users with membership to the Power BI administrator role have two sets of permissions apply:

  • Activities which are scoped across the entire organization
  • Activities for which normal user permissions apply

Within the above 2 categories, I’m thinking there are 4 main types of activities:

  1. Manage tenant settings (always scoped to the organization)
  2. Compile inventory and metadata (can be scoped to the organization)
  3. Manage workspace users (can be scoped to the organization)
  4. Export content from a workspace (relies on user permissions)

There’s a fair amount to digest, but Melissa does a good job explaining the implications of specific permissions.

Comments closed

Connection Failed With Error 772

Published 2018-11-29 by Kevin Feasel

Jack Vamvas investigates an error when trying to connect to SQL Server 2016 on Windows Server 2016:

Question: I’ve upgraded an application with a built – in Database API . When attempting to establish a SQL Server database connection this error appears – Connection failed – SQL Server Error 772 – TCPIP Socket

Upon investigation the application was using  the native drivers attempting to connect to a SQL Server 2016 \ Windows 2016

As part of the testing I downloaded the ODBC 13.1 SQL Server drivers – independent of the application and tested a DSN connection to the same SQL Server – and it connected OK. I then created a DSN with native drivers and the error reappeared.

What is going on ? How can I fix this issue?

Read on for the solution and keep those drivers up to date.

Comments closed

Operating Management Studio With Multiple Active Directory Accounts

Published 2018-11-20 by Kevin Feasel

Kenneth Fisher shows how to use different Active Directory credentials when using SQL Server Management Studio:

To help promote the seperation of duties one of the things my company has done is to divide our permissions into two accounts. We have one account that is for our daily tasks. Reading email, searching the internet, basic structure changes in a database etc. The other account is our admin account. It’s for remoting to servers, security tasks, really anything that requires sysadmin. I’m not going to argue the advisability of this because honestly, I’m kind of on the fence. That said, I do have to deal with it and there are a few tips in case you have to deal with it as well.

And if you’re not on the domain as well, runas /netonly /user:[domain\username] ssms.exe will do the job.

Comments closed