How to Install Python on Windows

Python is a popular programming language for developing applications. The Python design philosophy emphasizes code readability and focuses on clear programming for both small and large-scale projects. Python allows you to run modules and full applications from a large library of resources (or even applications you write yourself) on your server. Python works on a number of popular operating systems, including Windows Server OS.

Installation of Python on the Windows Server operating system is a simple matter of downloading the installer from Python.org and running it on your server. Additional configuration adjustments can make using Python easier.

Installing Python

  1. Log in to your server via Remote Desktop Protocol (RDP). For more information on using RDP, see Using Remote Desktop Protocol (RDP) to Log into Your Windows Server.
  2. Download and execute the latest Python installation package from Python.org. For Liquid Web servers, you’ll most likely want the 64-bit version of the installer, but you may want to discuss software requirements with your developer.
  3. Choose the recommended installation options for the simplest installation experience (You can also choose Customize Installation if you need to adjust locations or features, but this may require additional configuration. See Python.org for further instructions on custom installation options).
  4. Check the box for “Add Python 3.7 to PATH”. This will adjust your System Environment Variables automatically so that Python can be launched from any command prompt.The installion screen of Python on a Windows server.
  5. Verify a successful installation by opening a command prompt window in your Python installation directory (probably C:\Users\*yourusername*\AppData\Local\Programs\Python\Python37 if you’ve installed the latest available version). You should receive a message similar to what is shown below.
    If you selected “Add Python 3.7 to PATH”, you can verify the installation from any command prompt window.

When installing Python on a Windows server you'll see the path of installation.

Installing PIP

If you didn’t install PIP using the default settings in the installer, you’d want to install this program to make application and module management easier. You can verify PIP installation by opening a command prompt and entering

pip -VYou’ll see the output similar to the following:

By running the pip -V command we see which version of pip and its path that your Windows Server is running.

If PIP is not installed or for more information on installing PIP, see our article How to Install PIP on Windows.

Any core-managed server at Liquid Web, both traditional-dedicated and Cloud servers, can run Python once installed. If you need assistance, our Helpful Humans can help install and verify the installation. You can also install Python on any of our self-managed servers. Plesk fully-managed servers support Python through ActiveState Python 2.6.5. Plesk also supports full Python installations, but does not include the software in its default components. For more information about using Python with Plesk, see Plesk’s support documentation. Please contact our Solutions team if you have further questions about ordering a server that can support Python.

Note
NOTE: Python software installation is considered Beyond Scope Support. This means it is not covered under our managed support, but we will do what we reasonably can to assist. It may take longer for us to assist as the SLA for Beyond Scope Support is different than our managed services. Find out more in our article What Is Beyond Scope Support?

Remote Desktop Users Group

The most common way to remotely manage a Windows server is through Remote Desktop Protocol. By default, Liquid Web’s Windows servers only allow the members of the administrators’ group remote desktop access. However, the Remote Desktop Users group grants its members access to securely connect to the server through RDP (Remote Desktop Protocol) as well.

This article will go over the basics of the Remote Desktop Users group. By the end, you will be able to add users to the group, understand permissions, and basic user management.

 

Pre-flight

The information below covers methods to configure the Remote Desktop Users group for Windows Server 2012 through Windows Server 2016 on any Liquid Web Windows server. As a valued customer, if you do not feel comfortable performing these steps independently, please contact our support team for additional assistance. Liquid Web support is happy to walk you through the steps and answer any questions you may have.

 

Managing Local Users and Groups

Users and groups on Windows servers are managed in a number of different ways, but the most user-friendly way is through the Local Users and Groups interface. There are several ways to open the interface. However,  the easiest is to run “lusrmgr.msc”. Lusrmgr.msc can be launched by searching the start menu, command line, or through a run dialog. These methods allow you to find users and groups easily.

Note
To manage local users and groups, you will need to be logged in with a user that has the proper permissions to do so. This is most commonly a user that is already a member of the Administrators group.
Within a windows server type in lusrmgr.msc into the search bar to locate Users where you can find existing users and groups.

 

User Management

Once you open the Local Users and Groups interface, you will see two folders on the left, one for Users, and one for Groups. By selecting Users, you will see a full list of local users on the server. You can also see a variety of related tasks by right-clicking Users, Groups, a user’s name, or a blank area of the middle pane.

There are several ways to add a new user through the Local Users and Groups interface. These methods all result in the same “New User” dialog box opening where you can then configure a Username, Password, and other options. Choose one of the options below to create a new user:

  • With the Users folder selected in the left pane, click the Action menu, then select “New User…”.
  • With the Users folder selected in the left pane, click “More Actions” from the right- hand pane, then select “New User…”.
  • Right-click the Users folder, then select “New User…”.
  • With the Users folder selected in the left pane, right-click in a blank area of the middle page, then select “New User…”.

Once you have created a new user, or have identified the username of the existing user, you are ready to assign that user to a Group. Users assigned to a group are known as group members.

 

Group Management

As with user management, group management can also be performed in several ways. The options below cover several of the most common ways to assign a new member to the Remote Desktop Users group:

  • Select the Users folder from the left pane of the Local Users and Groups interface, open the Users Properties window by double-clicking the user, select the “Member Of” tab, then click “Add…”. Now type “Remote Desktop Users” in the text box and click OK.
  • Select the Groups folder from the left pane of the Local Users and Groups interface, double-click the “Remote Desktop Users” group, click “Add…”, enter the user’s name in the text box and click OK.
  • Open the system settings by right-clicking the start menu and selecting “System”, choose “Advanced system settings”, select the “Remote” tab, click the “Select Users…” button then click the “Add” button. Now enter the user’s name in the text box and click OK.
  • Open the “Server Manager”, select “Local Server” from the left pane, click the blue text next to “Computer Name”, select the “Remote” tab, click the “Select Users…” button then click the “Add” button. Now enter the user’s name in the text box and click OK.
    Note
    When selecting users or groups, it is recommended to click the “Check Names” button after typing in the user or group name. If the name is underlined after clicking the “Check Names” button, then the name was identified correctly.

You can also use the “Advanced…” button when selecting users or groups instead of typing its name. Clicking the “Advanced…” button followed by the “Find Now” button will result in a list of users to select.In a windows server, by right-clicking the User folder you can do a variety of tasks like adding a new user.

 

Notes on Permissions & Security

By default, there are no members of the Remote Desktop Users group and only members of the Administrators group are allowed to connect through RDP. Members added to the Remote Desktop Users group are considered non-Administrative users. These users will be unable to perform most management tasks such as installing software, managing IIS, or rebooting the server.

If a user requires management abilities, the user will need explicit access to that task or will need to be a member of the Administrators. Please use the best practice of “least privilege” when configuring your users, groups, and permissions.

 

Test/Verify Group Membership

When configuring new user and group memberships, you should always review group membership once complete.  Reviewing group membership is most commonly performed through the Local Users and Groups interface. In addition to verifying membership, we also recommend attempting a remote desktop connection with your newest Remote Desktop Users group member. If you are unable to connect with your user, please see our Remote Desktop Troubleshooting article.

Once you have logged in with your newest member of the Remote Desktop Users group, you can further verify that groups are set up correctly by running the command “whoami /groups” from a command line. The output of this command lists the username and its associated Group names.

 

Malicious Activity Detector (MAD) for Windows

One of the simplest goals of server security is keeping administrator credentials private. There is no better way to achieve this than through strict firewall rules that only allows specific IPs to authenticate. However, there are some situations where it is necessary to open a login prompt to the broader Internet. In this case, the only thing barring anonymous internet users from unauthorized access is your password. The stronger your password, the better off you are, but even the most cryptic passwords can be guessed given enough tries.

Malicious Activity Detector (MAD) helps protect you in these instances. It functions by monitoring login attempts to several services, and if it detects malicious activity, it applies a temporary block on that IP. If more attempts come in, the block continues to last longer. This method is exceptionally effective in preventing a successful brute-force attack while limiting the number of system resources expended.

 

Installation of MAD

Depending on the configuration and age of your server, you may already have it installed. Check the installation status by looking for an item in your Start Menu shown below.

Installing Liquid Web's Malicious Activity Detector for Windows tightens security for you server.

The program path is C:\Program Files (x86)\Liquid Web\MAD\MADGUI.exe

You may also check if “MAD.exe” is running from your Task Manager. If you don’t see it there, please Contact Support so that we may get it up and running for you. Once running,  we can move on to the configuration.

 

Note:
MAD will be installed on all Windows servers by default in the future.

 

Configuring MAD

MAD’s default settings offer protection for the most vulnerable services, and extra configuration is not required. That said, you may find yourself wanting to change its behavior, and we’re happy to give you the tools you need.

Let’s start with the most common change you may want to make: whitelisting and blacklisting. Opening the MAD Configure utility will get you on the right page. From here, you only need to choose the radio button for the list you want to modify, enter the IP, and click the button. You can remove entries in either list by right-clicking. This page also allows you to start or restart the service, but you shouldn’t need to use those functions.

The List tab easily let's you add in blacklisted or whitelisted IPs.

The next page is where most options are located. All of the service scanners list three choices for each: Enabled/Disabled, BlockThreshold, and Retention.

Our Malicious Activity Detector allows you to adjust settings for maximum security.

Enabled/Disabled -You may want to disable scanners for services that you do not have installed, but it is generally recommended to leave all options enabled due to minimal performance cost.

BlockThreshold – This setting controls how many ‘strikes’ it takes to be blocked. These are set fairly high by default to avoid affecting legitimate users, but you may want to lower the threshold to increase MAD’s sensitivity.

Retention -This refers to the size of the window that MAD looks at to determine if a user has met the BlockThreshold in seconds. By default, this is set to 300 (five minutes).

Example:
Set your BlockThreshold to 10 and your Retention to 300. If 9 failed attempts occurred at 12:00 and a failed attempt occurs before 12:05 it will be blocked. By 12:06 you will be in a new period and will be able to attempt 9 more times before being blocked.

PermaBlock – Sometimes robots can’t take the hint after being temporarily blocked several times in a row. The PermaBlock list remedies this situation. By default the retention period is 2 hours, this scanner checks for IPs that have been temp blocked five times (or your custom BlockThreshold). If it gets a hit, it does as the name implies and adds it to your blacklist, where it is managed much like manual entries.

AuditPolicy – This setting determines if MAD is allowed to edit your login event auditing policy. Disabling AuditPolicy is not recommended and may prevent MAD from working as intended.

TempBlockTimeout -When a block is triggered on one of the scanners the offending IP address will be blocked for this amount of time. Measured in seconds with a default setting of 900 (15 minutes).

 

Reviewing MAD Logs

MAD creates logs of all of the actions that it takes. It is good practice to review them regularly to see what has been going on. For example, if a certain service seems to be getting attacked more often than others you may want to consider hardening your firewall rules or MAD’s configuration itself.

Our Malicious Activity Detector keeps logs of anyone attempting to connect to your Windows server.

MAD also creates events in Windows Event Viewer under the ‘Applications and Services Logs’ folder. These events are most helpful for long-term investigation, as the folder will hold historical data for quite some time.

MAD also creates Events for long-term investigation, as the folder will hold historical data.

MAD for Windows is an excellent tool in your security arsenal, but a proactive plan is always better than a reactive one. We recommend utilizing Windows Firewall to ensure that only things that must be publicly accessible are. For further reading on security visit some of our other articles:

Security for Remote Desktop
Best Practices for Your Firewall

Install PHP on Windows

PHP for Windows provides users the ability to run nearly any PHP script desirable. Windows can tackle a wide range of software, from your PHP scripts to the many content management systems such as WordPress or Drupal.

Since Windows does not come already equipped with PHP, it does require some additional steps to install. This article will walk you through the process of how to effectively install PHP on Windows 2016 through the use of the Windows PHP 7 Installer.

Pre-flight

Before you can begin your PHP installation, you will need to determine if your server has our Fully managed Plesk control panel, or is one of our self or core managed options (without Plesk).

  1. You can determine whether or not your server has Plesk by logging into https://manage.liquidweb.com.
  2. Once you have successfully logged in, expand your server from the “Overview” page.
  3. Next, look to the far right of the “Log into your server” heading, and locate the word “Plesk.

If “Plesk“ is not listed, you do not have Plesk installed. Manually install PHP using the steps below (without the use of Plesk). If your server has Plesk installed, you can add PHP support through Plesk directly.

Find out if your server uses Plesk by viewing in manage.liquidweb.com.

The following information provides a step by step breakdown of each installation process. This article will provide steps for Windows Server 2016 and Plesk Onyx (if you have Plesk currently installed). Use these same steps as a guideline for Windows Server 2008 or 2012. Besides, the older versions of Plesk will use similar steps.

As with any managed Liquid Web server, as a valued customer, if you do not feel comfortable performing the PHP software installation independently, please contact our support team for additional assistance. Liquid Web support will be happy to walk you through the steps, answer any questions you may have, or complete the installation for you if needed.

Note:
As with any software change, we recommend that you have a valid backup before starting this process.

To install PHP using Plesk, you will navigate through the Updates and Upgrades option within Plesk. This method will automatically download and install PHP directly from the Plesk Control Panel. Listed are the steps to install PHP using Plesk:

  1. Login to Plesk as the admin user.
  2. Choose Tools & Settings, then select Updates and Upgrades.Installing PHP using Plesk.
  3. Click Add/Remove Components.Installing PHP using Plesk.
  4. From the Add and Remove Product Components page you will need to expand the Plesk hosting features. Select install next to the desired PHP version. Click Continue and you will see the installation process finish.Installing PHP using Plesk.
Note:
You should never attempt to make changes outside of Plesk that are directly supported through Plesk (such as installing PHP).

Once PHP has successfully installed, it will require enabling on a per domain basis. To enable PHP through Plesk, follow these steps:

  1. From Plesk, choose Domains on the left-hand side.
  2. Select your domain name.
  3. Choose Hosting Settings.
  4. Under Web Scripting and Statistics check the box to Enable PHP.
  5. Select the proper PHP version next to PHP support.
  6. Click OK.

That’s it! You are now ready to verify that PHP is working.

There are several ways to install PHP on Windows Server 2016 (without Plesk). Since the manual method is more complex and requires manual configuration to IIS, the recommended approach is using the Web Platform Installer. The Web Platform Installer will automatically download PHP and will configure the IIS handlers for you.

To install PHP using the Web Platform Installer, follow the steps provided below:

  1. Connect to your server using RDP with an Administrator user.
  2. Open Internet Information Systems (inetmgr.exe).
  3. Select the server name (under “Start Page” on the left hand side of IIS).
  4. Choose “Get New Web Platform Components” from the Actions pane.
    1. If the Web Platform Installer is not already installed you will be directed to a website to install the Web Platform Installer.
      1. Download and run the Web Platform Installer.
      2. You can now select “Get New Web Platform Components” from the Actions pane and proceed with step 5.
    2. If the Web Platform  Installer extension is already installed, it will open.
  5. From the Web Platform Installer search for “PHP 7”.
  6. Select the version of PHP that you wish to install and click “Add”, “Install”, “I Accept
  7. After the installation completes click “Finish”.

Installing PHP Without Plesk.

Once you have PHP installed, the next step is to verify that PHP is working correctly. You can do this by adding any PHP script to the website and manually navigating to the page in your browswer. The following steps explicitly explain the process of how to create a PHP page under a site in IIS. This process will then result in the output of information about PHP’s configuration. Commonly referred to as a “PHP info page,” we show you the steps needed to create one:

Note:
A PHP info page can contain sensitive data about versioning and enabled components. While creating a temporary page is typically ok, after use, we recommend you delete the page as soon as possible.
  1. Connect to your server using RDP with an Administrator user.
  2. Open Internet Information Systems (inetmgr.exe).
  3. Expand the server name (under “Start Page” on the left hand side of IIS).
  4. Expand “Sites”.
  5. Right click the site name and choose “Explore”.
  6. Within the directory that opened create a file named phpinfo.php with the following contents:<?php
    phpinfo();
    ?>
  7. Navigate to the site specifying the phpinfo.php we created. Example : http://domain.com/phpinfo.php
  8. If everything went well you should be shown a page that displays the PHP version and other information.
  9. Delete the phpinfo.php file we created earlier.
  10. The PHP Info Page shows the version of PHP you are currently using.Now that PHP is installed and working correctly you are ready to upload your code or get started with one of many PHP based content management systems of your choice.

How to Install PIP on Windows

One of the best tools to install and manage Python packages is called Pip. Pip has earned its fame by the number of applications using this tool. Used for its capabilities in handling binary packages over the easy_installed packaged manager, Pip enables 3rd party package installations. Though the newest versions of Python come with pip installed as a default, this tutorial will show how to install Pip, check its version, and show some basic commands for its use.

Python is an open-source programming language that allows you to run applications and plugins from a wide variety of 3rd party sources (or even applications you develop yourself) on your server. Python is cross-platform, meaning that you can run it on a number of different operating systems, including Windows Server OS.

 

Pre-flight Check

Before you can install Pip on your server, you’ll need to confirm that Python is installed.

The simplest way to test for a Python installation on your Windows server is to open a command prompt (click on the Windows icon and type cmd, then click on the command prompt icon). Once a command prompt window opens, type python and press Enter. If Python is installed correctly, you should see output similar to what is shown below:

Python 3.7.0 (v3.7.0:1bf9cc5093, Jun 27 2018, 04:59:51) [MSC v.1914 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.

If you receive a message like:

Python is not recognized as an internal or external command, operable program or batch file.

Python is either not installed or the system variable path hasn’t been set. You’ll need to either launch Python from the folder in which it is installed or adjust your system variables to allow Python to be launched from any location.

For more information about installing and using Python, see the Python.org website.

 

Installing Pip

Once you’ve confirmed that Python is correctly installed, you can proceed with installing Pip.

  1. Download get-pip.py to a folder on your computer.
  2. Open a command prompt and navigate to the folder containing get-pip.py.
  3. Run the following command:python get-pip.py
  4. Pip is now installed!

You can verify that Pip was installed correctly by opening a command prompt and entering the following command:

pip -V

You should see output similar to the following:

pip 18.0 from c:\users\administrator\appdata\local\programs\python\python37\lib\site-packages\pip (python 3.7)

Now that Pip is installed and configured, you can begin using it to manage your Python packages. For a brief overview of the available commands and syntax for Pip, open a command prompt and enter:

pip help

All of Liquid Web’s Windows core-managed servers can run Python and our support team can assist with installation and verification if needed. ActiveState Python 2.6.5 can be installed on Plesk Fully Managed servers if desired.

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.

 

SQL Database Migration with SSMS

Migrating MSSQL between servers can be challenging without the proper guidelines to keep you on track. In this article, I will be outlining the various ways to migrate Microsoft SQL Server databases between servers or instances. Whether you need to move a single database,  many databases, logins or stored procedures and views we have you covered!

There are many circumstances where you will need to move a database or restore databases. The most common reasons are:

 

  • Moving to an entirely new server.
  • Moving to a different instance of SQL.
  • Creating a development server or going live to a production server.
  • Restoring databases from a backup.

 

There are two main ways to move SQL databases. Manually with Microsoft SQL Server Management Studio (SSMS) or with the command line. The method you choose depends on what you need to accomplish. If you are moving a single database or just a few, manually backing up and restoring the databases with SSMS will be the easiest approach. If you are moving a lot of databases (think more than 10) then using the command line method will speed up the process. The command line method takes more prep work beforehand, but if you are transferring dozens of databases, then it is well worth the time spent configuring the script instead of migrating each database individually. If you aren’t sure which method to use, try the manual approach first while you get comfortable with the process. I recommend reading all the way through for a deeper understanding of the methodology.

 

Useful References for Terminology

SSMS – An acronym for Microsoft SQL Server Management Studio.

Source Server – The server or instance you are moving databases from or off.

Destination Server – The server or instance you are moving databases to.

 

Moving SQL databases with the manual method can be very easy. It is the preferred process for transferring a few or smaller databases. To follow this part of the guide, you must have MSSQL, and Microsoft SQL Server Management Studio (SSMS) installed.

 

1. Begin by logging into the Source server (the server you are moving databases from or off of). You will want to open Microsoft SQL Server Management Studio by selecting Start > Microsoft SQL Server >  Microsoft SQL Server Management Studio.

2.Log into the SQL server using Windows Authentication or SQL Authentication.

3. Expand the server(in our case SQL01), expand Databases, select the first database you want to move (pictured below).

Select your database within Microsoft SQL Server Management Studio.

4. Right click on your database and select Tasks then click Back Up.

Back up button in Microsoft SQL Server Management Studio.

5. From here you are now at the Back Up Database screen. You can choose a Backup Type such as Full or Differential, make sure the correct database is selected, and set the destination for the SQL backup. For our example, we can leave the Backup Type as Full.

6. Under Backup Type, check the box for “Copy-only backup.” If you are running DPM or another form of server backup, backing up without the Copy-Only flag will cause a break in the backup log chain.

7. You will see a location under Destination for the path of the new backup. Typically you will Remove this entry then Add a new one to select a folder that SQL has read/write access. Adding a new Backup Destination shows a path similar to the following:

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\

This C:\ path is where your stored database backup is. Note this location for later reference, as this is the default path to stored backups and will have to have proper read/write access for SQL services.

Note:
Advanced users may be comfortable leaving the destination as is, provided the permissions are correct on the output folder.

8. Next, append a filename to the end of this path such as AdventureWorks2012-081418.bak – Be sure to end the filename with the extension .bak and select OKSet the file name with the .bak extension in Microsoft SQL Server Management Studio

10. Once you have pressed OK on the Select Backup Destination prompt, you are ready to back up the database! All you need to do now is hit OK, and the database will begin backing up. You will see a progress bar in the bottom left-hand corner, and when the backup is complete, a window will appear saying ‘The backup of database ‘AdventureWorks2012’ completed successfully.

Navigate to the destination path, noted earlier, (in this case C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\) you will see your newly created file (in this case AdventureWorks2012-081418.bak) – Congratulations! This file is the full export of your database and is ready to be imported to the new server.  If you have more databases, then repeat the steps above for each database you are moving. After copying all database process to the next step of restoring databases to the destination server.

 

You should now have a .bak file of all your databases on the source server. These database files need to be transferred to the destination server. There are numerous ways to move your data to the destination server; you can use USB, Robocopy or FTP. After copying a database you can store it on your destination server,  for our example, we have stored it on the C drive in a folder named C:\dbbackups .

1. Open Microsoft SQL Server Management Studio.

2. Log in to the SQL server using Windows Authentication or SQL Authentication.

3. Expand the server and right click on Databases and select Restore Database.

4. The Restore Database screen looks very similar to the Back Up Database screen.Under Source, you will want to select Device instead of Database. Selecting Device allows you to restore directly from a file. Once you’ve chosen Device, click the browse icon […]

5. Select Add, then navigate to the folder in which your .bak files lives. (In this case, C:\dbbackups).

6. Select the first database .bak you would like to restore and click OK.

Select the .bak file to import your database into the destination server via SSMS.

7. Click OK and now you are ready to import the database. Before importing, let’s take a look at the Options section on the left-hand side. Under Options, you will see other configurations for restoring databases such as Overwrite the Existing Database, Preserve the Replication Settings and Restrict Access to the Restored DatabaseIn this case, we are not replacing an existing database so I will leave all these options unchecked. If you wanted to replace an existing database (for example, the backed up database has newer data than on the destination server or you are replacing a development or production database) then simply select Overwrite the Existing Database.

Note:
Advanced users may be comfortable leaving the destination as is, provided the permissions are correct on the output folder.

8. Clicking OK  begins the restore process as indicated by the popup window that reads ‘Database ‘AdventureWorks2012′ restored successfully.’ You have migrated your database from the source to the destination server.

Repeat this process for each database that you are migrating. You can then update path references in your scripts/application to point to the new server, verify that the migration was successful.

 

After importing your databases if you are unable to connect using your SQL login, you may receive the error ‘Login failed for user ‘example.’ (Microsoft SQL Server, Error: 18456).‘ Because the database is in the Traditional Login and User Model, logins are stored separately in the source server and credentials are not contained within the database itself. From this point on, the destination server can be configured to use the Contained Database User Model which keeps the logins in your database and out of the source server. (You can read more about this here.)Until then, we will have to move and interact with the users as part of the Traditional model. Continue below to proceed with the migration of your SQL users.

Backing up and restoring the databases did move your SQL logins relation to the databases (your logins are still associated with the correct databases with the correct permissions) but the actual logins itself did not transfer to the new server.  You can verify this by opening SSMS on the destination server and navigating to Server > Security > Logins. You will notice that any custom SQL logins you created on the previous server did not transfer over here, but if you go to Server > Databases > Your Database (AdventureWorks2012 in this case) > Security > Users you’ll see the correct login associated with the database.

If you have one or two SQL users, you can just delete the user’s association to the database in Servers > Databases > AdventureWorks2012 > Security > Users, re-create the user in Server > Security > Logins and map it to the proper database.

If you have a lot of logins, you will have to follow an additional process outlined below. To migrate all SQL users, open a New Query window on the source server and run the following script:

SQL Login Script
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

This script creates two stored procedures in the source database which helps with migrating these logins. Open a New Query window and run the following:
EXEC sp_help_revlogin

This query outputs a script that creates new logins for the destination server. Copy the output of this query and save it for later. You will need to run this on the destination server.

Once you’ve copied the output of this query, login to SSMS on the destination server and open a New Query window. Paste the contents from the previous script (it should have a series of lines that look similar to — Login: BUILTIN\Administrators
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE = [master]) and hit Execute.

You have now successfully imported all SQL logins and can now verify that the databases have been migrated to the destination server by using your previous credentials.

Views and stored procedures will migrate with the database if you are using the typical SQL Tape backups. Follow the instructions below if you need to migrate views and stored procedures independently.

  1. Open Microsoft SQL Management Studio on the Source server.
  2. Log in to your SQL server.
  3. Expand the server and as well as Databases.
  4. Right click on the name of your database and go to Tasks > Generate Scripts.
  5. Click Next.
  6. We will change Script entire database and all database objects to Select specific database objects and only check Views and Stored Procedures.Transfer Stored Procedures and Views within Microsoft SQL Server Management Studio
  7. Click Next, notice the Save to File option. Take note of the file path listed. In my case, it is C:\Users\Administrator\Documents\script.sql – The path of saved views and stored procedures.
  8. Click Next >> Next >>Finish, and select C:\Users\Administrator\Documents\script.sql and copy it to the destination server.
  9. Go to the destination server, open SSMS and log in to the SQL server.
  10. Go to File > Open > File or use the keyboard shortcut CTRL+O to open the SQL script. Select the file C:\Users\Administrator\Documents\script.sql to open it.
  11. You will see the script generated from the source server containing all views and stored procedures. Click Execute or use the keyboard shortcut F5 and run the script.
Note:
Unfortunately, there is no built-in way to do this with the command line. There are 3rd party tools and even a tool by Microsoft called mssql-scripter for more advanced scripting.

You have now migrated the views and stored procedures to your destination server! Repeat this process for each database you are migrating. A little guidance goes a long way in database administration. 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.

Looking for a High Availability, platform-independent SQL service that is easily scalable and can grow with your business? Check out our SQL as a Service product offered at Liquid Web. Speak with one of our amazing Hosting Advisers to find the perfect solution for you!

 

Improving Security for your Remote Desktop Connection

Remote Desktop Protocol (RDP) is the easiest and most common method for managing a Windows server. Included in all versions of Windows server and has a built-in client on all Windows desktops. There are also free applications available for Macintosh and Linux based desktops. Unfortunately, because it is so widely used, RDP is also the target of a large number of brute force attacks on the server. Malicious users will use compromised computers to attempt to connect to your server using RDP. Even if the attack is unsuccessful in guessing your administrator password, just the flood of attempted connections can cause instability and other performance issues on your server. Fortunately, there are some approaches you can use to minimize your exposure to these types of attacks.

Using a Virtual Private Network (or VPN) is one of the best ways to protect your server from malicious attacks over RDP. Using a VPN connection means that before attempting to reach your server, a connection must first be made to the secure private network. This private network is encrypted and hosted outside of your server, so the secure connection itself does not require any of your server’s resources. Once connected to the private network, your workstation is assigned a private IP address that is then used to open the RDP connection to the server. When using a VPN, the server is configured only to allow connections from the VPN address, rejecting any attempts from outside IP addresses (see Scoping Ports in Windows Firewall). The VPN not only protects the server from malicious connections, but it also protects the data transmitted between your local workstation and the server over the VPN connection. For more information, see our article What is a VPN Tunnel?

Note
All Liquid Web accounts come with one free Cloud VPN user. For a small monthly fee, you can add additional users. See our Hosting Advisors if you have any questions about our Cloud VPN service.

Like using a VPN, adding a hardware firewall to your server infrastructure further protects your server from malicious attacks. You can add a Liquid Web firewall to your account to allow only RDP connection from a trusted location. Our firewalls operate in much the same way that the software Windows firewall operates, but the functions are handled on the hardware itself, keeping your server resources free to handle legitimate requests. To learn more about adding a hardware firewall to your account, contact our Solutions team. If you already have a Liquid Web firewall in place, our Support team can verify that it is correctly configured to protect RDP connections.

Similar to using a VPN, you can use your Windows firewall to limit access to your RDP port (by default, port 3389). The process of restricting access to a port to a single IP address or group of IP addresses is known as “scoping” the port. When you scope the RDP port, your server will no longer accept connection attempts from any IP address not included in the scope. Scoping frees up server resources because the server doesn’t need to process malicious connection attempts, the rejected unauthorized user is denied at the firewall before ever reaching the RDP system. Here are the steps necessary to scope your RDP port:

  1. Log in to the server, click on the Windows icon, and type Windows Firewall into the search bar.
    Windows Firewall Search
  2. Click on Windows Firewall with Advanced Security.
  3. Click on Inbound Rules
    Inbound Firewall Rules section
  4. Scroll down to find a rule labeled RDP (or using port 3389).
  5. Double-click on the rule, then click the Scope tab.
    RDP Scope
  6. Make sure to include your current IP address in the list of allowed Remote IPs (you can find your current public IP address by going to http://ip.liquidweb.com.
  7. Click on the radio button for These IP Addresses: under Remote IP addresses.
  8. Click OK to save the changes.

While scoping the RDP port is a great way to protect your server from malicious attempts using the Remote Desktop Protocol, sometimes it is not possible to scope the port. For instance, if you or your developer must use a dynamic IP address connection, it may not be practical to limit access based on IP address. However, there are still steps you can take to improve performance and security for RDP connections.

Most brute force attacks on RDP use the default port of 3389. If there are numerous failed attempts to log in via RDP, you can change the port that RDP uses for connections.

  1. Before changing the RDP port, make sure the new port you want to use is open in the firewall to prevent being locked out of your server. The best way to do this is duplicate the current firewall rule for RDP, then update the new rule with the new port number you want to use.
  2. Login to your server and open the Registry editor by entering regedit.exe in the search bar.
  3. Once in the registry navigate to the following: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Terminal Server\WinStations\RDP-Tcp
  4. Once there scroll down the list till you find “PortNumber”
  5. Double-clicking on this will bring up the editor box.
  6. Change it from HEX to DEC so it’s in numbers.
  7. Set the port number here and hit OK (you can use whatever port number you wish, but you should pick a port that already isn’t in use for another service. A list of commonly used port numbers can be found on MIT’s website.)
  8. Close the registry editor and reboot the server.
  9. Be sure to reconnect to the server with the new RDP port number.

 

Upgrading PHP on Windows

Performing an upgrade to PHP on Windows Server

Keeping your software and applications up to date is a crucial part of maintaining security and stability in your web hosting systems. Unfortunately, updating system components and back-end software can sometimes be a frustrating and a difficult process. However, thanks to Microsoft’s Web Platform Installer, upgrading PHP on a Windows server with IIS is as simple as a few clicks.

Continue reading “Upgrading PHP on Windows”

Choosing Your Cloud Sites Technology Setup

Behind Cloud Sites, racks full of both Linux and Windows servers power over 100,000 sites and applications. Every Windows-based page is served from clusters built and optimized especially for Windows, and every Linux-based page is served from clusters built and optimized especially for Linux. We use advanced load balancing technologies to automatically detect the type of technology you are running and route each request to the proper pool of servers.

This is a great example of the power of cloud computing, since you no longer have to make a hosting choice between Linux and Windows. Both PHP and .NET are included, allowing you to choose the technology you need site by site.
Continue reading “Choosing Your Cloud Sites Technology Setup”