Issues with DBsync step during deploy


Today, when I was deploying customization package to a newly deployed config environment, I had an issue with a step not working correctly. The environment had not yet been used for anything so I hadn´t even copied a database to it. When I deployed the customization package to it I got the following error in the runbook log and the deploy failed:

Table Sync Failed for Table: SQLDICTIONARY. Exception: System.NotSupportedException: TableID not yet generated for table: AmcBankReconciliations

The sync step in the runbook is failing because there is no TableID for the table AmcBankReconciliations. And I thought that was exactly what the sync process was supposed to do (??).

Having no clue about why this happened I first turned to Google (as one does) and when I could not find anything there I asked my awesome colleagues and one of the said:

“I have seen newly deployed environments behaving strangely and my solution usually is to start Visual Studio and perform a DB Sync”

This was a bit strange since it was the Sync Step that failed but I thought I would give it a try. Since this was a config environment that is not going to use Visual Studio, I instead opted for using the amazing [d365fo.tools](GitHub – d365collaborative/d365fo.tools: Tools used for Dynamics 365 Finance and Operations) to do the sync

Invoke-D365DBSync -Verbose

When the sync had finished I tried resuming the deploy and to my surprise it finished perfectly… Nice 🙂

User Access issues to Synapse Serverless Views

I just set up the CDMUtil for a customer to let them access Dynamics 365 for Finance and Operations Data, exported to a DataLake, through a serverless instance in Synapse. The customer had added a new user and got an error which vas not perfectly logical

Msg 15151, Level 16, State 1, Procedure CustTable, Line 1 [Batch Start Line 0]
Cannot find the CREDENTIAL 'dynamics365_financeandoperations_XXXXXXXX_sandbox', because it does not exist or you do not have permission.
Msg 4413, Level 16, State 1, Line 223
Could not use view or function 'dbo.CustTable' because of binding errors.

The issue was obiously a permission issue… but where??

When we added an earlier user we set up a Role in SQL that had read access to the SQL DataBase. When we added the user to that role it started working… The confusing part was that the error message did not mention the new user anywhere.

When you set up the Synapse connection to Data Lake and FnO… Make sure you add the correct permissions to new SQL-Native users using TSQL in Management Studio.

Beware of the BacPac

The other day one of my colleagues needed a database copied from PROD to a DEV OneBox. Unfortunately there is no direct route from PROD to Onebox, you need to make a refresh to a Tier-2 environment and then move the database from the Tier-2 environment to LCS and then restore it to the OneBox environment. The issue I had was that I need to keep the data in the Tier-2 environment.

I used LCS to make an export to LCS, performed the entire process from above and it was finally time to restore the export… did not work… The issue I was getting was an error trying to create a trigger referring to a missing column. When I looked closer at this I found that the column was missing had been removed a long time ago and (in AX/D365 removed columns gets the prefix DEL_ and this column had that). Apparently the column had been removed before the export was done and for some reason the export was not consistent.

There are a couple of different solution to this problem…

In my case I opened up the bacpac file (it is apparently a zip file). Inside is all the data and the schema for the database. I opened the model.xml file and search for the T-SQL statement that created the trigger, I changed the statement to point to the DEL_ table, saved the file and put it back into the zip and renamed it back to bacpac.

When I tried to import it I got an error that said “mismatching checksum”. In the file called origin.xml there is a line containing the checksum for model.xml:

<Checksum Uri=”/model.xml”>8E0D68CEA76ACDB4DD4EE9853065E0E10D9C426F0CC880D18ABC19AAC64624C9</Checksum>

This prevents someone tampering with the model file. To generate a ned checksum for the model file, run the following PowerShell script and point to the model.xml file. Past the HASH in origin.xml

$modelXmlPath = Read-Host "model.xml file path"

$hasher = [System.Security.Cryptography.HashAlgorithm]::Create("System.Security.Cryptography.SHA256CryptoServiceProvider")

$fileStream = new-object System.IO.FileStream ` -ArgumentList @($modelXmlPath, [System.IO.FileMode]::Open)

$hash = $hasher.ComputeHash($fileStream)

$hashString = ""

Foreach ($b in $hash) { $hashString += $b.ToString("X2") }

$fileStream.Close()

$hashString

Put both Origin.xml and model.xml in the zipfil and change the extension to bacpac. Import it again.

The final takeaway: DO NOT RELY ON BACPAC EXPORTS for backup!!! Or at least test them in some way…

Note added after post:

A safer way to do this is probably (as long as we still have RDP access to t-2 environment):

Creating a new database as a copy of the original (since you are going to mess with it) by running this SQL query:

CREATE DATABASE MyNewCopy AS COPY OF axdb_mySourceDatabaseToCopy

Since this operation runs asynchronously in the background you will have to keep an eye on it before continuing:

SELECT * FROM sys.dm_database_copies

Renaming a OneBox Dev environment

Hi

Tonight I helped one of my developer colleagues who had issues with one of his DEV environments. The issue was that he had renamed the VM (you cannot have multiple machines in Azure DevOps with the same name). The problem was that he had not completed all the steps that is required to rename so it was behaving a bit weird.

First of all we were not able to connect to Reporting Services Configuration Manager. The solution was unfortunately not very logical… In the onebox environment there is a couple of SQL components installed from different SQL Server Versions:

If you use the wrong SSRS Configuration Manager it is not able to connect to the SSRS Server and get the error message: No report servers were found

The correct version is 2016! Completely logical… NOT!

When this was done we just followed this guide to complete the rename and all is well 🙂

/Johan

Links:
https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/migration-upgrade/vso-machine-renaming
http://www.alexondax.com/2017/05/what-i-do-to-rename-d365-virtual.html
https://community.dynamics.com/365/financeandoperations/f/765/t/269137

Moving SQL tempdb to another drive

Today I urgently needed to move tempdb from one drive to another to free up space. So I googled to find a script and found this one by Brent Ozar. It has been added to StackExchange and refined a bit.

DECLARE @newDriveAndFolder VARCHAR(8000);

SET @newDriveAndFolder = 'Z:\YourTempDBfolder';

SELECT [name] AS [Logical Name]
    ,physical_name AS [Current Location]
    ,state_desc AS [Status]
    ,size/1024 AS [Size(MB)]
    ,'ALTER DATABASE tempdb MODIFY FILE (NAME = ' + QUOTENAME(f.[name])
    + CHAR(9) /* Tab */
    + ',FILENAME = ''' + @newDriveAndFolder + CHAR(92) /* Backslash */ + f.[name]
    + CASE WHEN f.[type] = 1 /* Log */ THEN '.ldf' ELSE '.mdf' END  + ''''
    + ');'
    AS [Create new TempDB files]
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb')
ORDER BY f.[type];

Change the newDriveAndFolder variable and run the script and it will generate the T-SQL statements you need to move them.

ALTER DATABASE tempdb MODIFY FILE (NAME = [temp2] ,FILENAME = ‘F:\MSSQLDATA\temp2.mdf’);
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp3] ,FILENAME = ‘F:\MSSQLDATA\temp3.mdf’);
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp4] ,FILENAME = ‘F:\MSSQLDATA\temp4.mdf’);
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp5] ,FILENAME = ‘F:\MSSQLDATA\temp5.mdf’);

Worked like a charm

/Johan

Azure SQL SqlPackage error

Today I tried to do a restore of database to an Azure DB in a Dynamics 365 for Operations environment. When I ran SQLPackage.exe I got the following error:

‘Unable to connect to master or target server ‘AxDB_New’. You must have a user with the same password in master or target server ‘AxDB_New’

The issue here is that the version of SQL Management Studio that is provided in the Azure VM is version 16 and Azure SQL requires version 17… with that background I am not convinced that the error message is totally relevant…

The solution is to upgrade Management Studio to the latest Version.

image

/Johan

Error Doing a Full Data Sync

Today I am setting up a proof of concept for the retail solution in AX 2012 R3. As a base I am using the Microsoft AX 2012 R3 demo VM deployed to Azure using LCS

When Trying to do a  full data sync I got the following error:
”The target principal name is incorrect. Cannot generate SSPI context”

SSPI context has to do with Service Principal Name in Active Directory. The Service Account for SQL (contoso\sqlsvc) needs to have SPN set like this:

setspn –A MSSQLSvc/AX2012R2A:1433 contoso\sqlsvc
setspn –A MSSQLSvc/AX2012R2A.contoso.com:1433 contoso\sqlsvc

Where AX2012R2A and AX2012R2A.contoso.com are hostname and FQDN for the AX demo VM.

In my case I had duplicate FQDN and no hostname. I cleaned out the duplicates and added the hostname.

Links:
https://dynamicsessentials.net/2015/04/12/the-target-principal-name-is-incorrect-crm-setup/#comments

CREATE TABLE permission denied in database ‘tempdb’

This morning we had an incident with a customer who during the night had failed over their SQL cluster during the night and then failed it back again and now AX had stopped working. The error was this:

image

The problem is that when SQL is restarted the permission on tempdb is reset. AX solves this is a “special” way. When AX is installed the install created a stored procedure in master on the SQL server which it calls to set the permissions in case they get lost.

2016-11-10_09-15-41

These was unfortunately missing on one of the nodes so I used Management Studio to script them out and the create them on the node where they were missing

/Johan

SQL Express and the missing config file

When you install Microsoft SQL Server the install generates a configuration file. This is great if you want to create an unattended install of SQL. If you want to do a second install (or third or fourth…) just run: 

Setup.exe /ConfigurationFile=ConfigurationFile.INI

and you get the same install again.

Today I wanted to do the same with SQL express… but no file was created… Hmmm…

Apparently you need to start the install using this:

Setup.exe /ACTION=INSTALL /UIMODE=Normal

/Johan

Links

http://www.codeproject.com/Questions/713204/SQL-Server-How-to-generate-a-configuration-fi

Sharepoint Detective work

Today I was looking at a malfunctioning Sharepoint (for enterprise Portar) the problem was that it could not find the database for either configuration or content which meant that it was pretty much screwed. I am not the one who installed this so I did not know there to start looking. Since I am not very proficient in Sharepoint I decided to start from the beginning… Where should the database be located? I tried launching Sharepoint Central Administration… did not work. Tried launching the Sharepoint Powershell Admin console… Could not find farm… I looked in the eventlog… no mention of any SQL Server name…

So I searched around abit online and found the most basic setting… where does Sharepoint expect to find:

SharePoint 2007:
SharePoint Server config database connection string location for SharePoint Server 2007
My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.O\Secure\ConfigDb

SharePoint 2010:
SharePoint Server config database connection string location for SharePoint Server 2010
My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.O\Secure\ConfigDb

SharePoint 2013:
And for SharePoint 2013…
My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\15.O\Secure\ConfigDb

So this meant that I found the SQL server and could continue troubleshooting…

Links:

https://absolute-sharepoint.com/2013/06/sharepoint-server-config-database-connection-string.html