How to Obtain the KeyVaultResourceId for Azure Key Vault: A Comprehensive Guide

Azure Key Vault is an essential service for managing secrets, keys, and certificates. One critical element when working with Azure Key Vault is the KeyVaultResourceId, which uniquely identifies your Key Vault within your Azure subscription. This blog will guide you through the different methods to obtain the KeyVaultResourceId, including Azure CLI, Azure PowerShell, and the Azure Portal.

Understanding the KeyVaultResourceId

The KeyVaultResourceId is a unique identifier for your Azure Key Vault resource. It is used in various Azure operations and configurations to reference the Key Vault. The KeyVaultResourceId is structured as follows:

/subscriptions/{subscription-id}/resourceGroups/{resource-group}/providers/Microsoft.KeyVault/vaults/{key-vault-name}

Methods to Obtain KeyVaultResourceId

Using Azure CLI

The Azure Command-Line Interface (CLI) is a powerful tool for managing Azure resources. Follow these steps to retrieve the KeyVaultResourceId using Azure CLI:

Open Terminal or Command PromptLaunch your preferred terminal or command prompt.

Run the Command to List Key VaultsUse the following command to list all Key Vaults in your subscription:

    az keyvault list --output table

    This command displays a table with details about your Key Vaults, including their id field, which represents the KeyVaultResourceId.

    Locate the Key Vault and Note the id

    Find your specific Key Vault in the output. The id field is your KeyVaultResourceId.

    Example output:

    Name              Location    ResourceGroup    Id
    ----------------  ----------  ---------------  --------------------------------------
    my-keyvault       westus      my-resource-group  /subscriptions/<subscription-id>/resourceGroups/my-resource-group/providers/Microsoft.KeyVault/vaults/my-keyvault

    In this example, the KeyVaultResourceId is:

    /subscriptions/<subscription-id>/resourceGroups/my-resource-group/providers/Microsoft.KeyVault/vaults/my-keyvault

    Using Azure PowerShell

    Azure PowerShell is another robust option for managing Azure resources. Here’s how to get the KeyVaultResourceId using Azure PowerShell:

    Open Azure PowerShellLaunch Azure PowerShell on your system.

    Run the Command to Retrieve Key VaultsUse the following command to get the details of all Key Vaults:

      Get-AzKeyVault | Select-Object ResourceId, Name, ResourceGroupName

      Find Your Key Vault and Note the ResourceId

      Locate your Key Vault in the output. The ResourceId field is your KeyVaultResourceId.

      Example output:

      ResourceId                                                                                   Name         ResourceGroupName
      --------------------------------------------------------------------------------------------  ------------  -------------------
      /subscriptions/<subscription-id>/resourceGroups/my-resource-group/providers/Microsoft.KeyVault/vaults/my-keyvault  my-keyvault  my-resource-group

      Here, the KeyVaultResourceId is:

      /subscriptions/<subscription-id>/resourceGroups/my-resource-group/providers/Microsoft.KeyVault/vaults/my-keyvault

      Using Azure Portal

      The Azure Portal provides a user-friendly interface to access resource details. To find the KeyVaultResourceId in the portal:

      Go to the Azure PortalOpen your web browser and navigate to the Azure Portal.

      Navigate to Your Key VaultIn the Azure Portal, go to the “Key Vaults” section and select your Key Vault from the list.

      Find the Resource IDIn the Key Vault’s overview pane, locate the Resource ID. This is typically found in the properties section or URL of the Key Vault.

      Copy the Resource IDCopy the Resource ID, which is your KeyVaultResourceId.Example Resource ID:

        /subscriptions/<subscription-id>/resourceGroups/my-resource-group/providers/Microsoft.KeyVault/vaults/my-keyvault

        Conclusion

        Obtaining the KeyVaultResourceId is a crucial step for managing and configuring your Azure Key Vault resources. Whether using Azure CLI, Azure PowerShell, or the Azure Portal, these methods provide straightforward ways to access your Key Vault’s unique identifier.

        By understanding and utilizing the KeyVaultResourceId, you can efficiently manage your Key Vault settings, perform operations, and integrate with other Azure services.

        For more tutorials and tips on SQL Server, including performance tuning and database management, be sure to check out our JBSWiki YouTube channel.

        Thank You,
        Vivek Janakiraman

        Disclaimer:
        The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

        Disabling Deprecated feature sp_configure Remote access

        -> We had an audit on the database servers. An auditor advise us that “Remote access” feature should be disabled due to below reasons,

        [$] It is an obscure SQL Server to SQL Server communication feature that is deprecated feature. Please refer article for more details.

        -> It was decided that we should disable it on our SQL Server instances. We disabled it using below code,

        EXEC sp_configure 'remote access', 0 ;  
        GO  
        RECONFIGURE ;  
        GO

        OR

        -> Right Click SQL Server Instance -> Properties -> Connections -> Uncheck “Allow remote connections to this server”,

        -> Please note that a restart of SQL Services is required for the changes to take effect.

        -> We faced below issues after disabling sp_configure ‘remote access’ on our SQL Server instances.

        Log Shipping

        -> Logshipping main functionality as a whole such as backup, copy and restore works fine without issues. But the reporting part utilizing SQL Server Management Studio “Transaction Log Shipping Status” report displays incorrect information.

        -> LSAlert job on Monitor servers fails as it is not able to get upto date information from Primary and Standby Instances.

        -> The issues related to SSMS “Transaction Log Shipping Status” report and LSAlert job seems to be due to the fact that it is not able to get proper data from Primary and Standby server as the linked server fails with below error,

        Could not execute procedure on remote server ‘LOGSHIPLINK_JBSAG4_200666876’ because SQL Server is not configured for remote access. Ask your system administrator to reconfigure SQL Server to allow remote access.

        -> ‘LOGSHIPLINK_JBSAG4_200666876’ is a System Linked server that is created as part of Logshipping. I dropped this linked server and recreated using sp_add_linkedserver and was able to see same behaviour. I tried this step on a test server.

        -> I checked the trace further and understood that the linked server errors out when trying to run below command remotely,

        select @linkcmd = quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N’.msdb.sys.sp_processlogshippingmonitorhistory’
        exec @retcode = @linkcmd
                            @mode = @mode
                            ,@agent_id = @agent_id
                            ,@agent_type = @agent_type
                            ,@session_id = @session_id
                            ,@session_status = @session_status
                            ,@monitor_server = @monitor_server
                            ,@monitor_server_security_mode = 1
                            ,@database = @database
                            ,@log_time = @log_time
                            ,@log_time_utc = @log_time_utc
                            ,@message = @message

        -> This is when I realized that the linked server utilizes RPC Out that is “Remote Procedure call” to execute above function remotely on Primary and DR to fetch the details. Linked servers with “RPC out” true will require Remote access to be turned on and this seems to be the root cause of our issue.

        -> Linked Servers with RPC out set to false doesn’t have any issues and will function normally. But in our case, SSMS “Transaction Log Shipping Status” report and LSAlert job needs “RPC Out” set to true on Linked Servers to get the data remotely and display it as appropriate.

        Linked Servers

        -> The lab utilizes 2 database servers JBSAG1 and JBSAG2. Remote access option is disabled on both servers. A linked server for JBSAG2 is created on SQL Server Instance JBSAG1,

        Remote Access

        Linked server property

        -> Querying a remote object Table1 using Linked server. It works fine,

        -> Querying a remote View vw_table1 using Linked server. It works fine,

        -> Querying a remote Stored procedure sp_table1 using Linked server. It returns an error that remote access option is required,

        -> As a workaround, I will utilize “Execute AT” to execute remote Stored procedure sp_table1 using Linked server. It works,

        Always On

        I have setup a lab with 3 Always on replicas (2 in East US and 1 in Korea Central), testing was performed on the lab with Remote Access turned off,

        [$] Create an Availability group.
        [$] Drop an Availability group.
        [$] Suspend and resume availability databases.
        [$] Load the availability database and create issues such as HADR_SYNC_COMMIT, Redo latency, high log send queue etc.
        [$] Check Always On dashboard several times.
        [$] Failover and failback manually.
        [$] Failover and failback automatically.

        I did not see any issues on above scenario with remote access turned off. All above tests with remote access off were exhibiting same behavior as remote access on.

        Summary

        Discuss above points with your team and decide if things that will break as mentioned in this blog is required for your team and decide further.

        In my case, many of our database servers has Logshipping and Linked servers configured. We had issues atleast on Logshipping as we make use of LSAlert and SSMS “Transaction Log Shipping Status” report day in and out. Hence, we provided this justification and left remote access on.

        If there are any other features that make use of Remote Access which I have missed in my post. Please advise those in comment section.

        Thank You,
        Vivek Janakiraman

        Disclaimer:
        The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

        Always ON – Max DOP for Secondary

        Environment

        -> JBSUB-PRIMARY and JBSUB-DR are Azure IaaS virtual machines provisioned on East US region.

        -> Azure virtual machine JBSUB-PRIMARY and JBSUB-DR are part of Windows cluster JBSWikiClust without shared storage. Azure virtual machine JBSUB-PRIMARY and JBSUB-DR will be configured using Synchronous commit with automatic failover.

        -> We had a requirement wherein MAXDOP value should be set to 1 in primary replica and MAXDOP value should be 0 on secondary replica.

        -> The problem arises on above requirement when a failover is performed and the MAXDOP value for each of these 2 replicas are set at instance level. After the failover New primary will have MAXDOP as 0 and New Secondary with MAXDOP1 and this causes performance issues in our application.

        -> Database option “Max DOP for Secondary” came to rescue, where in we were able to specify the MAXDOP value for Primary and secondary replica as below,

        Primary Replica JBSUB-PRIMARY

        select @@servername
        go
        sp_configure 'max degree of parallelism'
        

        -> Then execute below user query,

        select top 100000 * from [dbo].[Table3] a INNER JOIN Table2 b ON a.sno=a.sno 
        order by b.sname

        Secondary Replica JBSUB-DR using Read-Routing List

        select @@servername
        go
        sp_configure 'max degree of parallelism'

        -> Then execute below user query,

        select top 100000 * from [dbo].[Table3] a INNER JOIN Table2 b ON a.sno=a.sno 
        order by b.sname

        Secondary Replica JBSUB-DR directly using SQL Server Instance

        select @@servername
        go
        sp_configure 'max degree of parallelism'

        -> Then execute below user query,

        select top 100000 * from [dbo].[Table3] a INNER JOIN Table2 b ON a.sno=a.sno 
        order by b.sname

        Hope this help!!!

        Thank You,
        Vivek Janakiraman

        Disclaimer:
        The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.