The SQL Server Security Ground to Cloud workshop uses the following components. Read through this list, and in the Activities that follow you will see specific steps for each installation. You can also simply read through each of these steps, and observe the activities in the workshop if you cannot install the hands-on poritions.
- A Microsoft Windows 10 or Higher Workstation: For this workshop, you will use a Microsoft Windows system as the base workstation, although Apple and Linux operating systems can be used in production. You'll see instructions for multiple options, from a physical system you have administrative access on, to Virtual Machines you can use for free on Windows, Linux and Apple systems. You can also use a VM in the cloud provider of your choice.
- Microsoft Azure: This workshop uses the Microsoft Azure platform for the cloud database and the Microsoft Defender products. You can also host your workstation there if desired. You can use a free Azure account, an MSDN Account, your own account, or potentially one provided for you, as long as you can create about $100.00 (U.S.) worth of assets.
- SQL Server Developer Edition: An installation of the SQL Server Developer Edition, the Database Engine feature.
- Microsoft Azure SQL Database: A Microsoft Azure SQL Database (smallest edition) allows for testing and exploration of SQL Server security on that platform.
- Microsoft Azure Defender Account: This is the primary tool from Microsoft for securing and reporting on security for your on-premises and in-cloud environments.
- Python: A simple set of Create, Read, Update and Delete (CRUD) applications to show traffic to and from the on-premises and in-cloud environments.
All of the following activities must be completed prior to class - there will not be time to perform these operations during the workshop. The complete pre-requisites steps will take from 2-6 hours.
Activity 1: Set up a Microsoft Azure Account
You have multiple options for setting up Microsoft Azure account to complete this workshop. You can use a free account, a Microsoft Developer Network (MSDN) account, a personal or corporate account, or in some cases a pass may be provided by the instructor. (For most classes, the MSDN account is best)
Option 1 - Free Account
The free account gives you twelve months of time, and a limited amount of resources. Set this up prior to coming to class, and ensure you can access it from the system you will bring to the class.
You can only use the Free subscription once, and it expires in 12 months.
Option 2 - Microsoft Developer Network Account (MSDN) Account
The best way to take this workshop is to use your Microsoft Developer Network (MSDN) benefits if you have a subscription.
Option 3 - Use Your Own Account
You can also use your own account or one provided to you by your organization, but you must be able to create a resource group and create, start, and manage a Workstation.
Option 4 - Use an account provided by your instructor
Your workshop invitation may have instructed you that they will provide a Microsoft Azure account for you to use. If so, you will receive instructions that it will be provided.
Activity 2: Create a Workstation and Install SQL Server Developer Edition and the Sample Application
You will use a Windows 10 or higher workstation for this course. After you complete that installation, you will install SQL Server 2019 on this workstation, along with the SQL Server Management Studio tool, and a sample application.
All steps and commands from here on should be done in the Workshop system you create - if you are using a Virtual Machine, ensure you are following these steps on that system, not on the host computer.
You can use a local Virtual Machine, or a physical workstation in a test configuration that can be reformatted if necessary. Choose one of the following options if you are planning to do the hands-on steps either in class or on your own.
Create your own Workstation
This course will cover some of the security features of SQL Server 2022, in a demonstration fashion. If you would like to install SQL Server 2022 instead of SQL Server 2019, check this reference.
You can create your workstation for this course using one of the following methods:
- Local Workstation - You can use a physical workstation for this course, assuming you can install software on that system and you have complete control of the administration account. This needs to be a system you could format and start over multiple times, so do not use your work or production workstation for this course. This system should have a minimum of 8GB of RAM, 2 processors, and 150GB drive space free.
- Local Virtual Machine - You can download a Windows 10 Workstation Image for VirtualBox, Hyper-V, VMWare, or Parallels for free here, or you can use your own installation media. This system should have a minimum of 8GB of RAM and 150GB drive space free.
- Microsoft Azure Virtual Machine. Select a system that has at least 2 processors, a minimum of 8GB of RAM and 150GB drive space free. Ensure you create all assets for this course in a single Resource Group, so that you can delete all these assets when you complete the workshop. Do not use your organization's production subscription for this workshop.
- Another Cloud Provider's Virtual Machine Environment. See the provider's documentation for this process. This system should have a minimum of 8GB of RAM and 150GB drive space free. Do not use your organization's production subscription for this workshop.
If you use a Microsoft Azure Virtual Machine, ensure that you "Stop" the VM in the Portal to ensure that you do no exceed the cost limits on this account. Simply shutting down the Virtual Machine will continue to cost you. You can read more about properly stopping a Microsoft Azure Virtual Machine here.
Apply Operating System Updates
For the rest of the workshop, you will use the Development Environment you just set up to run all steps.
Next, ensure all of your updates are current on your development system. You can use the Windows Update graphical tool or PowerShell.
Install SQL Server Database Engine
In this activity, you will install SQL Server, selecting at least the "Database Engine" Feature. If you select more than the Database Engine components, you will have more configuration options you will need to consider. For this course, we will focus on the Database Engine feature, so that is all you need to install. You can add more features later by using the Setup Center on your system.
For the next two steps, you can see a walkthrough video of this process here.
Install SQL Server Management Studio
Install the Python Sample Application
You do not need to be a developer to take this course, but having an application to use for SQL Server makes it more "real world" and what you will face in production. The steps below are comprehensive and simple to implement. This course uses a Python sample application.
-
Now open an elevated permissions (run as Administrator) PowerShell command-line and create a directory for the course:
cd \ mkdir c:\SampleDBApp cd \SampleDBApp
- In that same window, install Python using Chocolatey:
choco install -y python
- In that same window, install the package manager for Python, and use that to install the connection code for SQL Server. Some users have to restart at this point, if the below code doesn't work initially, try rebooting and it should work afterwards.
python -m pip install -U pip python -m pip install pyodbc
- In that same window, Start the Notepad program to create the first iteration of the sample application:
notepad SimpleConnection.py
Answer "Y" to create a new file, and paste the following text in the file, then save and close it. You will alter this file later.
import pyodbc
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};Server=(local);Database=master;Trusted_Connection=yes;Application Name=DBConnectionAppFromPython;')
cursor = cnxn.cursor()
# Connect and return version information
cursor.execute("SELECT @@version;")
row = cursor.fetchone()
while row:
print(row[0])
row = cursor.fetchone()
You can test this application with the following command if desired:
python SimpleConnection.py
If you are using an Azure Virtual Machine, when you are done with the installation and with each time period of lab exercises, shut down the Virtual Machine from the Microsoft Azure Portal by selecting "Stop" in the Virutal Machine Panel. Simply shutting down the Virtual Machine using the Power Off feature in the operation system does not release the assets and you are charged until the machine is Stopped in the Portal. https://build5nines.com/properly-shutdown-azure-vm-to-save-money/
Activity 3: Create a Microsoft Azure SQL Database
The instructions that follow use the Microsoft Azure Account you created earlier. If you created a Windows Virtual Machine in Microsoft Azure, use the same Resource Group for these steps.
Activity 4: Create a Microsoft Azure Defender for SQL Setup
The instructions that follow use the Microsoft Azure Account you created earlier. If you created a Windows Virtual Machine in Microsoft Azure, use the same Resource Group for these steps.
For Further Study
You now have a testing and classroom environment for this course. You will add more to this environment as you progress through the modules, and all steps from here on out should be taken on that system.
Next Steps
Next, Continue to 01 - The Database Security Landscape.