Securing Plaintext Passwords

John Morehouse shows you how to fix plaintext password storage when you can’t fix the application:

Once the data has been encrypted, we can move forward with creating a new view that will be used to “head fake” the application. The view is named the same as the original table therefore the change is seamless to the application.  The application doesn’t know if it’s calling a table or a view so that’s why this works.

You should never store passwords in plaintext.  You should almost never store passwords in a reversable format (i.e., encrypted).  The primary case in which I can see storing passwords encrypted rather hashed is if you have automated systems (or non-automated technicians) which need passwords to authenticate somewhere.  Even then, there’s a lot of value in using OAuth tokens.  But if you can’t get around any of this, John’s solution does remove the really bad decision of leaving passwords in plaintext.

HIBPwned

Kevin Feasel

2016-03-25

R, Security

Steph Locke has created an R package to query Troy Hunt’s Have I Been Pwned? site:

The answer in life to the inevitable question of “How can I do that in R?” should be “There’s a package for that”. So when I wanted to query HaveIBeenPwned.com (HIBP) to check whether a bunch of emails had been involved in data breaches and there wasn’t an R package for HIBP, it meant that the responsibility for making one landed on my shoulders. Now, you can see if your accounts are at risk with the R package for HaveIBeenPwned.com, HIBPwned.

This is a nice confluence of two fun topics, so of course I like it.

Encrypt Your Connections

John Martin shows that part of securing your environment includes encrypting SQL Server connections:

In order to demonstrate just how easy it can be to get hold of the information inside the TDS packets I will be using Network Monitor from Microsoft, this will capture the network packets sent and allow me to see the details of what is being sent. Other tools such as Wireshark will also provide a level of insight into what is being sent between the application and SQL Server. I have configured three Windows Server 2012 R2 systems, one with the client (SQLCMD), one with SQL Server, and finally one which will act as a router between the two subnets that each server is on. This configuration can be seen below;

Wireshark is a good friend of mine.  It should be a good friend of yours, too, but only if your environment allows you to have a packet capture tool installed.

Querying Active Directory From SQL Server

Ryan Adams shows us how to use OPENROWSET and OPENQUERY to connect to a domain controller and query Active Directory using LDAP:

In the code below, the first thing we do is enable Ad Hoc Distributed Queries so we can try out the OPENROWSET method.  The advantage to this method is not having a linked server and being able to call it directly out of TSQL.  Once we have that enabled we write our query and you’ll notice that we are essentially doing 2 queries.  The first query is the LDAP query inside the OPENROWSET function.  Once those results are returned we are using another query to get what we want from the result set.  Here is where I want you to stop and think about things.  If my LDAP query pulls back 50 attributes, or “columns” in SQL terms, and I tell it I only want 10 of them, what did I just do?  I brought back a ton of extra data over the wire for no reason because I’m not planning to use it.  What we should see here is that the columns on both SELECT statements are the same.  They do not, however, have to be in the same order.  The reason for that is because LDAP does not guarantee to return results in the same order every time.  The attribute or “column” order in your first SELECT statement determines the order of your final result set.  This gives you the opportunity to alias anything if you need to.

You can query LDAP using SELECT statements, but the syntax isn’t T-SQL, so in my case, it was a bit frustrating getting the data I wanted out of Active Directory because I was used to T-SQL niceties.  Nevertheless, this is a good way of pulling down AD data.

R And SSH Tunnels

Kevin Feasel

2016-03-16

R, Security

Steph Locke shows how to set up an SSH tunnel to connect to an external server within R:

Whilst down the rabbit hole, I discovered just in passing via a beanstalk article that there’s actually been a command line interface for PuTTY called plink. D’oh! This changed the whole direction of the solution to what I present throughout.

Using plink.exe as the command line interface for PuTTY we can then connect to our remote network using the key pre-authenticated via pageant. As a consequence, we can now use the shell() command in R to use plink. We can then connect to our database using the standard Postgres driver.

PuTTY is a must-have for any Windows box.

Windows Authentication On Linux

Ryan Adams shows us how to get Active Directory authentication on Linux using Samba and PAM:

Back to our question at hand.  Since the preview just got released I have not had a chance to test this out with SQL Server.  However, you can use Active Directory accounts with Linux and Unix by using Samba and PAM.  I see no reason why this implementation would not also work with SQL Server on Linux, but again I have not yet had a chance to test this out.  Since I already have some written installation instructions, that’s what I am providing here.

Chrissy LeMaire, in the comments, shows how to do it in OpenSUSE.  She also has a brand new blog post on the topic.

Granting Permissions In AGs Or On Mirrors

Matan Yungman discusses how to grant permissions only to the replica database in a mirroring or Availability Groups scenario:

You work with Database Mirroring or AlwaysOn AG, and you want to make sure your end users work only on the secondary server. How should you do that?

This solution feels a little hacky to me.  There’s enough value in it that I could see companies doing this, but it’d be nice if there were an easier way.

Permissions To Create Stored Procedures

Kenneth Fisher shows the permissions necessary to create a stored procedure:

The user still won’t be able to create procedures or views. And if you think about it in the right way it makes sense. While the CREATE permissions give us the ability to create objects what they don’t give us is a place to put them. Schemas are containers for most of the objects in databases. So in order change the schema (by putting an object in it) we need to grant ALTER on it. So for the CREATE to work we need to:

Getting the right granularity for permissions is a vital part of securing a SQL Server instance.

Service Account Password Changes Without Rebooting

Andrea Allred shows that you can change the password of a service account without restarting an instance:

Did you know that you can change the password on the SQL Service account that is running your SQL instance without a reboot or restart?  Turns out this is true.  We have a new round of password requirements and it means that we need to change passwords on servers more often. But, since we need our servers up and reboots have to be heavily planned, we needed a solution that kept us from having to restart an instance after a password change. This lovely msdn article explains all the details, but let me give you the cliffs notes.

This is helpful for those max uptime scenarios where even a momentary service restart requires planned downtime.

Azure SQL Database Security

James Serra has a number of links to Azure SQL Database security features:

Life we be so much easier if we could just trust everyone, but since we can’t we need solid security for our databases.  Azure SQL Database has many security features to make you sleep well at night:

Most of these are exactly the same as the on-premise product—at least the SQL Server 2016 version—but it goes to show that Azure SQL Database has grown up quite a bit.

Categories

July 2017
MTWTFSS
« Jun  
 12
3456789
10111213141516
17181920212223
24252627282930
31