SQL Server with Mr. Denny


August 15, 2012  2:00 PM

Old Web Based Applications Need To Be Removed

Denny Cherry Denny Cherry Profile: Denny Cherry

What happens to most obsolete web based applications at most companies?  They sit idle on a web server for months, sometimes years.  Why is this a problem? Because many of these old applications can be easily exploited via SQL Injection allowing access into the SQL Server databases which they connected to.  The reason that these old apps are a great way into the SQL Server is because they are old, and were probably written before things like SQL Injection protection became more common place.

This tweet from Daniel (@DaniSQL) is a perfect example of this.

An old application that isn’t being used anymore is still available on the Internet facing web farm.  Because this application isn’t being used any more it wasn’t on any lists of deployed applications, so when security audits were done it wasn’t seen as it wasn’t listed as an active application.  However it was apparently able to provide a hacker with a way into the database because it was still connected to a SQL Server instance and it was susceptible to SQL Injection.

The solution to this problem is sadly easy, remove the web based application from the web farm as the application isn’t being used anywhere.  It’s a lot easier than fixing the application, and a whole lot cheaper (10 minutes of a system administrator’s time versus weeks or months of a developers time).

I urge you to audit the applications and websites which are deployed to your web farms, especially the Internet facing web farms and see what’s on there.  When you audit them, don’t audit them against the list of what’s there.  Actually dig into the IIS config of each and every server (yes I’m well aware that doing this sucks) and actually see what’s configured on each machine.  If you don’t know if an application is actually being used ask around.  If it isn’t, remove it (or at least stop the site in IIS) so that you don’t have to worry about scripts breaking into your database and updating your data.

Now thankfully this current attack which is going around is just updating data, but it could easily enough be changed by the attacker to gather data as well, so do yourself a favor and protect yourself.

Denny

August 10, 2012  5:06 PM

Recommended reading from mrdenny for August 10, 2012 at 10:02AM

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find them as useful as I did. Denny


August 10, 2012  2:00 PM

Full Day Storage and Virtualization Class at SQL Saturday 145

Denny Cherry Denny Cherry Profile: Denny Cherry

On October 12th 2012 I’ll be presenting an all day session titled “Storage and Virtualization for the DBA” as a pre-con session for SQL Saturday 145 in Nashville, TN (links to all four pre-cons can be found on the SQL Saturday 145 page).  This popular pre-con session has been presented in locations like the SQL PASS Summit, SQL Day 2012 in Poland, SQL Bits X in London, SQL Saturdays in Florida and California and now the session is coming to Nashville, TN.

This session will be a two part session in which we will be focusing on two of the biggest topics in the DBA field.  How to properly design your SAN storage solution and how to properly design your virtualization solution.

The storage portion of this session will focus on SAN storage, but most of the material will apply to direct attached storage as well.

In the first half of the session we’ll be focusing on the storage array.  Storage can be one of the biggest bottlenecks when it comes to database performance.  It’s also one of the hardest places to troubleshoot performance issues because storage engineers and database administrators often do not speak the same language.  In this session, we’ll be looking at storage from both the database and storage perspectives.   We’ll be digging into LUNs, HBAs, the fabric, as well as the storage configuration.

After going over the components we’ll dig into some advanced storage configurations.  This includes RAID groups, multi-pathing software, and proper redundant storage network design.  We will also be digging into some advanced storage array backup techniques including taking storage level clones and snapshots.  After going over these advanced techniques we will dig into how these can best be used to backup the SQL Server environment to provide maximum redundancy with no recurring tape costs.

In the second half of the day we’ll be looking into the pros and cons of moving SQL Servers into a virtual server environment.  Specifically we’ll be looking into when it’s a good idea and when it’s probably not a good idea.  Like everything in the database world there are no hard set answers as to if virtualization is a good idea or not, but there are some times when virtualizing a SQL Server is a good idea, and can save you some money.  There are some other times when you will be shooting yourself in the foot and virtualization isn’t a good idea.  We’ll be focusing on when how to make this decision, and how to gather the metrics that you need in order to come to this decision.

We’ll look into how tie the virtual platforms to the storage array so that you can maximize the storage performance for your SQL Servers and the virtual environment.

The session is priced at $129.95, but through the end of August you can sign up for just $99.95.

I hope to see you there,

Denny


August 9, 2012  2:30 PM

SSWUG Virtual Conference Registration Is Now Open

Denny Cherry Denny Cherry Profile: Denny Cherry

It’s that time of year again, it is time for the SSWUG virtual conference.  There is a great set of speakers for this virtual conference including myself, Kalen Delaney, Eric Johnson, Kevin Kline, Brian Knight, Lynn Langit, Bill Pearson and Jason Strate and more.  Every one of these speakers is a top notch speaker but most are also very good friends of mine and I can say for sure that this virtual conference will be time and money well spent.

One of the great things about these virtual conferences is that you don’t need to travel to attend.  Sessions can be watched from home or work, all you need is a computer and a set of speakers (or headphones).  Looking over the schedule, there are going to be a lot of really good SQL Server 2012 sessions on the schedule.

Now when you go and register for the virtual conference (it only costs $159 for three days of great training material) be sure to use VIP Code VCDENNY (you have to click the “Update Registration” button for the code to take).  So stop stalling and get registered.  There’s another bonus when you register for the virtual conference, because there is so much great material which will be available, you may not be able to see all the sessions you want to the day of the virtual conference.  Because of that you can view the recordings of the sessions for 30 days after the conference ends for free.

I’ll see you at the conference (in the chat rooms at least),

Denny


August 9, 2012  2:00 PM

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

Denny Cherry Denny Cherry Profile: Denny Cherry

In case you missed the blog post over on securingsqlserver.com, I wanted to repost it here…

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


August 6, 2012  10:00 AM

Vendor Apps and AlwaysOn Availability Groups

Denny Cherry Denny Cherry Profile: Denny Cherry

By now hopefully everyone knows about AlwaysOn Availability Groups in SQL Server 2012 and the high availability options that they provide to databases.  One problem with AlwaysOn Availability Groups when combined with third party applications is that the third party application may configure the connection string and not allow you to change it.  When you want to install one of these third party applications within a SQL Server 2012 availability group this can give you some problems as typically you would typically create the database then add the database to the availability group.  However this requires that you change the connection string which in this case we are trying to avoid.

There however is some good news to this little problem.  The good news in this case is that you can create a SQL Server availability group, which has an availability group listener, without putting any databases in it.  This is done by creating the availability group without using wizard that is available within SQL Server Management Studio.  Instead of starting the wizard select the “New Availability Group…” option from the Availability Group context menu as shown below.

This will allow you to create an Availability Group without any availability groups and with only a single replica.  Once the availability group is created, the listener can be created for the availability group.  The 3rd party application can then use the listener to connect to the database engine and create the database.  The database once created can be added to the availability group as can the additional replicas.

While using this technique is a lot harder than going through the wizard as it requires that the database backups and restores be done manually and the configurations be all done by hand instead of the handy wizard it’ll meet the requirements of the application which is to not change the connection string.

Denny


August 2, 2012  4:00 PM

Sensitive Data Must Be Encrypted

Denny Cherry Denny Cherry Profile: Denny Cherry

The title of this post pretty much says it all.  If you store sensitive data in a database you have to work under the assumption that someone is going to try and break into the system and steal that data.  Thinking otherwise simply isn’t responsible as the developer and/or administrator of the system.  By not encrypting your sensitive data, such as users logins and passwords you could easily enough end up like Yahoo! did on July 11, 2012 with the usernames and passwords of all of the customers of a service being posted on the Internet for all to see.

Not only was this breach a major embarrassment for Yahoo! but it is a potential nightmare for their customers.  If those customers (there were a few hundred thousand in the list) use the same email address and password on other websites they’ve now had the username and password for those other services leaked as well.

Now I know that best practice for Internet security says that every website should have a different password, but for the bulk of Internet users this simply isn’t going to happen.  Among IT professionals the percentage of people that actually use a different password for each website is probably pretty close to zero.  I know that I personally use dozens of different websites a month, and for most people that it probably pretty normal between banks, credit card companies, Facebook, Twitter, work sites, Gmail, etc. that quickly gets up to dozens or hundreds of passwords which need to be remembered.  There are plenty of password vault type applications, but general Internet users aren’t going to be using them.  As IT professionals we need to remember that we are dealing with the general public and the general public isn’t going to know that they need to do this, no matter how many times we talk about it within the IT field.

One reason that there is lots of unencrypted data out there is that converting older applications from using plain text data to encrypted data is pretty hard to do.  There are lots of places within the application which need to be touched and there are possibly lots of different applications which need to be updated all at once.  Then there is the possibility of needing to take an outage to do the actual data change.  When it comes down to is biting the bullet and taking the outage and making the change.  It is well worth it to take the outage and encrypt all the data now, rather than have to worry about a data breach later.

There are lots of techniques which you can use to do this data encryption, to many to list in a single blog post so look for blog posts from me later on how to handle this change.  There are also plenty of consultants, including myself, who are happy to help with projects like this.

Denny


July 30, 2012  4:00 PM

Why is SQL Injection still a problem?

Denny Cherry Denny Cherry Profile: Denny Cherry

SQL Injection is probably the most popular attack vector for hackers when they attempt to break into databases.  The reason for this is that it is so easy for an attacker to gain access to the system, and typically to get pretty high level permissions to a database engine so that they can then export some of all of the data from the database engine.

The really sad thing about this is that it is very easy for software developers to protect against SQL Injection attacks.  The way that software developers protect the application from SQL Injection is by using parameterized queries instead of the older, and usually easier technique of simply building the database query using variables in the software code.

One of the reasons that I that I think that SQL Injection is just a big problem is thanks to the separation of duties that we have at most companies.  The reason that I say this is because the software developers that build the applications never have to deal with the cleanup from the SQL Injection attack.  Many developers, probably because they don’t work all that closely with database administrators, see SQL Injection as a SQL Server problem not an application problem.  This thinking would be wrong, as the only way to prevent SQL Injection problems is to protect the data at the application layer by using coding best practices like using parameterized queries like that shown below, taken from Chapter 8 of my book Securing SQL Server (this sample is for VB.NET, the book includes examples in C# as well as VB.NET).

Private Sub MySub()
Dim Connection As SqlConnection
Dim Results As DataSet
Dim SQLda As SqlDataAdapter
Dim SQLcmd As SqlCommand
SQLcmd = New SqlCommand
SQLcmd.CommandText = “sp_help_job”
SQLcmd.CommandType = CommandType.StoredProcedure
SQLcmd.Parameters.Add(“job_name”, SqlDbType.VarChar, 50)
SQLcmd.Parameters.Item(“job_name”).Value = “test”
Connection = New SqlConnection(“Data Source=localhost;Initial Catalog=msdb;Integrated Security=SSPI;”)
Using Connection
Connection.Open()
SQLcmd.Connection = Connection
SQLda = New SqlDataAdapter(SQLcmd)
Results = New DataSet()
SQLda.Fill(Results)
End Using
‘Do something with the results from the Results variable here.
SQLcmd.Dispose()
SQLda.Dispose()
Results.Dispose()
Connection.Close()
Connection.Dispose()
End Sub

Now I freely admin that coding the .NET code this way is harder than using Dynamic SQL which is shown below.

Private Sub MySub()
Dim Connection As SqlConnection
Dim Results As DataSet
Dim SQLda As SqlDataAdapter
Dim SQLcmd As SqlCommand
SQLcmd = New SqlCommand
SQLcmd.CommandText = “exec sp_help_job @job_name=’” + MyVBNetVariableWithTheJobName + “‘”
SQLcmd.CommandType = CommandType.Text;
Connection = New SqlConnection(“Data Source=localhost;Initial Catalog=msdb;Integrated Security=SSPI;”)
Using Connection
Connection.Open()
SQLcmd.Connection = Connection
SQLda = New SqlDataAdapter(SQLcmd)
Results = New DataSet()
SQLda.Fill(Results)
End Using
‘Do something with the results from the Results variable here.
SQLcmd.Dispose()
SQLda.Dispose()
Results.Dispose()
Connection.Close()
Connection.Dispose()
End Sub

The problem that I have with application developers taking the easy, shorter way out is that their job isn’t to take the easy way out.  Their job is to build the application securing and robustly, not in such a way that the application is as easy as possible to write.  This problem can probably be traced back to the specifications which were written for the application which probably don’t mention security at all anywhere in the specification from the business unit.  Because security isn’t a primary concern for the business unit it is left as an afterthought, an afterthought which is typically ignored until after there has been a breech.

Another reason that I think that SQL Injection is a problem is that we trust that our users wouldn’t want to do anything to hurt our applications or their data as they have a vested interest in keeping the system working correctly.  And this is true to some extent.  However when you publish an application on the public Internet not only will you customers be using it, but others will be attempting to hit the forms within the application.  Because of this, we can’t trust any input that the application user passes in.  Even if the value that is passed in is from a hidden field, or has been validated by the front end. If the value hasn’t been validated by the back end, and properly scrubbed then it shouldn’t be trusted.  And the only way to fully validate and scrub the value is to use the parameterized query technique which I showed above, no other technique no matter how clever will be as successful.

I’ve been working in the IT space for about 15 years now, and I’ve worked on dozens of application development projects over the years at companies large and small, and I can’t recall a single application design specification which included security of the data as a component of the application development.  As the production DBA for companies I’ve forced the issue when I would find problems early enough in the development cycle, but often I wouldn’t find out about the application that was being built until it was time to deploy the application to production.  At this point it is to late to make the kinds of major changes which need to be made, and because security doesn’t add value to the application or to the business unit security isn’t given the developer or QA resources which are needed to make the changes needed to properly secure the data from potential attackers.

I urge everyone that reads this, developers and administrators alike, to look at how applications within your environment connection to the database engine (it doesn’t matter what database engine you use, they can all be broken into via SQL Injection, and yes MySQL is included in this) and if dynamic SQL is being used, and isn’t being properly parameterized talk to upper management about this problem.  Explain to them that while this won’t be something which adds features to the application and won’t necessarily add value to the business, this is something which absolutely needs to be resolved.

Suffering from a SQL Injection breach will have a negative impact on the company, and the IT department in several ways.  From the company side of things customers will loose confidence in the company, which means that they will stop purchasing your product or using your service.  This means that the company will make less money.

Internally the business unit will loose faith in the IT staff as they can’t properly secure their applications from attackers.  The business unit will then loose faith in the developers as the IT staff explains that the only way to protect 100% against this sort of attack is to fix the application which means lots of time (possibly hundreds or thousands of man hours) just fixing database access code and not adding functionally into the application.  The business unit will then assume that the developers aren’t good developers and may request that new developers be brought in, that the application development be outsourced, or that a third party application be purchased.  All of which mean that you and/or your coworkers could easily be out on the street looking for more work.

If you are working on a new development project and security isn’t included in the specification push to have it added.  Yes it will slow the delivery of the application down, but it will remove the risk of a data breach, or worse than that a total network breach (where the attacker is able to get into the company network and take control of internal resources like domain controllers, file servers, etc.) which would be a major disaster  to any company no matter how large or small.

I hope that you take this to heart and fix any applications in your environment which have SQL Injection issues so that we can all stop reading about these data breaches which are coming all to often.

Denny


July 26, 2012  2:00 PM

Security Sessions at SQL PASS 2012

Denny Cherry Denny Cherry Profile: Denny Cherry

The SQL PASS session list for the SQL PASS 2012 Summit has been released.  This year there are 192 sessions being presented at the SQL PASS summit.  Last year at the 2011 summit there were only a couple of sessions on SQL Server Security.  This year there are 4 sessions.  While this appears to be a bit better that before (if I remember correctly there were 3 last year), based on the number of large scale data breeches this year we need to be talking more about SQL Server security, and most importantly people need to actually listen.

Where Should I Be Encrypting My Data being presented by me

SQL Server 2012 Security for Developers being presented by Andreas Wolter

The Evolution of Security in SQL Server 2012 being presented by Don Kiely

SQL Injection: From Website to SQL Server being presented by Mladen Prajdić

There are a couple of other sessions that mention security in the abstract, but based on these abstracts I’m guessing that security won’t be mentioned very much during the actual sessions.

Denny

P.S. Don’t forget about my free SQL PASS 2012 First Timer’s webcast coming up on October 17th, 2012 at 1pm Pacific / 4pm Eastern.  You do need to sign up for the session, which is FREE so get signed up.  Even if you have attended the SQL PASS summit before, this is worth it as there are some big changes in how PASS will be laid out at the convention center this year.


July 23, 2012  2:00 PM

SQL Saturday 147 Precon (#sqlsat147)

Denny Cherry Denny Cherry Profile: Denny Cherry

If you are planning on attending SQL Saturday 147 down in Recife Brazil you would check out my pre-con session being held on August 24th.  In this session I’ll be talking about all of the High Availability options which are available for SQL Server 2012.  I will be talking about AlwaysOn Availability Groups, Clustering, Database Mirroring, and Transaction Log Shipping.  Most importantly we’ll be talking about how you should be deciding which of these technologies to use.

The cost for this pre-con is $95 (US) plus an additional $6.22 (US) in fees to Event Bright for a total of $101.22.

I urge you to sign up for this great all day pre-con session today as seating for this session is limited.

Denny

P.S. Don’t forget this session will be held in English.


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: