SQL Databases Migration with Command Line

What if you have dozens of SQL databases and manually backing up/restoring each database is too time-consuming for your project? No problem! We can script out a method that will export and import all databases at once without needing manual intervention. For help with transferring SQL Logins and Stored Procedures & Views take a look at our MSSQL Migration with SSMS article.

1. Open SSMS (Microsoft SQL Server Management Studio) on the source server, log in to the SQL instance and open a New Query window. Run the following query:

SELECT name FROM master.sys.databases

This command will output a list of all MSSQL databases on your server. To copy this list out, click anywhere in the results and use the keyboard shortcut CTRL+A (Command + A for Mac users) to select all databases. After highlighting all the databases right click and select copy.

2. Open Notepad, paste in your results and delete all databases (in the newly copied notepad text) you do NOT wish to migrate, as well as deleting the following entries:

  • master
  • tempdb
  • model
  • msdb

These entries are the system’s databases, and copying them is not necessary. Make sure to delete everything except explicitly the databases you need to migrate.  You should now have a list of all required databases separated by a line. i.e.

  • AdventureWorks2012
  • AdventureWorks2014
  • AdventureWorks2016

3. Save this result on the computer as C:\databases.txt.

4. Create a new Notepad window, copy/paste the following into the document and save it as C:\db-backup.bat

mkdir %systemdrive%\dbbackups
for /F "tokens=*" %%a in (databases.txt) do ( sqlcmd.exe -Slocalhost -Q"BACKUP DATABASE %%a TO DISK ='%systemdrive%\dbbackups\%%a.bak' WITH STATS" )

5. Now that you’ve saved the file as C:\db-backup.bat, navigate to the Start menu and type cmd and right click on Command Prompt to select Run as Administrator.Type the following command:

cd C:\

And hit enter. Afterward, type db-backup.bat and hit enter once again.

At this point, your databases have begun exporting and you will see the percentage progress of each databases export (pictured below).

Command line shows the process of each database that is exported.

Take note of any failed databases, as you can re-run the batch file when it’s done, using only the databases that may have failed. If the databases are failing to back up, take note of the error message displayed in the command prompt, address the error by modifying the existing C:\databases.txt file to include only the failed databases and re-run db-backup.bat until all databases are successfully exported.

 

By now you have the folder C:\dbbackups\ that contains .bak files for each database you want to migrate. You will need to copy the folder and your C:\databases.txt file to the destination server. There are numerous ways to move your data to the destination server; you can use USB, Robocopy or FTP. The folder on the C drive of the destination server should be called C:\dbbackups . It’s important to accurately name the file as our script will be looking for the .bak files here. Be sure that the destination server has your C:\databases.txt file as well, as our script will be looking for the database names here.

 

1. Open a Notepad and copy/paste the following into the document and save it as C:\db-restore.bat

for /F "tokens=*" %%a in (C:\databases.txt) do (
sqlcmd.exe -E -Slocalhost -Q"RESTORE DATABASE %%a FROM DISK='%systemdrive%\dbbackups\%%a.bak' WITH RECOVERY"
)

2. Save the file as C:\db-restore.bat 

3. Navigate to the Start menu and type cmd.

4. Right click on Command Prompt and select Run as Administrator. Type the following command:

cd C:\

and hit Enter. Now type db-restore.bat and hit Enter.

Your databases have now begun importing. You will see the percentage of each databases restoration and the message “RESTORE DATABASE successfully processed” for each database that has been successfully processed.

Take note of any failed databases, as you can re-run the batch file when it’s done, using only the databases that have failed. If the databases are failing to back up, take note of the error message displayed in the command prompt, address the error (you can change the batch file as necessary), modify C:\databases.txt to include only the failed databases and re-run db-restore.bat until all databases are successfully exported.

Congratulations, you have now backed up and restored all of your databases to the new server. If you have any login issues while testing the SQL connections on the destination server, refer to the Migrating Microsoft SQL Logins (anchor link) section of this article and follow the steps therein. To migrate views or stored procedures please refer to the Migrating Views and Stored Procedures section. Every SQL server will have it’s own configurations and obstacles to face but we hope this article has given you a strong foundation for your Microsoft SQL Server Migration.

 

How to Remove (Delete) a User on Ubuntu 16.04

User management includes removing users who no longer need access, removing their username and any associate root privileges are necessary for securing your server. Deleting a user’s access to your Linux server is a typical operation which can easily be performed using a few commands.  

Pre-flight Check

  • We are logged in as root on an Ubuntu 16.04 VPS powered by Liquid Web!

Step 1: Remove the User

Insert the username you want to delete by placing it after the userdel command. In our example, I’ll be deleting our user, Tom.

userdel tom

Simultaneous you can delete the user and the files owned by this user with the -r flag.  Be careful these files are not needed to run any application within your server.

userdel -r tom

If the above code produces the message below, don’t be alarmed, it is not an error, but rather /home/tom existed but /var/mail/tom did not.

userdel: tom mail spool (/var/mail/tom) not found

 

Step 2: Remove Root Privileges

By removing Tom’s username from our Linux system we are halfway complete, but we still need to remove their root privileges.

visudo

Navigate to the following section:

## Allow root to run any commands anywhere
root ALL=(ALL:ALL) ALL
tom ALL=(ALL:ALL) ALL

Or:

## User privilege specification
root ALL=(ALL:ALL) ALL
tom ALL=(ALL:ALL) ALL

With either result, remove access for your user by deleting the corresponding entry:

tom ALL=(ALL:ALL) ALL

Save and exit this file by typing :wq and press the enter key.

To add a user, see our frequently used article, How to Add a User and Grant Root Privileges on Ubuntu 16.04. Are you using a different Ubuntu version? We’ve got you covered, check out our Knowledge Base to find your version.

Whitelisting in ModSecurity

Broken down into two parts our article’s first section hits on “how to whitelist IPs or URIs,” for people who are somewhat familiar with ModSecurity but want to know further about the process. Our second section examines why we configure ModSecurity and how to prevent the security of the server from getting in the way of our work. If you have a Fully Managed Liquid Web server reach out to our Heroic Support team for assistance with whitelisting!

How to Whitelist IPs or URIs

“ModSecurity is a toolkit for real-time web application monitoring, logging, and access control.” (modsecurity.org).  In simple terms, this means that ModSec, also called mod_security or ModSecurity, is a web application firewall that can actively look for attacks to the system and stop malicious activity. However, sometimes these rules trigger when legitimate work is taking place, blocking your IP and stopping you or your developer’s until you can remove the IP block. The way around for being blocked is known as whitelisting, which essentially allows for a specific IP to access the server.   There are a few ways to whitelist a request in ModSec, either by IP or by URI (URIs are specific pages on the website). 

Getting Started

  1. Find your IP or ask your developer for theirs. (You can find this by going to ip.liquidweb.com)If you or your developer have a static IP (one that will not change), one way you can whitelist the ModSec rules is by IP.
  2. Find the ModSec error in the Apache error logs with the following command (Be sure to modify the command with your IP in place of “IP here.”):
    grep ModSec /usr/local/apache/logs/error_log | grep “IP here”.
  3. The output of this command will give you a list of hits for ModSecurity from you or your developer’s IP, which you can see below. While this looks intimidating, you will only want to pay attention to 3 bits of information highlighted.  Please note, the output will not show these colors when you are viewing the files.
Note
Blue = client, the IP which tripped the rule
Red
= ID number of tripped rule within ModSec
Green = URI, the location where the error started from

[Fri May 25 23:07:04.178701 2018] [:error] [pid 78007:tid 139708457686784] [client 61.14.210.4:30095] [client 61.14.210.4] ModSecurity: Access denied with code 406 (phase 2). Pattern match "Mozilla/(4|5)\\\\.0$" at REQUEST_HEADERS:User-Agent. [file "/etc/apache2/conf.d/modsec2.liquidweb.conf"] [line "109"] [id "20000221"] [hostname "67.227.209.163"] [uri "/db/index.php"] [unique_id "WwjPWChxvG1CO4kz-D55eQAAACU"]

 

Whitelist By IP:

1. Once you have the correct ModSec error, you will need to edit the ModSec configuration. If you are using Easy Apache 4 you will find the configuration file with this path:
/etc/apache2/conf.d/modsec2/whitelist.conf

2. Open the file with your favorite text editor, such as vim, nano, or file manager like so:

vim /etc/apache2/conf.d/whitelist.conf

3.  The blue text above will be the IP address that you are whitelisting from the original error. You must keep the backslashes (\) and up-carrot (^) in order for the IP to be read correctly. Thus it will always look something like:

“^192\.168\.896\.321”

For for the id, noted in red, you will change the number after the colon, which will be the Apache error log like we saw above. This will look similar to:

Id:2000221

Add the following code with the colored sections edited to match your intended IP.

SecRule REMOTE_ADDR "^64\.14\.210\.4"
"phase:1,nolog,allow,ctl:ruleEngine=off,id:20000221"

 

Whitelist By URI:

If your IP is dynamic (changing) and you keep getting blocked in the firewall, it is best to whitelist it via URI, the yellow item in the ModSec error.

1. Begin by opening the Easy Apache 4 configuration file:

vim /etc/apache2/conf.d/whitelist.conf

2. Add the following text to the configuration. Remember to pay attention to the highlighted parts.  Change the yellow “/db/index.php” to match your URI and the red id to match the id of your error (Do not use the colon in this one).

<LocationMatch "/db/index.php">
SecRuleRemoveById 20000221
</LocationMatch>

3. The final step for whitelisting, before you finalize the process, is to ensure you have correctly set up the whitelist. For Easy Apache 4 you will run the command:
apachectl -t

As long as the command returns “Syntax Ok” you are safe to make the whitelist active by restarting Apache. Otherwise, review the whitelists to make sure the syntax matches up correctly with the above directions.

4. Lastly, restart Apache with the following command.

/scripts/restartsrv_httpd

You have successfully whitelisted yourself in ModSec!

 

Using ModSec

Cyber Security is a hydra; once one threat is cut off, two more grow back. While this is not a new analogy, it’s important to understand as we battle threats to our network, computers, and servers. With all the complexities that come with security, I want to talk about adequately configuring ModSec to deter threats while still allowing you to work on your websites. Often, when it comes to server security, too much protection can hinder effectiveness.

For example, say you have the following set up on your server:

  • You do not allow root SSH login to the server
  • utilize dual-factor authentication for any SSH logins
  • use an SSH key for the sudo user and require other security safeguards

While this type of configuration is secure, it takes longer to log into your system to make a quick edit to your settings, a double-edged sword; how can you keep the server safe while not tying your own hands?  A great example of how this plays out is using ModSec.

ModSec can block your IP if it falsely flags your work. While this module improves system security, you’ll need to be aware of properly implementing and “scoping” the technology. Scoping in this sense means to manage risks, the focus of what is important for security while still allowing work on the server with minimal interference. To tune out legitimate requests to your server, such as when you are editing your website’s code via a plugin, ModSec has the options to whitelist rules or IPs and keep your work on track.

Whitelisting an IP from the rules that ModSec follows is a great option so long as the IP never changes (i.e. a static IP, see article here to learn more https://support.google.com/fiber/answer/3547208?hl=en) and is limited to only people you trust. This method prevents ModSec from viewing your requests as malicious and blocking your IP. This practice has the drawback that if someone (say an unhappy employee) has access to your network, they now have a way around ModSec to attack your server.

With non-static (dynamic) IPs the problems of whitelisting an IP are readily apparent. With the continual change of a dynamic IP, it creates the potential of exploiting your server, as someone could use an old IP to access the server. Whitelisting specific rules comes to save the day! When you whitelist by rules, you can edit with granularity and limit the rules to particular domains and URIs, protecting the rest of the server from attacks related to that same rule!

Example of ModSecurity

ModSec reads a series of rules and applies them to incoming requests being made to the web server. An example of what a block looks like is:

[Sat Jun 30 02:21:56.013837 2018] [:error] [pid 79577:tid 139862413879040] [client 120.27.217.223:24397] [client 120.27.217.223] ModSecurity: Access denied with code 406 (phase 2). Pattern match "Mozilla/(4|5)\\\\.0$" at REQUEST_HEADERS:User-Agent. [file "/etc/apache2/conf.d/modsec2.liquidweb.conf"] [line "109"] [id "2000064"] [hostname "67.227.192.139"] [uri "/mysql/index.php"] [unique_id "WzchhAjuZ6wPAzo9AwW1WwAAAE8"]

This error shows Apache stopped a potential attack on a file at /mysql/index.php. This is an error similar to what appears when the code is being written or edited within programs like Drupal or WordPress.

Evaluating ModSecurity

If you are persistently being blocked in your firewall while working on your code, ModSec is the likely culprit. The ModSec errors can be found in the Apache error log (in cPanel the path is /usr/local/apache/logs/error_log). The phrase “ModSec” can be quickly isolated from the log (via the command ‘grep “ModSec” /usr/local/apache/logs/error_log’). By comparing you or your developer(s) IP to the log, you’ll be able to identify stopped requests that are legitimate. Verify these are valid requests by double-checking that someone in your organization made them. Once you have done so, you can move forward in setting up a whitelist for the error, per the steps above.

Again, we want to scope to allow the least amount of wiggle room for an attack and ensure we can keep working. If you are unable to have a trusted static IP, you’ll need to use the whitelist URI  method, providing the specific page as an exemption. Once completed, remove both whitelisted items from the configuration file, in case of a genuine attack.

On a parting note, I encourage you to explore ModSec and learn more of the ins and outs of the software. Exploring different methods of whitelisting can be a lot of for to learn and most importantly helps to tighten server security. As always, our Fully Supported Customers can contact our Helpful Human Support team for assistance. Check out articles on security in our Knowledge Base, like this one on Maldet! It’s another excellent way to learn about your server and develop an understanding of server security.

How to Add a User and Grant Root Privileges on Ubuntu 16.04

Ubuntu 16.04 LTS provides you the ability to add a user for anyone who plans on accessing your server.  Creating a user is a basic setup but an important and critical one for your server security. In this tutorial, we will create a user and grant administrative access, known as root, to your trusted user.

 

Pre-Flight Check

  1. Open a terminal and log in as root.  
  2. Work on a Linux Ubuntu 16.04 server

Step 1:  Add The User

Create a username for your new user, in my example my new user is Tom:

adduser tom

You’ll then be prompted to enter a password for this user.   We recommend using a strong password because malicious bots are programmed to guess simple passwords. If you need a secure password, this third party password generator can assist with creating one.

Output:

~# adduser tom
Adding user `tom' ...
Adding new group `tom' (1002) ...
Adding new user `tom' (1002) with group `tom' ...
Creating home directory `/home/tom' ...
Copying files from `/etc/skel' ...
Enter new UNIX password:
Retype new UNIX password:
passwd: password updated successfully

Note
Usernames should be lowercase and avoid special characters. If you receive the error below, alter the username. ~# adduser Tom
adduser: Please enter a username matching the regular expression configured via the NAME_REGEX[_SYSTEM] configuration variable.  Use the `--force-badname' option to relax this check or reconfigure NAME_REGEX.

 

Prompts will appear to enter in information on your new user.  Entering this information is not required and can be skipped by pressing enter in each field.

Enter the new value or press ENTER for the default
Full Name []:
Room Number []:
Work Phone []:
Home Phone []:
Other []:

 

Lastly, the system will ask you to review the information for accuracy.  Enter Y to continue to our next step.

Is the information correct? [Y/n]

 

Step 2: Grant Root Privileges

Assigning a user root access is to grant a user the highest power.  My user, Tom, can then make changes to the system as a whole, so it’s critical to allow this access only to users who need it. Afterward, Tom will be able to use sudo before commands that are usually designed to be used by the root user.

usermod -aG sudo tom

 

Step 3: Verify New User

As root, you can switch to your new user with the su – command and then test to see if your new user has root privileges.

su - tom

If the user has properly been granted root access the command below will show tom in the list.

grep '^sudo' /etc/group

Output:

sudo:x:27:tom

 

Accessing man pages on Ubuntu 16.04 LTS

Having access to man pages on your server is a pretty essential asset to be familiar with. If you’re not familiar with man pages they are documentation provided with software packages on Unix systems. They provide a sort of manual for applications, services and system resources. You can learn more about man pages in our introductory article. By default on Ubuntu based servers this command is not provided, since it’s a great tool to have access to this article will help you get them setup.

Continue reading “Accessing man pages on Ubuntu 16.04 LTS”

How To Install Git on Ubuntu 16.04 LTS

Git is one of the most popular tools used for distributed version control system(VCS). Git is commonly used for source code management (SCM) and has become more used than old VCS systems like SVN.

Installing Git on Ubuntu 16.04 LTS

Pre-Flight Check
  • You should be running a server with any Ubuntu 16.04 LTS release.
  • You will need to log in to SSH via the root user.
  • In this tutorial I’ll be working with a Core Managed Ubuntu 16.04.4 LTS server

First, as always, we should start out by running general OS and package updates. On Ubuntu we’ll do this by running:apt-get update

After you have run the general updates on the server you can get started with installing Git.

  1. Install Git
    apt-get install git-coreYou may be asked to confirm the download and installation; simply enter y to confirm. It’s that simple, git should be installed and ready to use!
  2. Confirm Git the installation
    With the main installation done, first check to ensure the executable file is setup and accessible. The best way to do this is simply to run git with the version command.
    git --version

    git version 2.7.4
  3. Configure Git’s settings (for the root user)
    It’s a good idea to setup your user for git now, to prevent any commit errors later. We’ll setup the user testuser with the e-mail address testuser@example.com.

    git config --global user.name "testuser"
    git config --global user.email "testuser@example.com"
    Note:
    It’s important to know that git configs work on a user by user basis. For example if you have a ‘david’ Linux user and they will be working with git then David should run the same commands from his user account. By doing this the commits made by the ‘david’ Linux user will be done under his details in git.
  4. Verify the Config changes
    Now we’ll verify the configuration changes by viewing the .gitconfig file. You can do this a few ways, we’ll show you both methods here.

    1. View the config file using cat with the following command:
      cat ~/.gitconfig
    2. Or, you can also view the same details using the git config command:
      git config --list

And that’s it! You have now installed Git on your Ubuntu 16.04 LTS server and have it configured on your root user. You can get rolling with your code changes from here, or you can repeat steps 3 and 4 for the other system user accounts.

Installing WordPress using WP-CLI

WordPress has a great GUI based installation process however some use cases call for CLI! Or maybe you just feel more at home in a terminal, either way this article will show you how to get your WordPress site setup with just a terminal, WP-CLI, and maybe a sprinkle of SSH.

So first things first, if you’re doing this locally fire up your terminal and if you’re doing this on a server connect to it via SSH. Now that you’re in CLI you want to get into the folder you’re installing WordPress to. From there you can get things rolling in WordPress! Continue reading “Installing WordPress using WP-CLI”

How to enable EPEL repository?

The EPEL repository is an additional package repository that provides easy access to install packages for commonly used software. This repo was created because Fedora contributors wanted to use Fedora packages they maintain on RHEL and other compatible distributions.

To put it simply the goal of this repo was to provide greater ease of access to software on Enterprise Linux compatible distributions.

What’s an ‘EPEL repository’?

The EPEL repository is managed by the EPEL group, which is a Special Interest Group within the Fedora Project. The ‘EPEL’ part is an abbreviation that stands for Extra Packages for Enterprise Linux. The EPEL group creates, maintains and manages a high quality set of additional packages. These packages may be software not included in the core repository, or sometimes updates which haven’t been provided yet.
Continue reading “How to enable EPEL repository?”

How to: Using killall to Stop Processes with Command Line

Sometimes you may find your server in a state of high load caused by out control of processes. First you’ll want to use a command like htop, top, or ps, to get an idea on the server’s current state. If you aren’t familiar with those utilities we’d suggest checking our our article on htop.

After you have an initial assessment of the server’s current load you will have a better idea on how to proceed. More often than not the load is likely being caused by regular server traffic and usage.

Generally that will mean the load is being caused by a high number of Apache, PHP, or MySQL processes. After all most servers are hosting websites and these are the most commonly required programs to run a website. With that in mind during times of high load it’s often nice to quickly stop all processes of a certain type.
Continue reading “How to: Using killall to Stop Processes with Command Line”

Featured Freeware: htop

Featured Freeware highlights some of the Liquid Web staff’s favorite free software. This can range from useful command line tools, open-source packges useful in web-development, or even multi-platform applications. This week we are covering a treasured favorite, htop.

Note: This post assumes you have a working knowledge of top. You can read our article on using top, if you are not familiar with the tool.

htop, or Hisham’s top, is an interactive process viewer for Unix systems. With htop you are provided the same functionality as top, however it provides some needed improvements. Most are in areas where top shows some of it’s age; for example, in htop you can scroll the list of processes vertically and horizontally to see all the process info.

Another benefit is that htop seems to start significantly faster, generally when using top there is a bit of a delay while the program loads up some initial data. So now that you know the basics of how htop differs from top, lets get to using it. First you’ll need to ensure it’s installed on the server and if not we’ll try to get it installed.
Continue reading “Featured Freeware: htop”