search

MicrosoftDocs / azure-docs

Open source documentation of Microsoft Azure
https://docs.microsoft.com/azure
Creative Commons Attribution 4.0 International
10.28k stars 21.46k forks source link

Policy to enable automatic SQL IaaS Extension Registration #108643

Closed jaypee1990 closed 6 months ago

jaypee1990 commented 1 year ago

How to enable a policy to check for new subscriptions which were created to be able to register them for automation SQL IaaS extension registration. This will help mitigate the problem for all the new subscriptions after the script has been run once and to make sure all new subscriptions have it without the need to run the script again and again.

Document Details

⚠ Do not edit this section. It is required for learn.microsoft.com ➟ GitHub issue linking.

Naveenommi-MSFT commented 1 year ago

@jaypee1990 Thanks for your feedback! We will investigate and update as appropriate.

SaibabaBalapur-MSFT commented 1 year ago

@jaypee1990 To enable a policy to check for new subscriptions which were created to be able to register them for automation SQL IaaS extension registration, you can use Azure Policy. Azure Policy is a service in Azure that you use to create, assign, and manage policies. These policies enforce different rules and effects over your resources, so those resources stay compliant with your corporate standards and service level agreements. You can create a policy that checks for the presence of the SQL IaaS extension and registers it if it is not present. You can then assign this policy to a management group or subscription to ensure that all new subscriptions have the extension without the need to run the script again and again.

Here are the high-level steps to create and assign a policy to check for the SQL IaaS extension and register it if it is not present:

Create a policy definition that checks for the presence of the SQL IaaS extension and registers it if it is not present. Assign the policy definition to a management group or subscription. Monitor the policy compliance to ensure that all new subscriptions have the extension.

You can find more information on how to create and assign policies in Azure Policy in the following document. https://github.com/MicrosoftDocs/azure-docs/blob/main/articles/active-directory/cloud-infrastructure-entitlement-management/onboard-azure.md

jaypee1990 commented 1 year ago

Hi Sai,

Thank you for your response.

I'm aware of the policy definition and assignment but is it possible to enable this via policy? https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/sql-agent-extension-automatic-registration-all-vms?view=azuresql&tabs=azure-cli What I understand is it registers the resource provider at subscription level as well as install SqlIaasExtension on all existing or future VMs.

If I create a policy to only look for missing extension and install if doesn’t exist, is it going to still check if finds the SQL server installation on the VM or will anyway just go ahead and install it on all VMs?

Thank you, Jyoti Prakash

From: Saibaba Balapur Hireka @.> Sent: Wednesday, April 26, 2023 8:42 AM To: MicrosoftDocs/azure-docs @.> Cc: Jyoti Prakash Nayak @.>; Mention @.> Subject: Re: [MicrosoftDocs/azure-docs] Policy to enable automatic SQL IaaS Extension Registration (Issue #108643)

@jaypee1990https://protect2.fireeye.com/v1/url?k=31323334-501d5122-313273af-454445555731-5bad38609bc3d56d&q=1&e=3f13bde5-2344-494e-a661-5bd1ed4be87b&u=https%3A%2F%2Fgithub.com%2Fjaypee1990 To enable a policy to check for new subscriptions which were created to be able to register them for automation SQL IaaS extension registration, you can use Azure Policy. Azure Policy is a service in Azure that you use to create, assign, and manage policies. These policies enforce different rules and effects over your resources, so those resources stay compliant with your corporate standards and service level agreements. You can create a policy that checks for the presence of the SQL IaaS extension and registers it if it is not present. You can then assign this policy to a management group or subscription to ensure that all new subscriptions have the extension without the need to run the script again and again.

Here are the high-level steps to create and assign a policy to check for the SQL IaaS extension and register it if it is not present:

Create a policy definition that checks for the presence of the SQL IaaS extension and registers it if it is not present. Assign the policy definition to a management group or subscription. Monitor the policy compliance to ensure that all new subscriptions have the extension.

You can find more information on how to create and assign policies in Azure Policy in the following document. https://github.com/MicrosoftDocs/azure-docs/blob/main/articles/active-directory/cloud-infrastructure-entitlement-management/onboard-azure.mdhttps://protect2.fireeye.com/v1/url?k=31323334-501d5122-313273af-454445555731-f13b7a361ce03217&q=1&e=3f13bde5-2344-494e-a661-5bd1ed4be87b&u=https%3A%2F%2Fgithub.com%2FMicrosoftDocs%2Fazure-docs%2Fblob%2Fmain%2Farticles%2Factive-directory%2Fcloud-infrastructure-entitlement-management%2Fonboard-azure.md

— Reply to this email directly, view it on GitHubhttps://protect2.fireeye.com/v1/url?k=31323334-501d5122-313273af-454445555731-5b761912a353e365&q=1&e=3f13bde5-2344-494e-a661-5bd1ed4be87b&u=https%3A%2F%2Fgithub.com%2FMicrosoftDocs%2Fazure-docs%2Fissues%2F108643%23issuecomment-1522869129, or unsubscribehttps://protect2.fireeye.com/v1/url?k=31323334-501d5122-313273af-454445555731-4faf63c7b279f929&q=1&e=3f13bde5-2344-494e-a661-5bd1ed4be87b&u=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAXSX7DH22ZJJTOYNNIJKKHTXDC7THANCNFSM6AAAAAAXJGLGII. You are receiving this because you were mentioned.Message ID: @.***>

SaibabaBalapur-MSFT commented 1 year ago

@adbadram Can you please check and add your comments on this doc update request as applicable.

jaypee1990 commented 1 year ago

Adding to the existing question,

When I apply policy to audit if the extension is installed, it will look for all VMs and report not compliant if it doesn’t find the extension. Which means it’s only looking at the extension and not to the SQL installation on the server.

If I use a DeployIfNotExist policy, it will try to install the extension and will fail since it will not find the SQL installation on the VM but the extension status will be failed.

When I will run the audit again, it will make everything compliant since all have the extension regardless of the status. I can of course filter to look for status but it won’t achieve what I’m trying to.

Sent from Outlook for iOShttps://aka.ms/o0ukef

From: Saibaba Balapur Hireka @.> Sent: Wednesday, May 3, 2023 11:56:30 AM To: MicrosoftDocs/azure-docs @.> Cc: Jyoti Prakash Nayak @.>; Mention @.> Subject: Re: [MicrosoftDocs/azure-docs] Policy to enable automatic SQL IaaS Extension Registration (Issue #108643)

@adbadramhttps://protect2.fireeye.com/v1/url?k=31323334-501d5122-313273af-454445555731-3baa917db16c9519&q=1&e=c1e5e874-9937-400f-a878-9fd939b5c2ca&u=https%3A%2F%2Fgithub.com%2Fadbadram Can you please check and add your comments on this doc update request as applicable.

— Reply to this email directly, view it on GitHubhttps://protect2.fireeye.com/v1/url?k=31323334-501d5122-313273af-454445555731-1a97b64d37d14e7b&q=1&e=c1e5e874-9937-400f-a878-9fd939b5c2ca&u=https%3A%2F%2Fgithub.com%2FMicrosoftDocs%2Fazure-docs%2Fissues%2F108643%23issuecomment-1532751386, or unsubscribehttps://protect2.fireeye.com/v1/url?k=31323334-501d5122-313273af-454445555731-d49a87362bfe9ee7&q=1&e=c1e5e874-9937-400f-a878-9fd939b5c2ca&u=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAXSX7DHBNVSAXWOFE3YUOU3XEITU5ANCNFSM6AAAAAAXJGLGII. You are receiving this because you were mentioned.Message ID: @.***>

jaypee1990 commented 1 year ago

Hi,

Is there any updates on this? I think we need to enable the feature BulkRegistration and Microsoft.SQLVirtualMachine resource provider to get this done instead of installing extension on VM.

From: Jyoti Prakash Nayak @.> Sent: Wednesday, May 3, 2023 12:22:54 PM To: MicrosoftDocs/azure-docs @.>; MicrosoftDocs/azure-docs @.> Cc: Mention @.> Subject: Re: [MicrosoftDocs/azure-docs] Policy to enable automatic SQL IaaS Extension Registration (Issue #108643)

Adding to the existing question,

When I apply policy to audit if the extension is installed, it will look for all VMs and report not compliant if it doesn’t find the extension. Which means it’s only looking at the extension and not to the SQL installation on the server.

If I use a DeployIfNotExist policy, it will try to install the extension and will fail since it will not find the SQL installation on the VM but the extension status will be failed.

When I will run the audit again, it will make everything compliant since all have the extension regardless of the status. I can of course filter to look for status but it won’t achieve what I’m trying to.

Sent from Outlook for iOShttps://aka.ms/o0ukef

From: Saibaba Balapur Hireka @.> Sent: Wednesday, May 3, 2023 11:56:30 AM To: MicrosoftDocs/azure-docs @.> Cc: Jyoti Prakash Nayak @.>; Mention @.> Subject: Re: [MicrosoftDocs/azure-docs] Policy to enable automatic SQL IaaS Extension Registration (Issue #108643)

@adbadramhttps://protect2.fireeye.com/v1/url?k=31323334-501d5122-313273af-454445555731-3baa917db16c9519&q=1&e=c1e5e874-9937-400f-a878-9fd939b5c2ca&u=https%3A%2F%2Fgithub.com%2Fadbadram Can you please check and add your comments on this doc update request as applicable.

— Reply to this email directly, view it on GitHubhttps://protect2.fireeye.com/v1/url?k=31323334-501d5122-313273af-454445555731-1a97b64d37d14e7b&q=1&e=c1e5e874-9937-400f-a878-9fd939b5c2ca&u=https%3A%2F%2Fgithub.com%2FMicrosoftDocs%2Fazure-docs%2Fissues%2F108643%23issuecomment-1532751386, or unsubscribehttps://protect2.fireeye.com/v1/url?k=31323334-501d5122-313273af-454445555731-d49a87362bfe9ee7&q=1&e=c1e5e874-9937-400f-a878-9fd939b5c2ca&u=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAXSX7DHBNVSAXWOFE3YUOU3XEITU5ANCNFSM6AAAAAAXJGLGII. You are receiving this because you were mentioned.Message ID: @.***>

MashaMSFT commented 6 months ago

Hi @jaypee1990 , Thank you for your submission! My sincere apologies for the delay it's taken to address your concern, we've been working through a very large backlog.

During this time, Git Issues have been deprecated as we've transitioned to a new feedback system. I've added your suggestion to our internal backlog (#250004) that we will continue addressing as priorities and resources allow.

We use your feedback to improve our documentation, so we are grateful for your input, and your time. I apologize for any inconvenience, and thank you for your patience and understanding.

Masha from the SQL Docs team

please-close