Securing SQL Server 2nd Edition is the Microsoft Deal of the Day

I’ve just learned that Securing SQL Server 2nd Edition is the Microsoft Deal of the Day on O’Reilly’s website. This means that O’Reilly is selling the book for 1/2 off today (Feb 11th, 2014) so you can pick the book up for just $24.98! That’s a deal and by far the best sale that I’ve seen. I don’t know what timezone this deal is based on, but I’d buy now rather than later.

Denny

Just Because It Isn’t a Password Doesn’t Mean It Shouldn’t Be Encrypted

Target has done a number on us as customers by having not just customer information but Personally Identifiable Information specifically.

As part of Target's ongoing forensic investigation, it has been determined that certain guest information - separate from the payment card data previously disclosed - was taken from Target. This theft is not a new breach, but was uncovered as part of the ongoing investigation. At this time, the investigation has determined that the stolen information includes names, mailing addresses, phone numbers or email addresses for up to 70 million individuals.

If Target has encrypted all the PII which sat within their database then this data breach wouldn’t have that big a deal because the data which was stolen would have been encrypted and useless to the thieves.  However based on the fact that Target had to announce the breach we are left to assume that the data wasn’t encrypted.

Because someone (probably a developer or project manager) made the decision to store all this PII in plain text instead of taking the time and CPU power to encrypt this information we the customers of Target have to pay the price.  And there is nothing that we can do about this as customers other than not shop there any more, which in reality this isn’t always an option.

As IT workers we need to push our employers and clients to ensure that they are properly encrypting all PII data possible so that the customers and general public aren’t put through this sort of thing.  Pushing to make this happen won’t make us popular with managers, or co-workers as it does add more work to the workflows and more work in general, but this is something which we must start doing.  And I’m not talking about encrypting data when at rest, but actually encrypting the data in the tables so that when an attacker exports the data from the tables using a basic select statement they get useless information, otherwise the entire data encryption process was pointless.

Someone within organizations needs to step up and start bringing this up in meetings.  If you don’t do it, no one will and this sort of massive data theft will happen again.  Just because you don’t work in a large retailer doesn’t mean that you shouldn’t be bringing this up in your company.  EVERY company needs to be thinking about this because you never know how much information the systems will be holding or how these systems will be used in the future, so it’s best to plan for the best now.

Denny

Preventing Problems Like The Target Card Breach Are Easy

Unless you live under a rock you heard about the Target credit card breach that happened between November 27th and December 15th 2013. What really pisses me off about this sort of thing is that it never should have been possible for this to happen.

Our current concept of credit cards and hotargetw they work was designed decades ago when data encryption was basically non-existent outside of government work. Because of this all the data that is needed to steal every penny in your checking account is carried around in plain text in your wallet in the form of your debit card (which if you are in the US probably also functions as a credit card as well). I’ll say it again everything is stored in plain text so that anyone with a magnetic strip reader can simply read the information from the card and use it. This information includes your credit card number, bank information, expiration date, and any other information about you and your account that the bank has decided to put onto the card.

The banking industry has come up with all sorts of security safeguards that they put in place to try and ensure that your credit card information is safe, except encrypt the data on the card so that a thief can’t read it. The rest of the world has evolved their banking and credit card systems, but not the US because we don’t like change. And most importantly the banks like being able to charge merchants extra swipe fees for different kinds of transactions.

 

What we should be using here in the US is a system called chip and pin. The credit card looks exactly like it does today, but instead of swiping your credit card like we do today, instead you insert the card into the reader. The reader prompts you for your pin number, much like when you use your card as a debit card. If the pin that you enter matches the pin which is stored in the chip, then the data is decrypted and the machine charges your card directly, then it simply tells the cash register that the charge was accepted or declined. The credit card information never goes to the stores cash register so the problem that Target had simply wouldn’t have happened. The credit card information is sent to the bank via either a phone line or via the network, but the data is encrypted before it leaves the credit card machine so there is no risk of it being intercepted between the credit card machine and the bank.

One of the big reasons that banks don’t want this to happen is that every transactions now becomes a pin transaction. Banks charge fees for pin card transactions, as do some stores. If every transaction is a pin based transaction the banks (and the stores) will have to stop charging these fees which means that they will loose profit (which is all these fees are, pure profit). So instead of loosing out of some profit the banks instead put our time, and sanity at risk as now about 40,000,000 people will either need to watch their bank accounts daily to ensure that there’s no fraud on their account, or cancel their card (like I did) and not have a debit or credit card for 2-7 days (depending on how long it takes your bank to send you a new card). And don’t forget that this is all happening over Christmas and New Years holidays so having working credit cards is kind of important.

It should be obvious by now that the banks aren’t going to give us a more secure banking system to use. The government needs to step in and mandate that the banks and credit card companies move us to a more secure system and that system should be the same system that the rest of the world is using. The chip and pin system which the rest of the world uses is a well used system that everyone has gotten used to using. Staying with the existing system just isn’t a realistic plan. This breach is going to cost target a small fortune in fines, fees, consulting dollars, etc. as they try and deal with it. Wouldn’t it be nice if it simply wasn’t possible.

Many stores are actually getting ready for these new cards already. You may have seen the new card readers which have a swipe on the side and a slot in the bottom to insert your card. These consoles are the ones that we should be using, just with the swipe option disabled.

There will be some pushback I’m sure because this means change. Yes you will need to remember your pin number. But you probably already have one for your debit cards, and it’s really not that hard for you to remember that pin number. People will need to get used to inserting the card instead of swiping it. You’ll get used to it. The biggest change will be when you go out to eat as the server will now bring the credit card machine to do instead of disappearing with your card as you’ll need to enter your pin on the portable keypad. I admit it takes a little getting used to, but it isn’t that big of a deal. I promise.

The only way this is going to happen is if the federal government requires it. And I really hope that they don’t mandate something different from the rest of the world because that would be the only mistake that’s a bigger one than keeping what we have today.

Denny

Second Edition of Securing SQL Server now longer available for pre-order. It’s Shipping!

I’m afraid that I’ve got some bad news.  You can no longer pre-order Securing SQL Server 2nd Edition from Amazon.

Instead you have to settle for ordering the book outright and having it shipped to you.  That’s right, no more being a pre-order book, it’s published and available to be shipped directly to you.  Currently Amazon is selling the book at full price which is $49.95, but if you have Amazon Prime it is available for Amazon Prime shipping.  Because it is considered to be a text book you get a $5 Amazon MP3 Credit (what ever terms and conditions that Amazon chooses do apply).

This is a totally updated edition of the book including all sorts of new information about security within SQL Server 2012.  I of course cover things like how to secure AlwaysOn Availability Groups, how to use user defined server roles, contained users, etc. I also dive into how to properly secure SQL Server Reporting Services and SQL Server Analysis Services so they can’t be used to access data that people shouldn’t have access to.

All in all this book is much larger with Amazon showing it at 408 pages compared to just 272 pages for the 1st edition.  If you find someone cheaper to purchase it make sure that you are in fact ordering the second edition.  The ISBN number is 1597499471.

I hope that you pick up a copy of the book and that it is useful for you in securing your SQL Server environment.

Denny

Kindle Version of Securing SQL Server 2nd Edition Is Available

In true Amazon style the Kindle Edition of Securing SQL Server 2nd Edition is available for purchase from Amazon before the physical print book is available.  I haven’t even gotten my preview copy yet (it should be here in a day or two) but you can get your digital copy from Amazon as of about a week ago.

So if you’ve been waiting for the 2nd edition to come out, there’s no need to wait any longer.

If you want that physical book you can pre-order it, and hopefully it’ll be shipping within just a couple of weeks.  Amazon has August 15th listed on the US website, but I’m not sure if that is the actual date or not.

Denny

Securing SQL Server 2nd Edition Coming Soon

I’m pleased to be able to announce that the 2nd edition of Securing SQL Server is going to be available soon.  It’s just been made available for pre-order on Amazon.com.  The second edition comes in at about 350 pages (according to Amazon, I don’t actually have a copy of it yet) while the first edition came in at about 270 pages so there has been a LOT of material added to the book.

While a lot of the new information is focused on SQL Server 2012, there is also a lot of new material which relates to older version of SQL Server including chapters on SQL Server Analysis Services and SQL Server Reporting Services, information on Instant File Initialization, EXECUTE AS, Database Firewalls, SAN Security, Actual Data Security (no idea how this got missed the first time around, but that’s to Brent Ozar for pointing it out).

As far as the SQL Server 2012 information you’ll find updated information about the SHA2 hashing algorithms, Securing AlwaysOn Availability Groups, Security and SQL Server Clustering, Security and Contained Databases and a lot more.

If you already have a copy of the 1st edition I encourage you to take a look at the second edition as well.  I know that it’s really soon for a second edition of a book (the first edition just came out February 2011, but this new edition comes on the release of SQL Server 2012.

Hopefully you pre-order you copy today.

Denny

P.S. Yes this edition will be available for the Kindle as well, that takes a little time.  As soon as I know that it’s been posted for the Kindle (usually happens a little after Amazon gets the physical books) I’ll post another announcement here.

P.P.S. If you visit my SecuringSQLServer.com site I’ve updated everything there for the new edition.  You can always find the old edition listed on the Other Books page on that site or on the Books page on mrdenny.com.

180k+ websites attacked because of bad dev code

There is another massive SQL Injection attack going around. This time hitting 180k ASP.NET websites.  The article which I referenced has a decent write up on the actual attack and it links to a post which has detailed information about the attack.  However the article on IT World gives some really poor advice on how to protect yourself from a SQL Injection attack.

There’s no easy way to fix the vulnerability of the database to this attack except to “harden” the database by applying all the patches and making all the security requirements consistent. Monitoring the database for unusual activity is important, too.

Patching SQL Server will NOT prevent SQL Injection attack, at all.  The SQL Server isn’t the attack vector for a SQL Injection attack, the web application is the attack vector.  By the time the SQL Injection attack gets to the SQL Server database (or any database) it’s too late.

SQL Injection is actually really easy to protect yourself from.  Simply stop using dynamically generated SQL  and instead start using parametrized queries (also called bound queries).  That’s it, that’s the big secret.  Yes I understand that writing your .NET code as parametrized queries is harder to write than just doing string concatenation and running the query, but getting your site attacked and putting malware on your customers computers because you didn’t want to do a little typing is just no excuse.

As this is a blog about my book “Securing SQL Server” here’s the sales pitch.  In the book I talk all about how to use parametrized queries.  It really isn’t that hard there is lots of sample code on how to do it.  You don’t need to use stored procedures to use parametrized queries.  You can do it with normal dynamic SQL as well, it works basically the same.

In case you didn’t get my point yet, parametrized queries are the ONLY WAY that you can 100% be sure that you are protecting yourself from SQL Injection attacks.  If you can’t find some links on how to use parametrized queries here are a few links for you PHP, .NET, and more .NET.

Denny

Exposing SQL Server to the public Internet is a pretty bad idea

Every once and a while we hear about the nightmare situation where a SQL Server has been broken into and data has been stolen. All to often when this happens because the SQL Server is exposed directly to the public Internet. When you ask people why the SQL Server is connected to the Internet the answer is pretty much always the same, to make it easier to manage so that they don’t have to RDP to the server and manage it from there.

While this is easier, is sure isn’t the safest solution. A much better solution would be to setup a VPN Network between the office and the data center so that the connection is secured so that people from the public Internet can’t access the SQL Server’s connection.  This will prevent people who aren’t supposed to be connecting to the SQL Server from connecting to the SQL Server.

I talk about this more in Chapter 1 of “Securing SQL Server”.  Check it out on Amazon, which will actually let you read a good portion of Chapter 1 online for free.

Meet the author, get your copy signed

There are several chances to meet the author of Securing SQL Server, ask questions, and get your copy of the book signed.  These include (and are subject to change):

Dallas Tech Fest – August 13th, 2011

SQL PASS – October 11-14, 2011

SQL Saturday 95 – September 17th, 2011

SQL Excursions - May 17-19, 2012

I hope to see you at one (or all) of these great events.  Feel free to bring your copy and get it signed making it a priceless collectable (OK, probably not but I do love signing copies).

Denny

Securing SQL Server will be available at the Tech Ed bookstore

If you are going to Tech Ed 2011, and you were thinking about purchasing a copy of “Securing SQL Server” but you wanted to thumb through it before purchasing, now is your chance.  I’ve just be told that the Tech Ed book store will be stocking “Securing SQL Server”.  If you pick up a copy at Tech Ed and you want it signed I’ll be working at the SQL Server booth most afternoons.  Bring it on by, and I’ll be happy to sign it for you or at least draw funny faces in it.

Denny