I thought my days of Linux were over

Published On: 2019-03-18By:

Oh, how wrong I was. Back in the day, all I worked on was Microsoft SQL Server. These days I’m doing some Microsoft SQL Server and a decent amount of Microsoft Azure and Amazon AWS cloud work. With all three of those, there’s a lot of Linux in play. Microsoft SQL Server has supported Linux since the release of SQL Server 2017 at Ignite 2017.  Microsoft Azure and Amazon AWS have both supported Linux since (I believe) they first supported VMs in their cloud platforms (forever is the world of computers).

Back when I had just a few years expense with SQL Server (and IT in general) I also owned and managed a large (at the time) Oracle database which ran on Unix. Once that was no longer my baby to manage, I assumed by *nix carrier was over. And it was, for a while, but now Linux is back and this time in the SQL Server world.

Looking at the servers that DCAC has in our Azure environment, we have more Linux boxes than Windows. Our website runs off of PHP running on a pair of Linux servers. Our database is MySQL running on a couple of Linux server (eventually we’ll move all this over to Azure PaaS, but still running on Linux). The only production servers in Azure that we have running Windows, our the Active Directory domain controllers, one of which also syncs from Active Directory to Azure Active Directory to handle our sign in, Office 365, etc.  That’s it. Everything is Linux.

Our lab environment in our CoLo is also a mix of Windows and Linux.  We have a few tools that were built by Microsoft that we run that are running on Windows, but we’ve also got a decent amount of Linux in the data center as well.  By the time this is published (I’m writing this on the flight to the PASS Summit in November 2018) we’ll have a Docker cluster up and running as well (unless I get lazy and I don’t get up to the CoLo to rack the servers for it). This Docker cluster is Linux based as well and will let us run a bunch more Linux servers as well.

Your point is?

The point that I’m trying to get to in all of this is that if you are a database administrator that thought they were going to stay in the Windows world forever, think again. You have to be an expert in Linux to manage these systems, but you’ll need to understand the difference between Windows and Linux. SQL Server has a few differences between the platforms, and these differences are significant to the platforms.  As a Windows DBA you’ll want to be able to navigate the Linux Operating System, and tell your system teams where SQL Server is storing the database files (they are in /var/opt/mssql/data if anyone asks) so that they know which mount points need to be made bigger.

You don’t need to know everything, but the basics of Linux are doing to take you a long way.

Denny

The post I thought my days of Linux were over appeared first on SQL Server with Mr. Denny.

Checking Open Ports in Azure

Published On: 2019-03-15By:

Let’s be honest here, data security is really important to me.  Some people probably think that I go to extremes to ensure that my data as well as my customers data is secured.  With that, sometime ago, I wrote a blog post on utilizing a VPN server that I built on an Azure Virtual Machine to help facilitate a secure connection whenever I am away from my home network.  Using a VPN server is a great way to ensure security and peace of mind.

Turns out that the server got hacked.  And it was my fault.

See, if you leave certain ports open to the outside world like I did, you are just asking for hackers to attack you.  And that’s exactly what happened.  When I first created the virtual machine, I mistakenly left port 3389, which is used to make remote desktop protocol (RDP) connections, open and available to the world.  There wasn’t any rule in place limiting those connections to a certain IP address or ranges, so it was a free for all for hackers.   By the way, hackers will routinely scan IP addresses looking for open ports so it was only natural that eventually one would find me.

Ironically, the hackers wanted bitcoin funds to release the virtual machine back to me.   I quickly laughed at them and promptly deleted the entire machine off of Azure.  Once the VM was removed, I was able to get a new virtual machine stood up, along with the VPN software re-installed, and available for connections within about 30 minutes.  I did make sure to apply certain Network Security Group (NSG) rules to help prevent this from happening again.

Azure Advisor

This was a great learning lesson for me, however it got me thinking about network security in Azure and ways to prevent this.  One way to do this is to utilize the Azure Advisor.  The Advisor offers up security recommendations (as well as recommendations in other categories) that will help further harden your Azure foot print.  These recommendations can range from NSG rules, enabling auditing, or installing endpoint protection solutions that will actively monitor for viruses and malware.  Here’s an example of what a report looks like:

As you can see, you can walk through all recommendations and decide on whether or not to implement them.  Keep in mind that some of the recommendations might incur additional costs against the respective subscriptions so make sure to evaluate them carefully.

Powershell

In conjunction with Azure Advisor, I also decided to utilize Powershell.   I thought that having a script that I could easily run on an ad-hoc basis would help me to identify any open ports that I might have.  Later I’ll automate this in some fashion, probably via Runbooks, but for now just having a script is handy enough for me.

The script is fairly basic in that it will loop through all of the subscriptions that are associated with your login, look for any NSG’s in which the source address is “*” (essentially this means any source IP address) and the Access method is set to “Allow”.   This, of course, is adjustable for your needs so feel free to play around with the values.   The script will also output the NSG’s which satisfy those requirements to a file and then subsequently open the file.

Let’s step through it!!

First, I’m going to set a path to the local file in which to export the output.   After that I need to authenticate to my Azure subscription.  If you are already connected and authenticated in the context of Powershell, the script will check for that and skip the login process accordingly.

$path = "c:\temp\openports.txt"

#log into Azure Account if needed
IF (-NOT (get-azcontext)){
    Login-AzAccount
}

Once we are authenticated and logged in, we can then start to loop through things.  We will loop through every subscription and then within every subscription, checking every NSG for open ports.  If the NSG has an open port, it will output the results into the file we specified in the step above.

#get all of the subs
$subs = get-azsubscription

#loop through the subscriptions
foreach($sub in $subs){
    set-azcontext -SubscriptionID $sub.ID
    $nsgs = Get-AzNetworkSecurityGroup 
        foreach($nsg in $nsgs){
            $nsg | get-aznetworksecurityruleconfig | where-object -FilterScript {$_.sourceaddressprefix -eq "*" -and $_.Access -eq "Allow"} | out-file -FilePath $path -Append
    }
}

Once the file has been populated, we can open the file with the Invoke-Item cmdlet which will open the text file into your default text editor, usually Notepad.

#open the file
invoke-item $path

Here is the entire script put together:

#############################################################################
# MIT License
# 
# Copyright (c) 2017-Present John Morehouse
# 
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:

# The above copyright notice and this permission notice shall be included in all
# copies or substantial portions of the Software.

# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
# SOFTWARE.
#############################################################################
$path = "c:\temp\openports.txt"

#log into Azure Account if needed
IF (-NOT (get-azcontext)){
    Login-AzAccount
}

#get all of the subs
$subs = get-azsubscription
foreach($sub in $subs){
    set-azcontext -SubscriptionID $sub.ID
    $nsgs = Get-AzNetworkSecurityGroup 
        foreach($nsg in $nsgs){
            $nsg | get-aznetworksecurityruleconfig | where-object -FilterScript {$_.sourceaddressprefix -eq "*" -and $_.Access -eq "Allow"} | out-file -FilePath $path -Append
    }
}

#open the file
invoke-item $path

You can also find the script in Github located here.

Summary

As a data professional, security should and is always on my mind.  Understanding the risks of using technology will help you find methods to prevent unauthorized access.  In this case, the hacker only really caused me about 30 minutes of time to rebuild my server, however they taught me a great lesson in security.  Even in your development environments, make sure to review your security rules so that this doesn’t happen to you.

 

© 2019, John Morehouse. All rights reserved.

William Ryan Homes Azure Migration

Published On: 2019-03-14By:

The Client:

Home Being Built
Founded in 1992, William Ryan Homes is the award-winning home builder of more than 10,000 new homes across the United States.
IT Support Coordinator Peter Guzman has been with the company for six years.

The Challenge:

When employees and clients of William Ryan Homes started complaining about slow applications, Peter Guzman knew it was time to go to the cloud.

“Our contractors couldn’t see what their next task was on the schedule, so they couldn’t move things around to reschedule vendors in accordance with construction. Everything was SLOW,” Peter explains.

The on-premise server critical to ERP/ERM was on its last legs and their SQL Server software was out of support. The fiscally prudent choice was to migrate to the cloud to spare the company major capital expenditures. But as the lone IT support coordinator for the whole company, Peter couldn’t manage a migration in addition to production needs. He needed an expert capable of doing the migration seamlessly and the consultant had to be willing to work around the company’s multiple time zones of production. He turned to an expert he trusted in the IT community, who gave him two names. Peter called them both.
Read Now >

Enable Mail Profile – Back to Basics

Published On: 2019-03-13By:

I’ve seen many people go through the trouble of setting up database mail and configuring SQL Agent Alerts only to realize it’s not working. The reason in the cases I’ve seen is because they have simply neglected to assign a mail profile to SQL Agent. This is way more common than you would think, thus I wrote this quick blog.

This check box and drop down can sometimes lead to hours of troubleshooting if you don’t know where to look. In order to receive the alerts, you must enable a mail profile. This would be the profile  created during the Database Mail configuration process.

To configure SQL Server Agent to use Database Mail

  • In Object Explorer, expand a SQL Server instance.
  • Right-click SQL Server Agent, and then click Properties.
  • Click Alert System.
  • Select Enable Mail Profile.
  • In the Mail system list, select Database Mail.
  • In the Mail profile list, select a mail profile for Database Mail.
  • Restart SQL Server Agent.

There are many reasons why Database Mail may fail to send Alerts, but make sure you have this configuration set before spending hours trouble shooting.

A Side Note:

If you want to learn how to setup your alerts and operators I’ve already written a blog on that with scripts you can find it here.

1 2 3 410

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.