Press "Enter" to skip to content

Category: Security

The Risk Of Opening Your SQL Server To The Internet

Published 2018-03-26 by Kevin Feasel

Max Vernon explains why it’s a bad idea to let your SQL Server be Internet-accessible:

First, it might be helpful to understand why someone might want to connect to their SQL Server via the Internet. Consider any of the following likely scenarios:

  • There is a client application or website hosted in a different data-center that needs access to the data stored in the SQL Server.
  • You need to provide geographical redundancy between several SQL Servers located in different countries that are only connected via the Internet.
  • You’re doing development on a hosted SQL Server and don’t understand the implications of having SQL Server exposed.

The typical way you might expose your SQ Server to the Internet when the server is behind a router or firewall is to open an incoming TCP port at the router or firewall directed at the SQL Server on port 1433. If you do this, you will very quickly start seeing incoming connection attempts from computers on the Internet. These attempts will try to login, typically via the “sa” account, continuously trying passwords.

It’s best to avoid this mess altogether, though if you do need to expose your SQL Server to the Internet, Max has tips on how to mitigate some of that risk.

Comments closed

Sharing Power BI Content Via E-Mail

Published 2018-03-23 by Kevin Feasel

Steve Hughes looks at the security implications of being able to share Power BI reports through e-mail:

My account does not have Power BI Pro, but now I can try it for free for 60 days and get access to the data while I am on the trial. I clicked both options, because I can. The Upgrade account option would require me to pay for Pro. However, Try Pro for free works and I was able to access the report fully. I have successfully shared my corporate content with a personal user.

Steve shows us where you can go to disable this if you want, as well as places where you can see what content has been shared.

Comments closed

Finding Where Power BI Local Credentials Get Stored

Published 2018-03-23 by Kevin Feasel

Eugene Meidinger hunts down where those local Power BI credentials live:

With SSIS, you have to be careful to export the SSIS files without any sensitive information included. But what about Power BI? If you save the .PBIX files on OneDrive, can you be exposing yourself to a security risk?

Looking at things, it looks like credentials for data sources are stored globally, so one wouldn’t expect them to be in the .pbix files.

Read on as he does some more sleuthing and discovers the answer.

Comments closed

Remote DAC And Vulnerability Assessments

Published 2018-03-21 by Kevin Feasel

Max Vernon points out a SQL Server Management Studio Vulnerability Assessment check which seems somewhat incomplete:

Certainly, you’d want to ensure the port for the DAC is not available to the Internet, but hopefully if you’re reading this blog you already know how silly it would be to open SQL Server to the Internet.

Assuming you don’t have the port open to the Internet, it’s very likely the DAC will not be of any use at all if you disable Remote Admin Connections as advised in the Vulnerability Assessment. My advice is to ignore this warning completely and configure the DAC to allow remote connections. Microsoft Technet has documentation about using the DAC, and says to configure it for remote connections by logging onto the server locally first, then configuring SQL Server to allow remote DAC connections, which seems a bit like putting the cart before the horse.

Read the whole thing.  I agree with Max’s assessment that if there are some basic controls around your instance (like not letting SQL Server be Internet-accessible, putting SQL Server instances in a protected subnet, etc.), remote DAC is definitely useful enough to keep running.

Comments closed

For GDPR, Don’t Forget Query Monitoring Tools

Published 2018-03-20 by Kevin Feasel

Grant Fritchey points out another spot that might store personal information:

When you capture query metrics through trace events or extended events, either using rpc_completed or sql_batch_completed, you not only get the query. You also get any parameter values associated with that query. Article 17 of the GDPR is extremely clear:

The data subject shall have the right to obtain from the controller the erasure of personal data concerning him or her without undue delay and the controller shall have the obligation to erase personal data without undue delay…

While there are a list of exceptions to the definitions of Article 17 listed at the link, none of those is because the data isn’t in the database or is stored in some separate information store such as your monitoring of queries. Instead, the GDPR pretty much says that any place (SharePoint, Excel, etc.) that the data resides, must be documented as part of your processing and is subject to control through the Regulation.

Read the whole thing.

Comments closed

Data Lake Permissions

Published 2018-03-20 by Kevin Feasel

Melissa Coates has started a multi-part series on Azure Data Lake permissions.  She’s put up the first three parts already.  Part 1 covers the types of permissions available as well as some official documentation:

(1) RBAC permissions to the ADLS account itself, for the purpose of managing the resource.
RBAC = Role-based access control. RBAC are the familiar Azure roles such as reader, contributor, or owner. Granting a role on the service allows someone to view or manage the configuration and settings for that particular Azure service (ADLS in this case). See Part 2 for info about setting up RBAC.

Part 2 looks at permissions for the Azure Data Lake Store service itself:

Setting permissions for the service + the data stored in ADLS is always two separate processes, with one exception: when you define an owner for the ADLS service in Azure, that owner is automatically granted ‘superuser’ (full) access to manage the ADLS resource in Azure *AND* full access to the data. Any other RBAC role other than owner needs the data access specifically assigned via ACLs. This is a good thing because not all system administrators need to see the data, and not all data access users/groups/service principals need access to the service itself. This type of separation is true for certain other services too, such as Azure SQL Database.

Try to use groups whenever you can to grant access, rather than individual accounts. This is a consistent best practice for managing security across many types of systems.

Part 3 covers using ACLs to grant rights to specific files or folders in Azure Data Lake Storage:

There are two types of ACLs: Access ACLs and Default ACLs.

An Access ACL is the read/write/execute permissions specified for a folder or file. Every single folder or file has its security explicitly defined — so that means the ADLS security model is not an ‘inheritance’ model. That is an important concept to remember.

A Default ACL is like a ‘template’ setting at a folder level (the concept of a default doesn’t apply at the file level). Any new child item placed in that folder will automatically obtain that default security setting. The default ACLs are absolutely critical, given that data permissions aren’t an inheritance model. You want to avoid a situation where a user has permission to read a folder, but is unable to see any of the files within the folder — that situation will happen if a new file gets added to a folder which has an access ACL set at the folder level, but not a default ACL to apply to new child objects.

There’s a lot of good information here and I’m looking forward to parts 4 and 5.

Comments closed

Protecting Database Assets From Administrators

Published 2018-03-19 by Kevin Feasel

Louis Davidson walks through which things are granted to administrators of different levels:

Own is a strange term, because really there is just one user that is listed as owner, but there are there are three users who essentially are owner level, super-powered users in a database:

1. A login using server rights, usually via the sysadmin server role (or a server permission to view all data)
2. The user dbo in a database, acquired either as a sysadmin, or as being the user listed as the owner of the database
3. Members of the db_owner database role

Sometimes, in the context of a database, these all start to blur together. But they are definitely all three independent things. Let’s write some code and see the differences, and one of the cases may be surprising to you. To do this, I will just be using the SELECT permission on a single table, but other rights will generally behave similarly. Note that another tool in your toolbox is Row Level Security in SQL Server 2016+. It is different, in that you can include a predicate that excludes the dbo, which you can read more about here in some of my earlier blogs.

Great read.

Comments closed

The Basics Of Kafka Security

Published 2018-03-15 by Kevin Feasel

Stephane Maarek has a nice post covering some of the basics of securing an Apache Kafka cluster:

Once your Kafka clients are authenticated, Kafka needs to be able to decide what they can and cannot do. This is where Authorization comes in, controlled by Access Control Lists (ACL). ACL are what you expect them to be: User A can(‘t) do Operation B on Resource C from Host D. Please note that currently with the packaged SimpleAclAuthorizer coming with Kafka, ACL are not implemented to have Groups rules or Regex-based rules. Therefore, each security rule has to be written in full (with the exception of the * wildcard).

ACL are great because they can help you prevent disasters. For example, you may have a topic that needs to be writeable from only a subset of clients or hosts. You want to prevent your average user from writing anything to these topics, hence preventing any data corruption or deserialization errors. ACLs are also great if you have some sensitive data and you need to prove to regulators that only certain applications or users can access that data.

He also has links to additional resources, which is helpful when you want to dig in further.

Comments closed

Flushing The Authentication Cache

Published 2018-03-15 by Kevin Feasel

Arun Sirpal describes an Azure SQL DB-only DBCC command:

This command only applies to Azure SQL Database, at a high level it empties the database authentication cache for logins and firewall rules for the current USER database.

In Azure SQL Database the authentication cache makes a copy of logins and server firewall rules which are in the master database and puts them into memory within the user database. The Database Engine attempts re-authorisation using the originally submitted password and no user input is required.

If this still doesn’t make sense, then an example will really help.

Click through for the helpful example.

Comments closed

Linking Azure VMs To An On-Prem Domain

Published 2018-03-13 by Kevin Feasel

Denny Cherry explains how to integrate Azure VMs with your existing Active Directory domain:

The first step is to put some domain controllers in Azure.  To do this, you’ll need a site to site VPN between Azure and your on-premises environment.  If you have multiple on-premises sites, then you’ll want to create a VPN between Azure and all your on-premises environments.  If your Azure environment is hosted in multiple regions, then you’ll want to create a mesh network when each on-premises site in VPNed into all of your vNets.  You’ll probably also want your vNets VPNed to each other (Peering of your networks between sites may be an option as well depending on how you’ve set things up).  If you have an extremely large number of users at your site, then Express Route might be something worth looking into instead of a site to site VPN.

Click through for the full process.

Comments closed