Implement a secure environment practice questions

Revoke the CONNECT permission from 'sales_user' using REVOKE CONNECT FROM sales_user; then create a new login and update the application connection string.

Why wrong: Incorrect. REVOKE CONNECT is a database-level permission and cannot be applied to a server login. This command would fail or have no effect on the login's ability to connect.

Change the password again and ensure the application is restarted to clear the cache.

Why wrong: Incorrect. Changing the password again does not terminate existing active sessions, and the application may continue to use cached old credentials until the connection pool is refreshed or restarted, causing delay.

Enable auditing to monitor future logins and leave the login as is.

Why wrong: Incorrect. Auditing only records events; it does not revoke access. The compromised login remains active.

Use IP-based firewall rules for all client connections, including Azure services.

Why wrong: Incorrect. Using IP-based firewall rules for all client connections, including Azure services, is not a best practice because it unnecessarily exposes the database to public IP ranges and complicates management.

Create firewall rules with broad IP ranges (e.g., 0.0.0.0/0) to simplify management.

Why wrong: Incorrect. Creating firewall rules with broad IP ranges like 0.0.0.0/0 is a security risk and violates the principle of least privilege.

Enable the 'Allow Azure Services' firewall rule to allow connections from Azure services.

Why wrong: Incorrect. Enabling 'Allow Azure Services' opens access to all Azure services' IP ranges, which is overly broad and should only be used as a last resort.

Assign the SQL Security Manager role.

Why wrong: Incorrect because the SQL Security Manager role allows updating security policies, which is more than read-only access and violates the requirement to not modify security settings.

Assign the Contributor role.

Why wrong: Incorrect because the Contributor role allows creating and managing resources, which includes modifying security settings, thus granting excessive permissions.

Assign the SQL DB Contributor role.

Why wrong: Incorrect because the SQL DB Contributor role allows full management of databases, including security settings, which exceeds the required read-only access.

Configure a virtual network rule to allow traffic from a specific subnet.

Why wrong: Incorrect. Virtual network rules are used for Azure SQL Database, not Azure SQL Managed Instance. Managed Instance uses subnet-level NSGs and route tables.

Configure server-level IP firewall rules.

Why wrong: Incorrect. Server-level IP firewall rules are not supported for Azure SQL Managed Instance. It is fully VNet-integrated and does not have a public endpoint.

Enable service endpoints on the subnet.

Why wrong: Incorrect. Service endpoints are not supported for Azure SQL Managed Instance. They are used for Azure SQL Database and other PaaS services.

Configure active geo-replication between the databases and manually initiate failover if needed.

Why wrong: Active geo-replication replicates database-level security but not server-level security (firewall rules, server admin). This does not meet the requirement for the secondary to have the same security settings without manual configuration.

Use database copy to create a copy in the secondary region.

Why wrong: Database copy is a one-time action that does not maintain ongoing replication; any changes after the copy are lost, and security settings are not replicated.

Use backup and restore to copy the database to the secondary region.

Why wrong: Backup and restore does not provide continuous replication; it requires manual restore steps and security settings are not automatically transferred.

The server will be created, but an audit log will be generated.

Why wrong: No audit effect is specified; only deny is used.

The server will be created successfully with public network access enabled.

Why wrong: The deny effect will block creation.

The server will be created, but the databases will be denied creation.

Why wrong: The policy only targets servers, not databases.

The database will log only failed authentication attempts to the storage account for 90 days.

Why wrong: Both SUCCESSFUL and FAILED groups are specified.

The command will fail because the StorageAccountResourceId parameter is incorrect.

Why wrong: The parameter is valid if $storage.Id is a valid resource ID.

The command will enable auditing for the server rather than the database.

Why wrong: The cmdlet targets a specific database (db1).

All security audit events for the database.

Why wrong: Incorrect. Security audit events are not stored in these DMVs.

Failed login events at the server level.

Why wrong: Incorrect. Server-level failed login events are not captured here.

Failed login attempts to the database, including client IP and application name.

Why wrong: Incorrect. Failed login attempts do not create sessions, so they are not represented in these DMVs.

Active Directory Federation Services (AD FS) token

Why wrong: Incorrect. AD FS is a federation service; it does not directly issue tokens to Azure SQL Database. Entra ID issues tokens after authentication, and AD FS is one of many possible identity providers, but it is not a direct authentication method for Azure SQL Database.

Microsoft Entra ID certificate-based authentication

Why wrong: Incorrect. Certificate-based authentication is a method to authenticate to Entra ID, but it is not directly used for Azure SQL Database logins. A client application must first obtain an Entra ID token using the certificate, then present it to Azure SQL Database.

Microsoft Entra ID password authentication

Why wrong: Incorrect. Password authentication is a method to authenticate to Entra ID, but Azure SQL Database does not accept passwords directly. A user must first obtain an Entra ID token via a client application using password authentication, then present the token.

Set up a site-to-site VPN between the VM's VNet and the SQL Database's VNet.

Why wrong: A site-to-site VPN establishes secure connectivity between two virtual networks or an on-premises network and an Azure VNet. This option fails because Azure SQL Database is a platform-as-a-service (PaaS) offering, not a VNet itself, making direct site-to-site VPN peering to it technically incorrect and overly complex for this scenario. It is tempting as VPNs provide secure cross-network communication, and would be the correct choice if the requirement was to connect two separate Azure VNets (e.g., in different subscriptions) or an on-premises network to an Azure VNet.

Use a VNet service endpoint for Azure SQL Database in the VM's VNet.

Why wrong: VNet service endpoints for Azure SQL Database route traffic from a chosen subnet to the database over the Microsoft backbone, but the database's logical server still resolves to its public endpoint, which lacks a private IP address. Service endpoints also operate at the subnet level and are commonly scoped to a single subscription, requiring the VM's VNet to be in the same subscription as the SQL Database to apply. Because they do not assign a private IP to the database inside the VM's VNet, they fail to meet the requirement for private, cross-subscription access.

Configure a firewall rule to allow the VM's public IP address.

Why wrong: Configuring an Azure SQL firewall rule to allow the VM's public IP address leaves the database's public endpoint enabled and accessible from the internet, creating an unnecessary attack surface. The VM's public IP must be static and rechecked whenever the VM or its Public IP configuration changes, since Azure public IPs can be lost if the resource is deallocated. This approach does not provide private connectivity; traffic crosses the internet and is protected only by the firewall rule, which is a far weaker security posture than a private endpoint.

SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP

Why wrong: Incorrect. This group logs successful authentication attempts, not failures. The requirement is only for failures.

DATABASE_OBJECT_PERMISSION_CHANGE_GROUP

Why wrong: Logs permission changes, not authentication.

DATABASE_OBJECT_CHANGE_GROUP

Why wrong: Logs DML changes.

SCHEMA_OBJECT_CHANGE_GROUP

Why wrong: Logs DDL changes.

Create a SQL Server Agent job on the managed instance that captures DDL changes and failed logins into a table. Grant DBAs access to the table.

Why wrong: It stores audit data in a table and grants DBAs access, violating the requirement that DBAs should not be able to view or modify audit logs.

Enable database-level auditing on each database with audit action groups SCHEMA_OBJECT_CHANGE_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP. Configure the audit log destination to be a central Azure Storage account. Set retention to 180 days. Grant the DBAs 'Storage Blob Data Reader' role on the storage account but not 'Contributor' or 'Owner'.

Why wrong: Granting the Storage Blob Data Reader role to DBAs allows them to view the audit logs, which violates the requirement that DBAs should not be able to view or modify the logs.

Use Azure SQL Auditing with Azure Monitor Log Analytics workspace. Configure the workspace with 180-day retention. Grant DBAs 'Log Analytics Reader' role.

Why wrong: Granting the Log Analytics Reader role allows DBAs to view audit logs in the Log Analytics workspace, violating the requirement that DBAs should not be able to view or modify the logs.

Grant SELECT permission on the sensitive columns only to the compliance officers and deny SELECT to all other users

Why wrong: This would cause errors for other users when querying the table, rather than masking the data. It also requires application changes to handle the error.

Use Always Encrypted to encrypt the sensitive columns and configure the column master key to allow access only to compliance officers

Why wrong: Always Encrypted encrypts data at the column level but requires application changes to handle encryption keys and is transparent to the database.

Implement row-level security (RLS) to filter rows based on user's UPN

Why wrong: RLS filters rows, not columns. It cannot mask individual columns.

Add a firewall rule allowing all Azure IP addresses.

Why wrong: Allowing all Azure IPs is overly broad and insecure.

Add a firewall rule for each VM's public IP address.

Why wrong: Public IPs may change and are less secure than VNet integration.

Add a firewall rule allowing all Azure services to access the database.

Why wrong: Allowing all Azure services is too permissive and not secure.

Advanced Threat Protection

Why wrong: ATP detects threats but does not audit all logins.

Transparent Data Encryption (TDE)

Why wrong: TDE encrypts data at rest, not audit events.

SQL Vulnerability Assessment

Why wrong: Vulnerability assessment scans for security issues, not logins.

The service principal used for AKV access has expired.

Why wrong: Permission issues would cause access denied errors, not encryption errors.

The new key is in a different Azure region than the database.

Why wrong: AKV keys can be in any region; region mismatch is not an issue.

The database is using service-managed TDE and cannot switch to customer-managed keys.

Why wrong: The error occurred after rotation, not during initial setup.

Certificate-based authentication

Why wrong: Certificate-based authentication is not natively supported for Azure SQL Database.

SQL authentication with strong passwords

Why wrong: SQL authentication does not support MFA.

Windows authentication

Why wrong: Windows authentication is not supported for Azure SQL Database.

Enable Transparent Data Encryption (TDE).

Why wrong: TDE protects data at rest, not against injection.

Run SQL Vulnerability Assessment weekly.

Why wrong: Vulnerability assessment identifies weaknesses but does not block attacks.

Enable Azure SQL Auditing to log all queries.

Why wrong: Auditing logs events but does not prevent attacks.

The server firewall is blocking the connection.

Why wrong: Firewall rules are IP-based; if the developer can reach the server, the issue is likely authentication.

The developer's IP address is not allowed.

Why wrong: Same as above; firewall would block any connection.

Multi-factor authentication is required but not configured.

Why wrong: Active Directory Password authentication does not require MFA.

Configure firewall rules to allow only specific IP addresses.

Why wrong: Firewall rules control access, not encryption.

Enable Transparent Data Encryption (TDE).

Why wrong: TDE encrypts data at rest, not in transit.

Enable SQL Auditing.

Why wrong: Auditing logs activity, not encrypts connections.