Press "Enter" to skip to content

Category: Security

Handling Permissions Changes With Powershell

Published 2018-02-05 by Kevin Feasel

Drew Furgiuele has a process to store and then re-run rights grants on SQL Server databases:

Permission requirements for these environments can change over time, just like the code and data going into your databases. It’s hard to track permissions because a database permission is much more than just a user principal; database objects often contain permission definitions for GRANT and DENY states, and users may belong in certain database roles in one environment, but not another. This isn’t a big deal… until it is: sooner or later your data and code drift will be different than production, or maybe some new change really breaks an environment. Then, you’ll be asked to restore these environments to either an earlier version, or, more likely, you’ll be asked to “refresh” these editions to what is currently in production.

You probably already have a process for this, but how are you handling maintaining differences in permissions between environments? Wouldn’t it be nice if you had a way to quickly evaluate, store, and then re-apply permissions as part of refresh? Even better, wouldn’t it be cool if you could do this for all your databases on a given instance? Or what about all your instances in a given environment?

You can, and you can do it pretty easily with PowerShell.

My one problem with Drew’s otherwise-excellent post is that he approved far too many entry visas in the opening GIF.  100% deny, 0 problems.

Comments closed

The Cost Of Downtime

Published 2018-01-31 by Kevin Feasel

Allan Hirt points out a new rule in the UK:

However, as of this week, if you have a company or work in the UK, things just got a whole lot more interesting. The UK government officially released a statement on January 28 which affects “critical industries”. Long story short: if you fall under the classification which seems to be limited right now to energy, transport, water, and health firms, you could be fined up to £17 million ($24 million in US Dollars at today’s exchange rate) in the event of a cyber attack taking you down. It was the WannaCry outages that precipitated the response (as an example, FedEx says WannaCry cost them about $300 million US Dollars). Remember this doozie from British Airways? Also covered under this new Network and Information Systems (NIS) Directive; it’s not just about security, but includes other things like power outages, hardware failure, and environmental hazards.

Read the whole thing.

Comments closed

The Benefits And Risks Of Sharing In Power BI

Published 2018-01-30 by Kevin Feasel

Steve Hughes explains the implications of sharing a Power BI report:

The primary reason to use share is to distribute content outside the context of a Power BI App. Power BI Apps should be your first mechanism for sharing content within your organization. It requires more thought and planning which is typically a good idea with your companies data. However, there are times when sharing makes sense. With the ability to share reports, you can limit sharing to specific areas. Also, you may want to create a “one-off” report for use in decision making but not something to be deployed in the long term.

Sharing is very different from deploying Apps. App deployment is not that difficult to do, but prevents sharing and is much easier to manage access.

Read on for Steve’s thoughts, including his hesitancy toward sharing en masse.

Comments closed

Using Schemas For Database Management

Published 2018-01-26 by Kevin Feasel

Jana Sattainathan explains the benefits of using schemas to segment out functional sections of a database:

My recommendation on how to manage permissions goes like this:

  1. Create Database with appropriate Schemas – like HR/Finance (or) Staging/ETL etc
  2. Create objects like tables and views inside the appropriate Schemas
  3. Create database roles such as db_finance_admin_role, db_developer_role, db_ddl_deployer_role etc
  4. Grant permissions at the Schema level to database roles as shown in the example above
  5. Create AD groups (instead of individual logins) like Finance_DB_Admins, IT_Developers etc
  6. Grant database role membership to AD groups instead of individual logins – EXEC sp_addrolemember N’db_developer_role’, N’IT_Developers’

Doing it this way allows you to separate the concerns. For example the db_developer_role can be granted more or less permissions and all the groups granted that role will automatically get that. Also, you are free to use the AD groups across instances in multiple databases with different permissions.

Click through for more details, including how to get to separate schemas from an all-dbo database.

Comments closed

Don’t Run Services As Root On Linux

Published 2018-01-24 by Kevin Feasel

Kellyn Pot’vin-Gorman explains why running SQL Server as root is a bad idea:

Although enhancements have changed Windows installations for applications to run with a unique user, I created a mssql OS user even back on SQL Server 2000 on Windows as I had a tendency to use similar security practices for all database platforms as a multi-platform DBA.  With that being said-  yes, it introduced complexity, but it was for a reason: users should be restricted to the least amount of privileges required.  To grant any application or database “God” powers on a host is akin to granting DBA to every user in the database, but at the host level.  As important as security is to DBAs INSIDE the database, it should be just as important to us OUTSIDE of it on the host it resides on.

Security is important and has become more complex with the increase of security breaches and introduction of the cloud.  One of the most simple ways to do this is to ensure that all application owners on a host are granted only the privileges they require.  The application user should only utilize SUDO, stick bit, iptables, SUID, SGID and proper group creation/allocation if and when required.

It’s the same reason we don’t recommend giving everyone sa rights to databases.  Read on for more.

Comments closed

Aggregations And Always Encrypted

Published 2018-01-18 by Kevin Feasel

Monica Rathbun finds trouble with Always Encrypted:

The real challenges started when the client began to test their application code. The first thing we hit was triggers.

The table had several insert triggers associated with the columns that were now encrypted. Since the data was now encrypted the insert triggers would fail. Again, we lucked out and they were able to recode somethings in order to remove the triggers. Of course, since troubles always come in threes, this was no different. First the constraint problem, then the triggers, then we hit the biggest road block that halted our Always Encrypted implementation.

Read on for more information about the things you cannot do with Always Encrypted, including some limitations which will eventually go away.

Comments closed

Storing Credentials For Containers

Published 2018-01-18 by Kevin Feasel

Andrew Pruski shows how to store a credential using Powershell and pass it into a Docker container:

I work with SQL Server in containers pretty much exclusively when testing code and one of my real bug bears is that SQL Server in containers does not support Windows authentication (unless you’re using Windocks).

So when I’m working I find it quite annoying to have to specify a SA username & password when I want to connect.

OK, I can use Get-Credential, assign to a variable, and then reference that in a connection string but I want something a bit more permanent especially as I always use the same password for all my containers

Read on for Andrew’s method, and check out Rob Sewell’s method in the comments.

Comments closed

Flexible Active Directory Account Lookup In Powershell

Published 2018-01-15 by Kevin Feasel

Jana Sattainathan builds a flexible AD lookup cmdlet in Powershell:

Now, the problem is that I have to lookup the AD User account for each of these users to add to the AD group “CrisisManagement_ReadOnly_Group”. Although I still use the AD module command Get-ADUser to lookup names, I have to do so one name at a time like this:

Get-ADUser -Filter ‘(name -like “*David*”) -and (name -like “*Smith*”)’

The above method is tedious and time consuming, especially if it is a long list of users. I would rather paste the list that the sender sent me as is into a PowerShell command and auto-magically add the corresponding accounts to the AD group. If we are unable to find an user, report it as an error.

Click through for the code and more.

Comments closed

Service Broker Security

Published 2018-01-15 by Kevin Feasel

Misha Capewell has a quick synopsis of the two flavors of service broker security:

There are 2 types of security in Service Broker: dialog and transport. Dialog security establishes a secure, authenticated connection between Service Broker Services or dialog endpoints. Transport security establishes an authenticated network connection between SQL Server instances or Service Broker endpoints. Clear as mud, right? Don’t worry, these are easily mixed up by both novice and experienced Service Broker admins. To illustrate, let’s go back to our taxes scenario. You’ve completed your forms, stamped your envelope and you’re ready to mail it in. You drop it in your nearest mailbox and what happens next? A postal worker will pick it up, it gets loaded into a truck and shipped between various sorting facilities (as you might have noticed I have no clue how the USPS works) until it is finally delivered to the IRS via yet another postal worker. Now, those postal workers all have the authority to transport your tax return from point to point. However, they do not have the authority to open up and read your return. That’s what transport security is. The IRS agent on the other end, though, he does have the authority to read your return. That’s dialog security.

Click through for more details, including a couple of scripts to help configure each form of security.

Comments closed

Active Directory And ElasticMapReduce

Published 2018-01-11 by Kevin Feasel

Bruno Faria shows how to use AWS’s CloudFormation to extend Active Directory into an AWS ElasticMapReduce cluster and run jobs via Kerberos:

In this example, you build a solution that allows Active Directory users to seamlessly access Amazon EMR clusters and run big data jobs. Here’s what you need before setting up this solution:

  • An AWS account
  • An Amazon EC2 key pair
  • A possible limit increase for your account (Note: Usually a limit increase will not be necessary. See the AWS Service Limits documentation if you encounter a limit error while building the solution.)

To make it easier for you to get started, I created AWS CloudFormation templates that automatically configure and deploy the solution for you. The following steps and resources are involved in setting up the solution:

  1. Create and configure an Amazon Virtual Private Cloud (Amazon VPC).
  2. Launch an Amazon EC2 Windows instance (Active Directory domain controller).
  3. Create an Amazon EMR security configuration for Kerberos and cross-realm trust.
  4. Launch an Amazon EMR cluster with Kerberos enabled and a cross-realm trust configuration.

You can use the AWS CloudFormation templates to complete each step individually, or you can deploy the entire solution through a single step.

Read the whole thing.

Comments closed