Shahzad Khan – Page 102 – My Point of View

CS2001 Missing AssemblyAttributes.cs when executing SSIS package deployed to the server

If you get this error;

The reson is permissions;

Navigate to c:\windows\temp. On the temp folder, right click properties, security and advanced. Add your proxy account here and give it full permissions;

Resource

https://stackoverflow.com/questions/35347632/cs2001-missing-assemblyattributes-cs-when-executing-ssis-package-deployed-to-the

enabling / disabling xp_cmdshell

I am not getting into the discussion of pros and cons of xp_cmdshell. This command helps you to run broad level features inside your SQL server. You can run OS level commands, SSIS packages etc.

Here is how you can enable, disable and use.

To check, if xp_cmdshell is enabled and available to use;

SELECT CONVERT(INT, ISNULL(value, value_in_use)) AS config_value
FROM sys.configurations
WHERE name = 'xp_cmdshell';

Turn on xp_cmdshell

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE

Turn off xp_cmdshell

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE

Here is a simple use case;

EXEC xp_cmdshell 'dir c:\'

Here is how you can enable it, use it and disable;

DECLARE @originalSetting INTEGER = 0;
SELECT @originalSetting = CONVERT(INT, ISNULL(value, value_in_use))
FROM  sys.configurations
WHERE  name = 'xp_cmdshell' ;
 
IF @originalSetting = 0
BEGIN
    EXEC sp_configure 'show advanced options', 1;
    RECONFIGURE;
    EXEC sp_configure 'xp_cmdshell', 1;
    RECONFIGURE;
END
 
EXEC xp_cmdshell 'dir c:\';
 
IF @originalSetting = 0
BEGIN
    EXEC sp_configure 'show advanced options', 1;
    RECONFIGURE;
    EXEC sp_configure 'xp_cmdshell', 0;
    RECONFIGURE;
END

You can create a stored procedure and use;

CREATE PROCEDURE sp_SuperCmdShell
(
    @commandToRun VARCHAR(1000)
)
AS
BEGIN
    -- check to see if xp_cmdshell is enabled, if its not
    --   then enable it and run the command then set it back
    --   to not enabled.
    DECLARE @originalSetting INTEGER = 0;
    SELECT @originalSetting = CONVERT(INT, ISNULL(value, value_in_use))
    FROM  sys.configurations
    WHERE  name = 'xp_cmdshell' ;
 
    IF @originalSetting = 0
    BEGIN
        EXEC sp_configure 'show advanced options', 1;
        RECONFIGURE;
        EXEC sp_configure 'xp_cmdshell', 1;
        RECONFIGURE;
    END
 
    EXEC xp_cmdshell @commandToRun;
 
    IF @originalSetting = 0
    BEGIN
        EXEC sp_configure 'show advanced options', 1;
        RECONFIGURE;
        EXEC sp_configure 'xp_cmdshell', 0;
        RECONFIGURE;
    END
 
END
GO
exec #sp_SuperCmdShell 'dir c:\';

This is not a new feature. It came out with early release of SQL server. If you see the usage command, they are OS level command. With the super privileged access any body can run a “format c:\” OS command and leave your database server in unrecoverable state. My suggestion would be to use it with least privileged proxy account.

Multiple environments, do I need Azure Key Vault for each environment?

I started getting this problem today when I try to use same secret for my second application in a single key vault;

Multiple resources/entities can access a single Key Vault instance – provided they’re all in the same location (data center).

You may choose to segment your keys, secrets and certificates, either by placing them in different Key Vaults or by using different access methods/identities, however that’s not necessary.

The only time you need a separate Key Vault instance is when the resources/entities accessing it are in another location (data center/region).

It’s worth noting that you don’t need to worry too much about provisioning Disaster Recovery for resources using Key Vault, as the SLA Microsoft provide is unsurprisingly good: https://docs.microsoft.com/en-gb/azure/key-vault/key-vault-disaster-recovery-guidance. One caveat to that would be if you’re running IaaS/PaaS instances and want to run a DR fail-over yourself to another data center, at which point you’d need to manually migrate the keys/secrets/certificates in your main Key Vault into another instance (and re-point your VMs accordingly)

Resources

https://docs.microsoft.com/en-us/answers/questions/77182/key-vault-for-multiple-app-service-should-i-create.html

https://docs.microsoft.com/en-us/azure/key-vault/general/best-practices

Restrict Web App Access to selected IPV4 addresses

To secure your web app make sure you have setup HTTPS only and client certificate required.

To further restrict access, you can setup IP address-based rule.

To add an access restriction rule to your app, select Networking under settings and click on Configure Access Restrictions. On the Access Restrictions pane, select Add rule. After you add a rule, it becomes effective immediately.

Rules are enforced in priority order, starting from the lowest number in the Priority column. An implicit deny all is in effect after you add even a single rule.

On the Add Access Restriction pane, when you create a rule, do the following:

Under Action, select either Allow or Deny.

Optionally, enter a name and description of the rule.
In the Priority box, enter a priority value.
In the Type drop-down list, select the type of rule.

The different types of rules are described in the following sections.

Note

There is a limit of 512 access restriction rules. If you require more than 512 access restriction rules, we suggest that you consider installing a standalone security product, such as Azure Front Door, Azure App Gateway, or an alternative WAF.

Set an IP address-based rule

Follow the procedure as outlined in the preceding section, but with the following addition:

For step 4, in the Type drop-down list, select IPv4 or IPv6.

Specify the IP Address Block in Classless Inter-Domain Routing (CIDR) notation for both the IPv4 and IPv6 addresses. To specify an address, you can use something like 1.2.3.4/32, where the first four octets represent your IP address and /32 is the mask. The IPv4 CIDR notation for all addresses is 0.0.0.0/0. To learn more about CIDR notation, see Classless Inter-Domain Routing.

Azure DevOps required to deploy build to App Services and we need to allow these services for this use case. Microsoft has introduced an AzureDevOps service tag for it but as of this writing the tag is not working. The work around is to open app to selected geography where Azure DevOps is running. In my case, they are running in EASTUS. I am going to add this rule to allow Azure DevOps to works with Azure App Service;

This will open up App to the whole EastUS region but still is better than opening it up to the whole world.

As of this writing Azure DevOps Service tag is not supported for hosted agents. AzureDevOps tag does not cover Microsoft Hosted Agents IP range, which makes the use case for using it in Azure App Service limited. The only relevant use case is, if a custom web hook is hosted on App Services.

Azure DevOps IP address and domain URLs can be used and Azure Virtual machine scale set agents to shrink the possible IP range. Refer to following links for further info;

DevOps Inbound connections: Allowed address lists and network connections – Azure DevOps | Microsoft Docs

Scale set agents: Azure virtual machine scale set agents – Azure Pipelines | Microsoft Docs

SET AN IP ADDRESS-BASED RULE USING POWERSHELL

Add-AzWebAppAccessRestrictionRule -ResourceGroupName “appservice-rg” -WebAppName “simse” -Name IpRule -Priority 200 -Action Allow -IpAddress 100.15.181.143/32

Resource

https://docs.microsoft.com/en-us/azure/app-service/app-service-ip-restrictions

Can I move an app resource from one region to another region?

I have created a resource group in East US2. I am going to move “app service plan” from “East US” to “East US2.

Great. The resource is moving.

The resource can be moved but its location (region) is retained.

This time I am going to move same resource from East US to East US2 (Region transfer).

If you click ok, you will see this message;

No use. One region resource cannot be moved to another region with Move option. What it means, that you have to create your resource group, app service plan, apps domain name, application insights in a separate region.

Follow this article from Microsoft to move your resources from one region to another.

https://docs.microsoft.com/en-us/azure/app-service/manage-move-across-regions