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 – Bank Management button missing from Chequebook Maintenance window

Only a short post however I thought I’d blog this one as its a frequent query we receive on the Bank Management module in Dynamics GP.

Clients who have the “Bank Management” module for Dynamics GP will often create a new chequebook via “Cards > Financial > Chequebook” and notice the “Bank Management” option isn’t immediately visible to add the additional setup:

This is because you first need to grant access to the users in the bank management module and then you see the option.

You grant access via “Cards > Financial > Bank Management > Setup”

Once in the Bank Management Setup window click “Access”

Once in the Access window pickup the relevant users and grant them access to the new chequebook:

Once this has been granted the “Bank Management” option is visible on the chequebook in the “Cards > Financial > Chequebook” window.

Hope this helps someone else in the future.

Thanks for reading.

Dynamics GP – Odd issue using a DNS Alias for the SQL Server resulting in the error – No Obligations found on VAT Return

A colleague and I worked on a very interesting support ticket today. A client reported that when logging into Dynamics GP using their own Dynamics GP User ID they received the error “No Obligations Found” when retrieving the obligations for the VAT Return however it worked fine when logged in as “sa”.

**Please note there has been a service pack to correct this exact issue however this has been applied to the clients system so it can’t be causing this issue**.

As this worked with “sa” we checked the SQL Error log and surprisingly we found the following messages recorded at the time the routine had been ran:

As you can see the error log is scattered with “Login failed for user” messages for the user in the question.

The next part of the investigation was to setup a SQL Profile Trace to see what SQL statements were running just prior to the login failed message and we found the following:

As you can see the SQL statement “select SERVERPROPERTY(‘ServerName’)” runs just prior to the login failed errors.

My colleague then recalled they are using a DNS Alias for the SQL server name in the ODBC connection rather than the actual SQL server name.

We therefore ran “select SERVERPROPERTY(‘ServerName’)” on the server and, as expected, this returns the actual SQL server name rather than the alias name that is used in the server name in the ODBC connection on the clients PC.

Intrigued that the issue could somehow be related to how GP encrypts passwords using the SQL server name we setup the following test:

  1. Created an additional ODBC connection on the clients PC pointing to the actual SQL server name rather than DNS Alias name.
  2. Logged into Dynamics GP as “sa” using this new ODBC and created a new GP user, copying the security of the existing user. (so the password gets encrypted using the actual SQL server name)
  3. Finally, we logged into GP using the new ODBC and the new GP user.

After successfully logging in using the new user, and the ODBC pointing at the actual SQL server name, we walked through retrieving the obligations and the whole process worked. We also checked the SQL error log and there were no “Login Failed” error messages for the user.

I have to admit at this point I’m guessing as to what is going on however based on the results of this isolated test I can only assume that somehow during this process the users Dynamics GP credentials are being re-submitted to SQL using the actual SQL server name, and as the users password is encrypted with the SQL server DNS Alias name, its failing with “Login failed”. This works when logged in as “sa” because this is the only User ID that doesn’t have its password encrypted by GP. (its not encrypted because its the SQL System Administrator account)

Therefore although using a DNS alias can help with server changes (ironically because of how GP encrypts the password), this has got us thinking that using a DNS Alias for the SQL server could be causing other issues with GP that we don’t know about, so we may avoid this in the future if we can.

After discovering this little oddity I’d be interested if anyone else has any other experience of issues when using a DNS Alias for the SQL server.

Disclaimer :- I’d usually attempt to recreate this type of issue on my own system before documenting and sharing however I can’t in this instance as I don’t have an account with HMRC to complete the full process. However given how interesting this does seem I thought it worthwhile sharing anyway.

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 GP – Error Message Invalid length parameter passed to LEFT or SUBSTRING function in Edit Payables Check Window

I encountered this error when a client was trying to select a vendor in the “Edit Payables Cheque Batch” window. A screen shot of the error is below

The error occurred when trying to select a vendor from the list in the left hand pane of the window.

To troubleshoot this I created a SQL trace to capture all the SQL statements and also any error messages when the error occurred. This highlighted the exact error and offending SQL statement as per below:

Next I copied the offending SQL statement to SQL management studio and ran it in a query window. (I do have to tweak it a little and I also checked it wasn’t going to do any updates or inserts that could cause data issues). This gave me the same error being returned by Dynamics GP

Now it was a question of finding all the LEFT or SUBSTRING statements, commenting them out, and re-running the query until it worked. After some trial and error I found when commenting out the highlighted portion of the SQL statement below it executed without error

Therefore I now know the issue is with the A.KEYSOURC field. At this point I suspected it was blank and therefore the LEN calculation is producing a 0. The alias A has been given to the PM20100 table so I queried this table for the vendor in question and as suspected there is a blank KEYSOURC for this vendor.

I therefore deleted this blank record using a simple SQL script

DELETE PM20100 WHERE VENDORID='<InsertVendorid>' AND KEYSOURC=''

And now I can successfully select the vendor in the “Edit Payables Cheque Batch” window.

I hope this helps explain the issue and also how it can be fixed if anyone else encounters this in the future.

Thanks for reading!

Dynamics GP – Error Message PERIOD_KEY_INVALID when submitting VAT Return to HMRC

I had a client report this issue recently which was preventing them submitting the VAT return to HMRC via the government gateway. The exact error is below

After some digging it seems the period key is passed down from HMRC when the obligations are retrieved therefore its strange that the error is produced when submitting the VAT return as it should be using the same period key.

I looked at the TX00300 table (VAT Return Header) and I can see the PeriodKey was blank for the VAT Return ID in question

I then checked a clients system who I know has managed to process their VAT return successfully and this showed a PeriodKey in the TX00300 as per below

Therefore I can only assume that after pulling down the obligations either the PeriodKey wasn’t written to the TX00300 table correctly or the client accidentally changed a date in the VAT Return window which blanked the PeriodKey. Then, when they then tried to submit the VAT return they received the “PERIOD_KEY_INVALID” error.

To resolve the issue I had the client delete and recreate the VAT return ensuring no dates were altered after selecting the obligation and this then worked fine. (I did want to work through the whole process with them so I could check the PeriodKey in the TX00300 at every stage however unfortunately they processed everything before I had chance i.e. I wanted to check the PeriodKey value after getting the obligations, and then check it again after calculating and then prior to submitting)

In was a simple fix in the end but it was interesting piecing things together.

Thanks for reading.

Dynamics GP – The remittance reprint is including a remittance for a different vendor

A client reported that when reprinting a remittance via the “Recreate Check Stub” option it was correctly reprinting the remittance however another remittance for a totally different vendor was also printing at the same time?

The option being used for the remittance reprint is below

To troubleshoot I looked at the main table used on the “Reprint Check Remittance” layout in report writer.

As you can see from the image above this is the “PM Remittance Temp” table which on investigation is the PM80950 table.

When I queried this table in SQL I found a single row and the Vendor ID matched that of the remittance that was being tagged onto the reprint.

I therefore removed this row from the table by running a DELETE statement in SQL and now when I reprint the remittance it prints correctly.

I hope this helps someone else in the future if they come across the same issue.

Thanks for reading