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