SQL Server Error "AADSTS500133: Assertion is not within its valid time range" for Managed Identity

I created a Linked Service for an Azure SQL Database in Azure Data Factory. I then created a user in the database using the System Assigned Identity.

CREATE USER [crestlinecapitalADF-preProd] FROM EXTERNAL PROVIDER
GO
sys.sp_addrolemember @rolename = N'db_datareader', @membername = N'crestlinecapitalADF-preProd'
GO
sys.sp_addrolemember @rolename = N'db_datawriter', @membername = N'crestlinecapitalADF-preProd'
GO
sys.sp_addrolemember @rolename = N'db_ddladmin', @membername = N'crestlinecapitalADF-preProd'
GO

The script returned no errors. It seemed like the user was created and the roles were granted successfully.

When I tried to execute an ADF pipeline to load data into a table from a file, it returned an error an error saying that the either the table didn't exist or the right permissions weren't granted. I believed the issue was with the permissions needed to truncate a table prior to loading data. I experimented by granting the db_owner role to the system managed identity but to no avail.

Eventually, I decided to delete the Linked Service and re-create it. I even deleted the database user crestlinecapitalADF-preProd. However, this time, when I tried to execute the same script, it returned the following error:

Msg 33134, Level 16, State 1, Line 1 Principal 'crestlinecapitalADF-preProd' could not be resolved. Error message: 'AADSTS500133: Assertion is not within its valid time range. Ensure that the access token is not expired before using it for user assertion, or request a new token. Current time: 2024-12-13T04:38:21.1607509Z, expiry time of assertion 2024-12-13T02:50:36.0000000Z.

I tried changing the Linked Service numerous times hoping to regenerate the token but the issue remains unresolved.

I also got the same error when I tried to create user after dropping it from Azure SQL as you see below:

Failed to execute query. Error: Principal 'pdstackadf' could not be resolved. Error message: 'AADSTS500133: Assertion is not within its valid time range. Ensure that the access token is not expired before using it for user assertion, or request a new token. Current time: 2024-12- 13T06:47:07.1621877Z, expiry time of assertion 2024-12-13T06:46:01.0000000Z. Trace ID: 7e992e41-044b-456f-9378-c4c43fc77f00 Correlation ID: f33e664a-9049-42cb-ade0-e0a8df38c0a6 Timestamp: 2024-12-13 06:47:07Z'

The causes of error are various reason like the token issuer doesn't match the API version within its valid time range, Expired, Malformed, Refresh token in the assertion isn't a primary refresh token. As you drop the user token get expired and it try to connect the new user with that expired token only.

To resolve this issue, you need to disable the managed identity of Azure data factory and enable it again and then try to create a user and assign roles to it