Wednesday 22 February 2023

Connect and upload in azure blob with azure key vault using x++

 Today, I will be sharing details about file upload in azure blob using azure vault that includes pre requisite and code snippet used to it.

Before proceeding with code we need following key information which will be added in the key vault parameters in D365 F&O.

  1. Azure application id
  2. Azure application client secret
  3. Key vault URL
  4. Key vault Secret (In our case we need three : Container name, storage account, storage key)
  5. Container/Blob must be created in the azure

The above mentioned information can be added and fetched from azure portal. After getting those details  you need to create a new record in azure vault parameters form.

Go to System administration > Setup > Key vault parameters

Create a new record in key vault then create three new records for secrets.




Below is the code snippet used to get storage account, storage key and container name via Azure vault and upload files in the blob.

Note: I have created csv file using CommaStreamIo and getting the stream from it in such a way
System.IO.Stream stream = commaStreamIo.getStream();
Also, add following namespace at the top of the class :
using Microsoft.Azure;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Blob;
using Microsoft.WindowsAzure.Storage.Blob.CloudBlobContainer;
using Microsoft.WindowsAzure.Storage.Blob.CloudBlockBlob;
using Microsoft.WindowsAzure.Storage.File

Code snippet : 
 private void uploadFileInAzureBlob(System.IO.Stream _stream, str _fileName)
    {
      
        KeyVaultCertificateTable    certTableForContainer;
        KeyVaultCertificateTable    certTableForStorageAccount;
        KeyVaultCertificateTable    certTableForStorageAccountKey;
        str                         containerName;
        str                         storageAccountName;
        str                         storageAccountKey;
        
        certTableForContainer         = KeyVaultCertificateTable::findByName("ContainerName");
        certTableForStorageAccount    = KeyVaultCertificateTable::findByName("StorageAccountName");
        certTableForStorageAccountKey = KeyVaultCertificateTable::findByName("StorageAccountKey");

        try
        {
            if (certTableForStorageAccount.RecId != 0 && certTableForStorageAccountKey.RecId != 0 &&  certTableForContainer.RecId != 0)
            {
                containerName        = KeyVaultCertificateHelper::getManualSecretValue(certTableForContainer.RecId);
                storageAccountName   = KeyVaultCertificateHelper::getManualSecretValue(certTableForStorageAccount.RecId);
                storageAccountkey    = KeyVaultCertificateHelper::getManualSecretValue(certTableForStorageAccountKey.RecId);

                var storageCredentials = new Microsoft.WindowsAzure.Storage.Auth.StorageCredentials(StorageAccountName, storageAccountkey);
                CloudStorageAccount storageAccount = new Microsoft.WindowsAzure.Storage.CloudStorageAccount(storageCredentials, true);

                if(storageAccount)
                {
                    CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
                    CloudBlobContainer blobContainer = blobClient.GetContainerReference(containerName);
                    changecompany(curExt())
                    {
                        CloudBlockBlob blockblob =    blobContainer.GetBlockBlobReference(_fileName);
                        if(blockblob && !blockblob.Exists(null, null))
                        {
                            if(_stream)
                            {
                                _stream.Position = 0;
                                System.IO.StreamReader reader = new System.IO.StreamReader(_stream);
                                str csvFileContent = reader.ReadToEnd();
            
                                blockblob.UploadText(csvFileContent,null,null,null,null);
                                blockBlob.FetchAttributes(null,null,null);
                                BlobProperties BlobProperties = blockblob.Properties;

                                if(BlobProperties.Length == _stream.Length)
                                {
                                    info(strFmt("File %1 uploaded successfully", _fileName));
                                }
                            }
                        }
                        else
                        {
                            error(strFmt("File %1 Already Exists",_fileName));
                        }
                    }
                }
                else
                {
                    error("Unable ToConnect With Storage Account");
                }
            }
            else
            {
                info("Storage Account Or Key Record Missing");
            
            }

           
        }
        catch(Exception::Error)
        {
            error("Operation Cannot Be Completed");
        }

}

Monday 20 September 2021

Part 4 - Connect Azure BLOB storage with Dynamics 365 for Finance and Operations - (Place/Store/Upload the file in the blob folder using X++)

Today, I will share another part of connecting azure blob storage with Dynamics 365 for finance and operations series.

This part will be about placing or uploading the file in  the blob folder using X++ in Dynamics D365 Finance and Operations. Basically it is covering the write operations to the blob storage.

Perquisite:

  1. Storage account
  2. Blob container
  3. Create 2 folders in the blob container












Create a new class in the Dynamics 365 finance and operations and add following methods in it.
  • connectToAzureBlob
  • uploadFileToAzureBlob
  • moveTheFileBetweenFolders
  • deleteTheFileFromFolder
And we will be using following .NET libraries.
  • using Microsoft.WindowsAzure.Storage;
  • using Micorosft.WindowsAzure.Storage.Blob;
  • using System.IO;
  • using System.Text;
  • using System.Text.ASCIIEncoding;
























Output:

1) Uploaded or creating new file in azure blob





2) Moved file to another folder






3) Deleted the file from folder


Part 3 - Connect Azure BLOB storage with Dynamics 365 for Finance and Operations - (Consume or Read file details or data from the blob folder using X++)

Today, I will share another part of connecting azure blob storage with Dynamics 365 for finance and operations series.

This part will be about Consuming or Reading file details or data from the blob folder using X++ in Dynamics D365 Finance and Operations.

Perquisite:

  1. Storage account
  2. Blob container
  3. Create folder in the blob container
  4. Add a file to the blob container for testing (Using the Upload option in the Azure portal)

Sample text file










Create a new class in the Dynamics 365 finance and operations and add following methods in it.
  • connectToAzureBlob
  • GetFileNameList
  • readFileValueFromMemoryStream
And we will be using following .NET libraries.
  • using Microsoft.WindowsAzure.Storage;
  • using Micorosft.WindowsAzure.Storage.Blob;
  • using System.IO;
Sample code for the classes:
















Output :  




Part 2 - Connect Azure BLOB storage with Dynamics 365 for Finance and Operations - (Establish connection between Azure BLOB and D365 F&O)

Today, I will share another part of connecting azure blob storage with Dynamics 365 for finance and operations series.

This part will be about establishing the connection between azure blob and D365 F&O.

Perquisite:

  1. Storage account
  2. Blob container
1- Storage account

The application will require to access the connection string at runtime in order to authorize requests made to Azure Storage. We will use the connection string in Dynamics 365 Finance and Operations environment variable. (That variable can be part of parameters on the form)

The format of the connection string is :

DefaultEndpointsProtocol = [http|https];AccountName=myAccountName;AccountKey=myAccountKey

  • HTTP or HTTPS (Whether you want to connect to either of one protocol)
  • myAccountName : Replace it with your storage account
  • myAccountKey : Replace with your account access key
You can find out the connection key value on this path in the Azure portal.
- Go to Storage account - Access keys 












2- Blob Account


- Go to Storage account > Storage Explorer (preview) > BLOB CONTAINER




3- Store the BLOB file path in the Dynamics 365 Finance and Operations

Blob container has a folder type structure and files will be accessed from certain path.
We will store the file path and store it in the Dynamics 365 F&O variable to access the file at the particular location. With the help of file path we can iterate the blob container to particular folder and access the files.

Steps to create folder in blob container.
  • Go to Storage account > Storage Explorer (preview) > BLOB CONTAINER
  • Click New Folder and enter the name
  • Click OK









4- Create a new class and a code in it.(It will establish connection between Azure blob and D365 F&O)

We will use two .NET libraries to use Azure blob.
  • using Microsoft.WindowsAzure.Storage;
  • using Micorosft.WindowsAzure.Storage.Blob;








Output:



Part 1 - Connect Azure BLOB storage with Dynamics 365 for Finance and Operations - (Create Storage account and container in it)

Today, I will share the list of blogs which give the understanding of how to connect azure blob storage with Dynamics 365 for finance and operations and how to perform different operations on it.

Consider one of the common scenario where to need to exchange files between different applications such file can be created by D365 for finance and operations and third party app. As we all know that D365 does not provide accessibility to local path for Dynamics Production URL so we need to rely on another source.

The best and convenient way will be to handle situation using Microsoft Azure BLOB storage as central repository to exchange files between external system.

Part 1 - Create Azure BLOB and establish connection with Dynamics 365 for finance and operations

Perquisite:

  • Need Azure subscription to access Azure storage and can be used by creating a free account here .
    • Create a free account on Azure portal.
    • Sign into the Azure portal.
1) Go to Storage account by click home icon and click on it to open storage accounts page.





















2) Create Create button.

3) Enter the information for storage account and click Review+Create button.

4) Click Create (once validation is passed) as it will deploy the storage account.





















5) Go back to the storage accounts menu and check the newly created storage accounts. 

6) Click the Storage account and go the access keys. Click the show keys to view the details of keys.
Note : Key 1 and Key 2 are basically the connections strings which will be used to access this storage account.
7) Go to the container and click the Container icon to create new one.
8) Enter the name and click Create.
 
Output : Newly created container.










Tuesday 14 September 2021

View the Extensible Data Security Policy by using SQL query

 Today, I will share out the query which can used to view the Extensible data security policy in order to get insight what is happening on the backend  whether policy is created correctly or not.

This query will list down the details of all policies available in the system.

Note : We will use AxDB for it in the SQL

SELECT PrimaryTableAotName, QueryObjectAOTName,

ConstrainedTable, ModeledQueryDebugInfo, ContextType,ContextString,IsModeled

FROM [ModelSecPolRuntimeEx]

Output:





SQL Query to get security details (such as Security roles, duties and privileges)

 Today, I will sharing out the list of queries which can used to get the security details.

It will cover following combinations of data.

  • Security roles
  • Security roles to duties
  • Security roles with privileges
  • Security role and duty combination along with privileges
Following list of tables are used in it.
  • SECURITYROLES
  • SECURITYOBJECTCHILDREREFERENCES
  • SECURITYDUTY
  • SECURITYPRIVILEGE

-- Get the list  of all security roles 
Select Name as SecurityRoleName FROM SecurityRole;

-- Get the list of all security roles to duties
SELECT T2.Name as SecurityRole, T3.NAME as Duty 
FROM SECURITYOBJECTCHILDREREFERENCES T1 
JOIN SECURITYROLE T2 ON T1.IDENTIFIER = T2.AOTNAME 
JOIN SECURITYDUTY T3 ON T1.CHILDIDENTIFIER = T3.IDENTIFIER
WHERE T1.OBJECTTYPE = 0 AND T1.CHILDOBJECTTYPE = 1 ;

-- Get the ;ist of all security roles with privileges 
SELECT T2.Name as SecurityRole, T3.NAME as Privileges
FROM SECURITYOBJECTCHILDREREFERENCES T1 
JOIN SECURITYROLE T2 ON T1.IDENTIFIER = T2.AOTNAME 
JOIN SECURITYPRIVILEGE T3 ON T1.CHILDIDENTIFIER = T3.IDENTIFIER
WHERE T1.OBJECTTYPE = 0 AND T1.CHILDOBJECTTYPE = 2 ;


-- Get the list of all role-duty combination with privilege 
SELECT T2.Name as SecurityRole, T2.AOTNAME as RoleSystemName,  T3.NAME AS Duty, T3.IDENTIFIER as DutySystemName, T5.NAME as Privilege, T5.IDENTIFIER as PrivilegeSystemName
FROM SECURITYOBJECTCHILDREREFERENCES T1 
JOIN SECURITYROLE T2 ON T1.IDENTIFIER = T2.AOTNAME 
JOIN SECURITYDUTY T3 ON T1.CHILDIDENTIFIER = T3.IDENTIFIER
JOIN SECURITYOBJECTCHILDREREFERENCES T4 on T4.IDENTIFIER = T3.IDENTIFIER
JOIN SECURITYPRIVILEGE T5 on T4.CHILDIDENTIFIER = T5.IDENTIFIER
WHERE T1.OBJECTTYPE = 0 AND T1.CHILDOBJECTTYPE = 1 
AND T4.OBJECTTYPE = 1 AND T4.CHILDOBJECTTYPE = 2;

Connect and upload in azure blob with azure key vault using x++

 Today, I will be sharing details about file upload in azure blob using azure vault that includes pre requisite and code snippet used to it....