Dynamics GP – Error Msg “A fatal error occurred while creating an SSL client credential. The internal error state is 10013” with the Web Client

A client reported the following error when trying to access the Dynamics GP web client:

Dynamics GP Web client – An unexpected error has occurred

The first thing I checked was the event viewer which highlighted the following error in the system log:

A fatal error occurred while creating an SSL client credential. The internal error state is 10013

I then checked through the system log and found this first starting happening 4 days ago which coincided with a server reboot.

As I knew nothing had changed with the GP environment I asked the clients IT department if any maintenance or other changes had occurred prior to the reboot and they informed me they had disabled both SSL v2 and SSL v3, TLS 1.0 and TLS 1.1.

I knew the issue had to be related to these changes however as this isn’t my area of expertise I called on the assistance of a colleague who after researching themselves advised me to add the following reg keys via an elevated command prompt

reg add HKLM\SOFTWARE\Microsoft\.NETFramework\v4.0.30319 /v SchUseStrongCrypto /t REG_DWORD /d 1 /reg:32

reg add HKLM\SOFTWARE\Microsoft\.NETFramework\v4.0.30319 /v SchUseStrongCrypto /t REG_DWORD /d 1 /reg:64

I then recycled the application pool in IIS and restarted all web client related service and this now works.

To confirm the server operating system was Windows Server 2012 R2 and they are using Dynamics GP 2016.

Hope this helps someone else facing a similar issue in future.

Thanks for reading.

Dynamics GP – All rows mysteriously deleted from SOP Distribution Work\History (SOP10102)

This week I dealt with a very bizarre issue affecting all Sales Order Processing Transactions that I thought was worth documenting. It started with a simple support request explaining that Sales Order Processing Invoice transactions weren’t posting due to missing distributions. See below

Missing SOP Distributions

When I connected to investigate further I found the Sales Order Processing Distribution Work\History table (SOP10102) was, with the exception of a hand full of rows, completely blank. (and those rows related to the most recently added SOP transactions)

As this table contains the distributions for both Work and History documents all distributions on posted Sales Order Processing transactions had been lost as well.

Before I even considered how this could have happened I set about trying to find out which user could have done this, and when it happened, so I could recover the missing data.

The first clue for when this could have happened lay with the SQL transaction log backups. As this table would have had hundreds of thousands of rows, I suspected this would have generated lots of SQL transaction log, so I checked the size of the transaction log backups and this highlighted a larger than normal LOG backup at midday (I could do this as the SQL database is in FULL recovery model and the client performs hourly transaction log backups)

Large transaction log backup at midday

I now suspected the issue had arose between 11am and midday so I set about querying the contents of this SQL transaction log backup file looking for transactions with deletes on the SOP10102 table using the query below.

(As usual I’m standing on the shoulders of giants for this one by using this excellent blog post by SQL guru Paul Randal as the basis of the query https://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/. As a side note this website is an awesome SQL resource!)

select	
	[begin time] as BeginTime,
	[End Time] as EndTime,
	SUSER_SNAME ([transaction sid]) as UserID, 
	object_name(object_id) as TableName, *
FROM fn_dump_dblog 
	(NULL, NULL, N'DISK', 1, N'D:\...\backup_2019_10_07_120002_5428125.TRN', 
	 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) as log1
left join sys.partitions part 
	 on part.partition_id=log1.partitionid
where  
	[transaction id] IN
(select 
	[transaction id]
 FROM fn_dump_dblog 
	(NULL, NULL, N'DISK', 1, N'D:\...\backup_2019_10_07_120002_5428125.TRN',
	DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) as log1
left join sys.partitions part 
	on part.partition_id=log1.partitionid
where 
	operation = 'LOP_DELETE_ROWS' and 
	object_name(object_id) in ('SOP10102')) 
order by [Transaction ID]

I scanned through the results and found the transaction id below which was logging lots of deletes on the SOP10102 table

Transaction showing deletes on SOP10102

I then took this Transaction ID and ran the following SQL query to get full details on this SQL Transaction

select 
	[begin time] as BeginTime, 
	[End Time] as EndTime,
	SUSER_SNAME ([transaction sid]) as UserID, 
	object_name(object_id) as TableName, *
FROM fn_dump_dblog 
	(NULL, NULL, N'DISK', 1, N'D:\...\backup_2019_10_07_120002_5428125.TRN', 
	 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
	 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) as log1
left join sys.partitions part 
	on part.partition_id=log1.partitionid
where  [transaction id] IN ('0000:088fba17')

This query returned 1,466,128 rows all of which are deletes to SOP10102. (this is more than the number of rows in the table which is likely because of the additional deletes on the SQL non clustered indexes that also need maintaining)

Lots of deletes on SOP10102 table

Following on from this I’m now know the user who has caused the deletes, and the exact time it happened. I also know this SQL Login ID relates to a GP user which means this has to have happened inadvertently while using GP. (you can’t use a Dynamics GP SQL Login to access the data via any tool other than Dynamics GP as the password is encrypted)

I asked the user in question if they noticed anything strange at this time like GP crashing or hanging but unfortunately they don’t recall. Therefore this is still a total mystery I’m afraid.

To recover the data I restored the backup to a new database right up to the point of the data going missing i.e. I restored the FULL SQL backup and all LOG backups with NORECOVERY with the exception of the last LOG backup which I restored as per below

I then copied the data back using the SQL query below

INSERT INTO LIVEDB..SOP10102
(SOPTYPE, SOPNUMBE, SEQNUMBR, DISTTYPE, DistRef,
ACTINDX, DEBITAMT, ORDBTAMT, CRDTAMNT, ORCRDAMT,
CURRNIDX, TRXSORCE, POSTED, Contract_Exchange_Rate)
SELECT
A.SOPTYPE,
A.SOPNUMBE,
A.SEQNUMBR,
A.DISTTYPE,
A.DistRef,
A.ACTINDX,
A.DEBITAMT,
A.ORDBTAMT,
A.CRDTAMNT,
A.ORCRDAMT,
A.CURRNIDX,
A.TRXSORCE,
A.POSTED,
A.Contract_Exchange_Rate
FROM RESTOREDDB..SOP10102 A
LEFT JOIN LIVEDB..SOP10102 b
on a.SOPNUMBE=b.SOPNUMBE and a.SOPTYPE=b.SOPTYPE
WHERE
B.SOPNUMBE IS NULL

After running the INSERT query the client confirmed that all the data had been recovered.

I’d be interested to know if anyone else has seen anything similar in the past. Unfortunately I’ve also seen this happen on the Purchase Order Processing side but it was before my blogging days. I’ve also found this Microsoft blog which documents a similar issue that Microsoft support dealt with https://blogs.msdn.microsoft.com/developingfordynamicsgp/2010/04/08/pop-distributions-randomly-being-deleted/

Although I didn’t get to the bottom of the missing POP data I did have my suspicions however there are no related third parties that could have caused this issue with SOP10102.

Unfortunately I suspect this will also remain an unsolved mystery I just hope it doesn’t happen again.

Thanks for reading

Dynamics NAV – Message “Number of source documents posted: 0 out of a total of 1” when posting a Warehouse Receipt

Today I ran into an issue when posting a Warehouse Receipt to a new location I just created. I’m using a new location as I’m testing the processes of Warehouse Receipt and Putaways along with Warehouse Shipments and Picks and I’d rather do this on a new location.

I thought it worth documenting this process as when troubleshooting why the Warehouse Receipt wouldn’t post I found a setup option I could change which gave me a better, more comprehensive error, enabling me to get to the root cause of the issue. This was crucial to understanding why the Warehouse Receipt wouldn’t post.

Starting from the beginning, to create the new location I went to the “Location Setup” window and created the location as per below

Location Setup

I then went ahead and created a Purchase Order so I could work through creating a Purchase Order followed by a Warehouse Receipt and Putaway. I did this by creating a Purchase Order and clicking “Create Whse. Receipt”

Create Whse. Receipt

Success. This created a Warehouse Receipt, and as all the relevant information was populated, I just need to post this to create the Putaway however when trying to post the document I was presented with the message below:

Number of source documents posted: 0 out of a total of 1

Hmmm. The error isn’t giving anything away, and when I doubled checked everything seems fine so why isn’t it posting?

After much pondering and testing I stumbled across the setting below in the “Warehouse Setup”

Warehouse Setup

At the moment its set to “Posting errors are not processed”. I therefore switched this to “Stop and show the first posting error” in the hope it would give me more information when posting.

Warehouse Setup

I then tried to post the Warehouse Receipt again and now I get a much better error message

The error is suggesting I haven’t setup posting accounts against my new location. Doh!

I therefore went back to “Location Setup” and clicked “Inventory Posting Setup” and populated the Inventory posting group and posting accounts as per below

I then went back to my Warehouse Receipt and now all the posting accounts have been added it posts fine.

I’m guessing it would have taken much longer to get to the root cause of the issue if it wasn’t for that setting in “Warehouse Setup” so I’m sure this will come in handy in the future as well.

Thanks for reading!