Nishant Rana's Weblog

Fixed – Column names in each table must be unique. Column name ‘x’ in table ‘y’ is specified more than once while importing solution in Dynamics 365

Recently while trying to import the solution we got the below error.

Below was the outcome of our analysis

The column or the field referred to in the error message was not available in the CRM application i.e. customizations – default solution was not having this attribute.
The column was only available in the base table of the entity.
The column was not available in the filtered view of that entity.
The column was not available in the MetadataSchema.Attribute table.
We had around 50 such columns in the base table of the entity.

In short, the field was only available in the base table and wasn’t available anywhere else, which could have been the result of the solution being deleted at some point in time, followed by the wrong restoration of the table (i.e. unsupported change to the database).

We raised the Microsoft Support for the same, and the way we resolved the issue was by dropping/deleting those columns from the base table, followed by running the solution import which went successful this time. (fortunately the fields were all blank – null or default value)

ALTER TABLE “table_name” DROP “column_name”;

We can also get this error if we have a field with same schema name as source field created in the target environment (created manually / directly there). In this case also either we can remove the field from the source environment’s solution file or we can delete the field from the target environment.

Hope it helps..

Pause and resume enhancements in SLA in Dynamics 365 – 2020 Release Wave 2

The way we currently configure Pause and Resume for an SLA is by setting the Allow Pause and Resume field to Allow.

And within the Service Tab of System Settings, we can define the status values on which the SLA calculation should get paused at the entity level.

So basically, the SLA will be paused for the case record with status as either On Hold and Waiting for Details as we have defined below.

As soon as we change the status to Researching, the timer resumes

With 2020 Release Wave 2, we can now configure Pause Criteria at the SLA Item level, which overrides the criteria defined at the SLA / Entity level as defined above.

Toggle Override Criteria field

We can define the criteria as shown below using the condition builder.

Here we have defined the condition for SLA to be paused in case of case type Request.

Save and activate the SLA.

Let us now create a new case to see it in action.

We can see the SLA timer running.

Update the case type to Request and save.

As expected, we can see the SLA paused

Check other blog posts on Release 2020 Wave 2

Hope it helps..

JWT – JSON Web Token – Introduction

As we know, HTTP is a stateless protocol where each request is treated as an independent request. For rendering static web page, this could still be fine, but what if the web application needs to track a user across multiple requests.That is where Session and state management came to the picture. The server will authenticate the user and if it’s a valid request, the server will save the session id and return the same to the client. The client can pass this session-id for any subsequent request. The server will check for the session id and will process the request for the client.

With server-side session management, scalability can be a challenge, say we have a load-balanced scenario, the user sends a session id in the request which goes to a different server which knows nothing about the session causing failure. Now we can always save the session id in the database which will bring its overhead.

This is where JWT – JSON Web Token comes to rescue that comply with the stateless nature of the HTTP.

JSON is an open standard RFC 7519, that defines a compact and self-contained method for securely transferring information between parties.

The format of JSON Web Token

header.payload.signature

payload is the part of transmitted data that is the actual intended message in computing.

The header will typically contain

typ – the type of media, JWT in this case.
alg – the algorithm used for signing and/or decryption the JWT

The payload contains information about the client or set of claims. There are seven registered (public) claims and we can define private (custom) claims also.

iss	issuer	The party that issued the JWT
sub	subject	The party that this JWT carries information
aud	audience	Intended recipient
exp	expiration	Exact moment from which the JWT is considered invalid in ‘seconds since Epoch’ format
nbf	from not before	Exact moment from which the JWT is considered valid.
Iat	Issued at time	Time when the JWT was issued
jti	JWT ID	Unique identifier for this JWT

The third part signature is computed as follows:

Header and Payload are encoded using Base64url encoding and are concatenated with a period separator.

This is then run through the algorithm specified in the header.

HS256(secret, base64URLEncoding(header) + “.” + base64URLEncoding(payload))

The signature is also encoded using Base64urlEncoding

Finally, the token will be

token= base64urlEncoding(header) + ‘.’ + base64urlEncoding(payload) + ‘.’ + base64urlEncoding(signature)

We can encode or decode JWTs at

https://www.jsonwebtoken.io

Here changing the Payload will change the JWT String.

The flow will look something like below

Get the free comprehensive guide on JWT

https://auth0.com/resources/ebooks/jwt-handbook/

Hope it helps..

PowerAutomate: Perform “Group By” operation on Data

Ajit Patra

In this post, we’ll see how we can perform aggregate function on data such as Sum or Count using PowerAutomate. Unlike LINQ in C# or SQL, it’s not very straight forward to do using PowerAutomate for which I thought of sharing the approach we had taken.

Recently, we got a requirement to calculate the sum of Quantity, after grouping the data on 5 columns NDISNumber, SupportDeliveredFrom, SupportNumber, ClaimType and CancellationReason in a custom entity Claim.

Below are the steps we followed to achieve the result:

**Action**: Initialize Variable(Variable)
**Purpose**: Hold the unique data of 5 columns based on which we need to group data.

**Action**: Initialize Variable(Variable)
**Purpose**: Resultant array to hold unique data along with sum of quantity

Initialize 5 variables to hold unique values of each group-by column inside Apply to each action we are going to use further. Please see…

View original post 564 more words

Always Encrypted feature in SQL Server

Always Encrypted is used for encryption at the column level rather than the entire database. It provides both data at rest as well in memory (in flight).

It is different from column (cell-level) and Transparent Data Encryption (TDE) which uses keys and certificates, which are stored in the database. In the case of Always Encrypted, keys are managed outside the database. SQL Server cannot decrypt on its own, as it is the client who encrypts / decrypts on the fly, separating those who own the data from the one who manages it.

To enable Always Encrypted we can use either T-SQL or the Always Encryption Wizard in SSMS. It supports both Randomized and Deterministic Encryption types.

Columns are encrypted using CEK – Column Encryption Key.
The encrypted versions of each CEK are stored in the database.
CMK – Column Master Key is used to encrypt all the CEKs.
Only the client possesses the CMK which is stored in either Azure Key Vault, Certificate Store, HSM.

SSMS Wizard generates both CEK and CMK. CMK is stored in the Client certificate or Azure Key Vault.

CMK is used to encrypt CEK. The encrypted CEK along with Path to CMK is what is stored in the database. So, the database has no way of decrypting the data.

Next SSMS Wizard begins the encryption process, where it creates a new temporary table, transfers the data from the table and encrypts it on the fly, and then drops and replaces the main table.

All of this occurs on the fly at the client side which is SSMS in this case.

The client will pass the ‘Column Encryption Setting=Enabled’ in the connection string, which fetches the Encrypted CEK and Path to CMK from the database.

The client uses the Path to CMK to get the CMK and uses it to decrypt the encrypted CEK received and then uses the CEK to decrypt the encrypted data.

To apply Always Encrypted, within SSMS, right-click the database and select Tasks > Encrypt Columns…

Below we have selected the Email Id column of MyContact table.

The below wizard step is used for generating CMK which can be either stored in Windows Certificate Store or Azure Key Vault with the client.

We can either run the encryption at that time or choose to generate a PowerShell Script which we can run later.

Below is the summary of steps that will be performed

Before encryption

After encryption

To decrypt here, at the client which is our SSMS in this case, right-click in the query window and select Connection > Choose Connection…

Specify column encryption setting=enabled in the Additional Connection Parameters tab.

We can see the actual data as the client SSMS in this case, peforms the decryption at the run time using the path to CMK and encrypted CEK details received from the database.

Usage within a console application –

Few points to consider while planning to use Always Encrypted –

https://www.sentryone.com/blog/aaronbertrand/t-sql-tuesday-69-always-encrypted-limitations

Reference – Pluralsight – SQL Server Course

Hope it helps..

Error – Please unblock each locked file or unlock XrmToolBox.zip before extracting its content

We were getting the below error while trying to run the XrmToolBox.

“One reason can be that at least one file is locked by Windows. Please unblock each locked files or unlock XrmToolBox.zip before extracting its content”

The resolution in our case was to reset the XrmToolBox installation i.e. deleting the content of

C:\users\appData\[loginname]\roaming\MsCrmTools\XrmToolBox

Get the details here

https://www.xrmtoolbox.com/documentation/for-users/reset-xrmtoolbox-installation/

Hope it helps..