How to Secure Azure SQLMI in Your Environment?

Description

SQL Managed Instance (SQL MI) provides native Virtual Network (Net) integration while Azure SQL 

Database enables restricted Virtual Network (Net) access using Net Endpoints. 

SQL MI helps bridge the gap between Azure SQL Database and On-premises SQL Server due to being built on an instance scoped configuration model.

BASELINE CONFIGURATION

Config Name Description Solution GE Control ID
SQL MI Name Standard needed for the SQL MI Name All SQL MI servers should be created using following naming convention uai*-.database.windows.net EA Standard
SQL MI Location Location for SQL MI Make sure the SQL Managed Instance is created in the same region of the resource group EA Recommendation
Authentication Method Allowed Authentication Method The only allowed authentication method at this time is "SOL authentication". Azure AD authentication is not allowed at this time for the SOL Managed Instance. Make sure the Administrator Password is stored securely in the Key Vault Secret. Check Password Policy below for creating SQL MI passwords SEC 4.1
Microsoft Defender for SOL Managed Instance Enable Defender for the SOL Managed Instance Enable the Microsoft Defender for the SOL Managed Instance in Production environment. SEC 1.1, 1.4
Identity Use System-assigned managed identities Use system-assigned managed identities to enable central access management between this database and other Azure resources EA Standard
Service Principal Service Principal should be off Service Principal should be off, as it is in preview mode EA Standard
Lock Prevent Accidental Deletion Must have at least a delete lock enabled to prevent accidental deletion of SQL MI EA Standard
Encryption in Transit Data Transfer using TLS must use TLS 1.2 Select TLS version 1.2 as the minimum version during creation SEC 7.2
Public Access Public access to SQL Server Deny Public access to SQL Server by Default SQC 1.2
Connection Policy Connectivity Policy to the SQL MI Proxy Mode for the connection to the SQL MI is not allowed. EA Recommendation
Use Private Endpoint Target SQL Managed Instance should be accessible over private network of either Azure or GE internal network. Create private link for target SOL Managed Instance and use that link for accessing the SOL server over the VNET. Access to the SQL MI outside the subscription should be restricted. SEC 8.4
Transparent Data Encryption Transparent data encryption (TDE) encrypts your databases, backups, and logs at rest without any changes to your application Must be encrypted used the Customer Managed Key only. SEC 7.4
Audit Logs Enable SQL Audit Logs Logs must be stored in Gas Power Cyber approved logging destination: central analytics workspace in 328-gp-azr-ops SEC 3.11

Baseline Configuration – SQL MI Database

Config Name Description Solution GE Control ID
SQLMI Database Name Standard Needed for the SQL MI Database Name SQL MI should be created using following naming convention uai*--db EA Standard
SQL MI Database Instance SQL MI Database creation in SQL MI Applications are not allowed to share SQL MI. all SQL MI Database should be created in dedicated SQL MI only EA Standard
Instance Instance Requirements Default 2 core CPU, 8 GB Memory is default for production provisioned but DEV & QA environment teams server less option is recommended EA Recommendation
Collation Instance collation defines rules that sort and compare data and cannot be changed after instance creation Must user default collation EA Recommendation
Geo-Replication Enables active geo-replication (Geo-DR) allowing the application to perform quick disaster recovery in case of a data center scale outage Must use Geo Replication only on production environment EA Recommendation
Maintenance Window During a maintenance event, databases are fully available and accessible but some of the maintenance upcates require a failover as Azure takes databases Must use “system default EA Recommendation

Tagging Considerations

Policy Name Description Solution
UAI tag All speech services must be tagged with a valid UAI - Example: Key: uai
- Value: uai1234567
- Use lower case name and value
Env tag All speech services must be tagged with a tag corresponding to the application environment - Example: Key : env, Value: prd
- For Valid envs see item 5.2 in cloud controls document
- Use lower case name and value
Appname tag Must tagged with application short name where applicable - Example: Key: appname
- Value: ABC123

Ready to experience the full power of cloud technology?

Our cloud experts will speed up cloud deployment, and make your business more efficient.  

Resource Standards and Policies

Config Name Description Solution
Naming convention Resource follows standard naming convention Apply naming standards based on the following guidelines
Naming & Tagging Standards
Password Policy for the SQL MI Follow the GE Password Policy for Creating SQL MI Password Min Length = 16 characters
(Should be combination of Lower case Upper Case characters, Specials Characters and Numbers)

Network Considerations

Config Name Description Solution
Private endpoints Deny public internet access Ensure that key vault is accessible only over Stakeholder private network

Identity and Access Management

Config Name Description Solution Mandated/Optional IAM Policy CF Template
Built-in Azure Role to be used with system managed identity for reading secrets Allows Key Get, WrapKey. UnwrapKey Permissions for the Managed Identity to be used with SOL Server. Assign System Managed identity with SOL MI. The SQL MI with private end point cannot access Key Vault with private end point + firewall. if User Managed identity is assigned KeyVault Crypto Service Encryption User Mandated

Operational Considerations

Config Name Description Solution
Mandatory Backup Maintenance Requirements Make sure regular backups are being taken for the SQLMI database
Maintenance Ensure Regular automated Backups Backup retention for Prod instance should be 35 days
Maintenance window should be mentioned as per discussion with app team and DBA team.
Performance Insight of database Follow Query Performance Insight for Azure SQL MI database
Audit Logs and Diagnostic Logs Enable Audit and Diagnostics Logs for the SQL MI database and SQL MI Diagnostic Logs should be enabled and The Logs should be sent to the Log Analytics workspace.
Following logs should be captured at minimum
-SQLinsights
- Errors
- Timeouts
- Blocks
- Deadlocks
- Basic
- InstanceAndAprAdvansed
- WorkloadManagement
Patch Management Databases and apps must be patched regularly using over the air updates OTA Updates are more efficient for Fixing Security vulnerabilities, address software stability issues & Deploy new or improved features

Not Approved Features/ NTI Needed

Config Name Description Solution
Power BI Transform Data. Visualize Data, Unify Data Not approved as of now
Power Apps Enables users to build mobile & web-based forms and apps with low or no code Not approved as of now
Power Automate Create automated workflows between your favourite apps and service to synchronize files Not approved as of now

ISmile Technologies is a proud partner of the top public cloud providers AWS, Microsoft Azure, and Google Cloud. The company can provide you with a cloud governance model and core framework to ensure your operations in the public cloud are scalable and secure. Schedule a free assessment today.

CLOUD ENGINEER

Mahaboob Khan

A Cloud Engineer at ISmile Technologies, he had extensive experience working on Microsoft Azure which involves activities like Implementation, Managing and troubleshooting the User related issues. With automation tools like Azure ARM Template, Terraform, and Azure DevOps, he helps our client to automate deployment of IaaS and PaaS services.

Register a Free Cloud ROI Assessment Workshop

Register a Free Cloud ROI Assessment Workshop

Get a Detailed assessment report with recommendations with an assessment report

Schedule free Workshop
Register a Free Cloud ROI Assessment Workshop
Register a Free Cloud ROI Assessment Workshop

Liked what you read !

Please leave a Feedback

0 0 votes
Article Rating
guest
0 Comments
Inline Feedbacks
View all comments

Related articles you may would like to read

How can Docker Containerization Help in Reducing CICD Deployment Costs
0
Would love your thoughts, please comment.x
()
x
Proposals

Know the specific resource requirement for completing a specific project with us.

Blog

Keep yourself updated with the latest updates about Cloud technology, our latest offerings, security trends and much more.

Webinar

Gain insights into latest aspects of cloud productivity, security, advanced technologies and more via our Virtual events.

ISmile Technologies delivers business-specific Cloud Solutions and Managed IT Services across all major platforms maximizing your competitive advantage at an unparalleled value.

Request a Consultation