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*- |
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*- |
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.
