Press "Enter" to skip to content

Category: Security

sp_help_revlogin

Published 2016-06-16 by Kevin Feasel

Chrissy LeMaire wants to deprecate sp_help_revlogin:

Now you’ve migrated the logins with their passwords, SIDs, and a few default properties. But you don’t have the logins’ server roles, server permission sets, database roles or database permission sets. So now you gotta find and use someone’s modified version of sp_help_revlogin, but you’re still left with manually executing the procedure against your source and destination servers.

Oh, and don’t forget different versions of SQL Server use different hashing algorithms, so you’ll need to use one of the many different versions of sp_help_revlogin if you have a mixed environment.

Let’s hope you only have one or two SQL Servers to migrate and not hundreds.

Chrissy has a couple of great Powershell cmdlets to help get rid of this procedure, as well as a nice explanation of each and Youtube videos should you be so inclined.  Definitely check it out, as well as her dbatools Powershell suite.

Comments closed

PCI Compliance

Published 2016-06-16 by Kevin Feasel

Denny Cherry reminds us that SQL Server 2005 is no longer PCI compliant:

If you are running a PCI compliant system on SQL Server 2005 you are going to fail your next audit. One of the audit requirements is that the vendors must support the version of your software which you are running on. As Microsoft no longer offers support for SQL Server 2005 that’s going to cause you to fail your next PCI audit.

Microsoft’s serious about sunsetting old versions of SQL Server, and at this point, there have been five versions of SQL Server released since 2005.

Comments closed

Row-Level Security With Reporting Services

Published 2016-06-14 by Kevin Feasel

Paul Turley discusses combining row-level security, SQL Server Reporting Services, and SQL Server Analysis Services:

In every data source connection string, you can add a simple expression that maps the current Windows username to the CUSTOMDATA property of the data source provider.  This works in SSRS embedded data sources, shared data sources, in a SharePoint Office Data Connecter (ODC) file and in a SharePoint BISM connection file.  In each case, the syntax should be the similar.  Here is my shared data source on the SSRS 2016 report server

This is pretty snazzy.  Paul goes into good detail on the topic, so read the whole thing.

Comments closed

Azure Key Vault Connector Available

Published 2016-06-14 by Kevin Feasel

Rebecca Zhang notes that Azure Key Vault is now available to all:

When using these SQL encryption technologies, your data is encrypted with a symmetric key (called the database encryption key) stored in the database. Traditionally (without Azure Key Vault), a certificate that SQL Server manages would protect this data encryption key (DEK). With Azure Key Vault integration for SQL Server through the SQL Server Connector, you can protect the DEK with an asymmetric key that is stored in Azure Key Vault. This way, you can assume control over the key management, and have it be in a separate key management service outside of SQL Server.

The SQL Server Connector is especially useful for those using SQL Server-in-a-VM (IaaS) who want to leverage Azure Key Vault for managing their encryption keys. SQL IaaS is the simplest way to deploy and run SQL Server, and it is optimized for extending existing on-premises SQL Server applications to the cloud in a hybrid IaaS scenario, or supporting a migration scenario.

Read on for more details.

Comments closed

Password-Limiting Moves

Published 2016-06-13 by Kevin Feasel

Brian Kelley is not happy that Office 365 limits password lengths:

I blinked when I saw the warning, “Your password can’t be longer than 16 characters.” I couldn’t believe that I had gotten that warning, so I erased what I had typed for a password and started typing 1, 2, 3, etc., to see if this warning did trip at 17 characters. It did. Why in the world is there a limitation on password length if you’re going to do a hash my password? And if you had to pick a limit, why 16 characters? Why not 50 or 100 or 255?

I’ll go one step further:  there is never a good limit to how long a password should be.  For services like these, Microsoft should have the plaintext version of the password (which again, should be a string of an arbitrary length) only enough to perform an adequate number of rounds of hashing and salting using an appropriate hashing function (e.g., bcrypt).  At that point, once the password gets hashed, the hash is always the same length, meaning the length of the plaintext is irrelevant for storage.

Comments closed

Skirting Around Dynamic Data Masking

Published 2016-06-10 by Kevin Feasel

Phil Factor gives a trivial method of subverting dynamic data masking:

Dynamic data masking is a great product and solves some niche problems that come if you need to do certain testing with live data at the application level. You should, however, beware of using it as a database-level security device.

I haven’t yet used it in testing because I don’t have the problem that it solves.

The problem that it solves is for people doing testing, especially user-acceptance testing, of an application using live data. It is good at masking data where the user is unable to make direct access to the database to execute queries.

Phil has code to get around credit card numbers, and I will say that he’s not the first person I’ve seen do this.  Dynamic Data Masking is not a general-purpose security solution.

Comments closed

HIBPwned

Published 2016-06-10 by Kevin Feasel

Steph Locke has a new CRAN package out:

HIBPwned is a feature complete R package that allows you to use every (currently) available endpoint of the API. It’s vectorised so no need to loop through email addresses, and it requires no fiddling with authentication or keys.

You can use HIBPwned to do things like:

  1. Set up your own notification system for account breaches of myriad email addresses & user names that you have

  2. Check for compromised company email accounts from within your company Active Directory

  3. Analyse past data breaches and produce charts like Dave McCandless’ Breach chart

The regular service is extremely useful and Steph’s wrapper looks like it’s worth checking out.

Comments closed

Finding Malicious Domains

Published 2016-06-08 by Kevin Feasel

Rafael San Miguel Carrasco uses dimensionality reduction to figure out if a domain is malicious:

Dimensionality reduction is a common techique to visualize observations in a dataset, by combining all features into two, that can then be used to draw the observation in an scatter plot.

One popular algorithm that implements this technique is PCA (Principal Components Analysis), which is available in R through the prcomp() function.

The algorithm was applied to observations of sthe dataset, and ggplot2’s geom_point() function was used to draw the results in a 2D chart.

I would want to see this done for a couple hundred thousand domains, but I do like the idea of taking advantage of statistical modeling tools to find security threats.

Comments closed

Getting Started With Security Analytics

Published 2016-06-07 by Kevin Feasel

Michael Schiebel has an introduction to the thought process behind security analytics:

Now, we’re getting somewhere.  Looking at this graph we see we have four high-level problems we are trying to solve.

  1. (Unknown/Unknown) The first step in realizing that we have a problem is accepting that we may not have the answer.  We may not have the right mental or computational models; or even the right data to find bad things.

  2. (Known/Unknown) We’ve invested time and energy brainstorming what could happen, sought out and collected the data we believe will help, and created mental and conceptual models that SHOULD detect/visualize these bad things.  Now, we need to hunt and seek to see if we’re right.

  3. (Unknown/Known) We’ve been hunting and seeking for some time tuning and training our analytical models until they can automatically detect this new bad thing. Now we need to spend some time formalizing our response process to this new use case.

  4. (Known/Known) Great, we’ve matured this use case to a point that we can trust our ability to detect; maybe even to the point of efficient rules/signatures.  We have mature response playbooks written for our SOC analysts to follow.  Now we can feel comfortable enough to design and implement an automated response for this use case.

I think his breakdown is correct, and also would reiterate that within any organization, all four zones come into play, meaning you have different teams of people working concurrently; you’ll never automate away all the problems.

Comments closed