SQL Server DBA Tutorial 104 - SQL Server Remediate Vulnerabilities

*****************************************************************************************
* SQL Server Remediate Vulnerabilities *
*****************************************************************************************
SQL vulnerability assessment is an easy-to-configure service that can discover, track, and help you remediate potential database vulnerabilities. Use it to proactively improve your database security.

⇒ What is SQL vulnerability assessment?

SQL vulnerability assessment is a service that provides visibility into your security state. Vulnerability assessment includes actionable steps to resolve security issues and enhance your database security. It can help you to monitor a dynamic database environment where changes are difficult to track and improve your SQL security posture.

Results of the scan include actionable steps to resolve each issue and provide customized remediation scripts where applicable. You can customize an assessment report for your environment by setting an acceptable baseline for:

◉ Permission configurations
◉ Feature configurations
◉ Database settings

⇒ Remediate vulnerabilities

When a vulnerability scan completes, the report is displayed in the Azure portal. The report presents:

◉ An overview of your security state
◉ The number of issues that were found
◉ A summary by severity of the risks
◉ A list of the findings for further investigations

⇒ To remediate the vulnerabilities discovered:

1. Review your results and determine which of the report's findings are true security issues for your environment.

2. Select each failed result to understand its impact and why the security check failed.

3.As you review your assessment results, you can mark specific results as being an acceptable baseline in your environment. A baseline is essentially a customization of how the results are reported. In subsequent scans, results that match the baseline are considered as passes. After you've established your baseline security state, vulnerability assessment only reports on deviations from the baseline. In this way, you can focus your attention on the relevant issues.

4. If you change the baselines, use the Scan button to run an on-demand scan and view the customized report. Any findings you've added to the baseline will now appear in Passed with an indication that they've passed because of the baseline changes.

⇒ TDE (Transparent Data Encryption)

Create Master Key

We must first create the master key. It must be created in the master database, so as a precautionary measure I like to begin this statement with the USE MASTER command.

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
GO

Create Certificate protected by master key

Once the master key is created along with the strong password (that you should remember or save in a secure location), we will go ahead and create the actual certificate.

CREATE CERTIFICATE TDE_CHIRAGS_Cert
WITH
SUBJECT='Database_Encryption';
GO

/**Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.**/


The certificate’s name is “TDE_CHIRAGS_Cert” and I gave it a generic subject. Some Database Administrators like to put the name of the actual database that they are going to encrypt in there. It is totally up to you.
Create Database Encryption Key

Now, we must utilize our USE command to switch to the database that we wish to encrypt. Then we create a connection or association between the certificate that we just created and the actual database. Then we indicate the type of encryption algorithm we are going to use. In this case it will be AES_256 encryption.

USE DB
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_CHIRAGS_Cert;
GO

Enable Encryption

Finally, we can enable encryption on our database by using the ALTER DATABASE command.

ALTER DATABASE DB
SET ENCRYPTION ON;
GO

Note : Flow the Process shown in video.

😉Subscribe and like for more videos:
💛Don't forget to, 💘Follow, 💝Like, 💖Share 💙&, Comment

Tutorial Link :

Thanks & Regards,
Chitt Ranjan Mahto "Chirag"
_________________________________________________________________________________________
Note: All scripts used in this demo will be available in our website.
Link will be available in description.