Exercise - Set up sandbox environment

create a sql server

az sql server create \
    --name $SERVERNAME \
    --resource-group $RESOURCEGROUP \
    --location $LOCATION \
    --admin-user $ADMINLOGIN \
    --admin-password $PASSWORD

create a database

az sql db create --resource-group $RESOURCEGROUP \
    --server $SERVERNAME \
    --name marketplaceDb \
    --sample-name AdventureWorksLT \
    --service-objective Basic

get the connection string

az sql db show-connection-string --client sqlcmd --name marketplaceDb --server $SERVERNAME | jq -r

sqlcmd -S tcp:server8297.database.windows.net,1433 -d marketplaceDb -U <username> -P <password> -N -l 30

create a vm

az vm create \
  --resource-group $RESOURCEGROUP \
  --name appServer \
  --image UbuntuLTS \
  --size Standard_DS2_v2 \
  --generate-ssh-keys

connect to vm

ssh 168.62.197.45

install tools on linux vm

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools unixodbc-dev

Exercise - Restrict network access

Firewall rules

AZ sql has a built in firewall to all and deny netowkr access to database and the service itself.

• All IP blocked by default
• Use IP ranges to allow clients
• server level
  • allow access to az services
  • IP address rules
  • vnet rules
• Database level
  • IP address rules

Server-level firewall rules

Allow client to access the Server. Allow access to AZ services (does what it says) Use this when you have PAAS such as logic apps or functions IP address rules - specific public IPs (such as your home) Virtual network rules - which AZ networks can connect

Database-level firewall rules

Scoped to an individual databbase Only allows IP address rules Rules are replicated with the db if you move it to a new server More overhead.

Restricting network access in practice

Best practice - use database level IP rules when Use server level IP rules for admins or DB's with the same requirements

sqlcmd -S tcp:server8297.database.windows.net,1433 -d marketplaceDb -U 'AzureAdmin' -P 'Password$$$$' -N -l 30

allow access rule EXECUTE sp_set_database_firewall_rule N'Allow appServer database level rule', '168.62.197.45', '168.62.197.45';

Exercise - Control who can access your database

create a new user

CREATE USER ApplicationUser WITH PASSWORD = 'YourStrongPassword1$$$'; GO

we then removed selected permissions from a specific table

Exercise - Secure your data in transit, at rest, and on display

AZ SQL uses TLS.
AZ SQL uses TDE to encrypt data at rest. Enabled by default. Database > Security > TDE

Dynamic data masking

can mask sensitive data there are standard masks (credit card, email, number) or custom masks not masked for admin user, but is masked for app user.

Exercise - Monitor your database

Azure SQL Database auditing

• audit trail of spec'd events
• report on activity
• logs are written to append blobs in a storage account
• can view or send to log analytics

Auditing in practice

• best practice -> dont do server and database auditing together
• use server auditing
• server > security > auditing
• database > security > auditing
  • shows it is enabled at the server level
  • 'view audit logs' to see.

Advanced Data Security for Azure SQL Database

Set of advanced capabilities

• Data discovery and classification (find sensitive data)
• vuln. assessment (looks for problems)
• advance threat protection (watches for attacks)

Setup and configuration

At the server level security > sec center