Without a doubt, today I encountered one of the most bizarre situations in the BizTalk Server Administration Console: The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server, Error: 0). And believe me, I been here along time.
Everything
was working fine, and I was normally working on the environment until I try to
refresh the BizTalk Server Administration Console and I got the following
error:
TITLE: BizTalk Server Administration
——————————
Failed to load Group [SQLSERVERNAMEINSTANCE:BizTalkMgmtDb] data providers. (Microsoft.BizTalk.Administration.SnapIn)
For help, click: http://go.microsoft.com/fwlink/?LinkId=47400&ProdName=Microsoft+BizTalk+Server+2016&ProdVer=3.12.774.0&EvtSrc=Microsoft.BizTalk.Administration.SnapIn.Properties.Errors&EvtID=FailedLoadingGroupProviders&EvtChain=Microsoft.BizTalk.Administration.SnapIn.Properties.Errors+%2cFailedLoadingGroupProviders%3bMicrosoft.BizTalk.Administration.SnapIn.Properties.Errors+%2cFailedLoadingGroupProviders%3bMSSQLServer+%2c0
——————————
ADDITIONAL INFORMATION:
Failed to load Group [SQLSERVERNAMEINSTANCE:BizTalkMgmtDb] data providers. (Microsoft.BizTalk.Administration.SnapIn)
For help, click: http://go.microsoft.com/fwlink/?LinkId=47400&ProdName=Microsoft+BizTalk+Server+2016&ProdVer=3.12.774.0&EvtSrc=Microsoft.BizTalk.Administration.SnapIn.Properties.Errors&EvtID=FailedLoadingGroupProviders&EvtChain=Microsoft.BizTalk.Administration.SnapIn.Properties.Errors+%2cFailedLoadingGroupProviders%3bMSSQLServer+%2c0
——————————
Failed to load Group [SQLSERVERNAMEINSTANCE:BizTalkMgmtDb] data providers. (Microsoft.BizTalk.Administration.SnapIn)
For help, click: http://go.microsoft.com/fwlink/?LinkId=47400&ProdName=Microsoft+BizTalk+Server+2016&ProdVer=3.12.774.0&EvtSrc=Microsoft.BizTalk.Administration.SnapIn.Properties.Errors&EvtID=FailedLoadingGroupProviders&EvtChain=MSSQLServer+%2c0
——————————
The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476
——————————
BUTTONS:
OK
——————————
But let me give some context because problems
will not happen without any reason. I was normally working with the BizTalk
Server Administration Console until I got a notification that my password would
expire in 5 days and at the same time suggesting for me to change right away
and… I did that for once in my life, I follow the recommendation and do not
wait any longer to change the password.
Once I successfully update my password and remote
access once again to my environment I started getting this issue in the admin
console.
Cause
In a way, unfortunately, after I successfully update my password and remote access once again to my environment, I didn’t go directly to the BizTalk Server administration console. I was doing some configuration in IIS regarding SSL and only after a few minutes I went to the admin console and got this bizarre issue.
I did the basic tasks you normally do in these
situations, nothing related to this particular error but in general:
Restart
the host instances and Enterprise Single sign-on;
Check
if the WMI service is running or Restart WMI service;
Close
the BizTalk Administration Console and open again;
Open
the SQL Server Management Studio and try to connect to the BizTalk SQL Instance
The curious is that all host instances and ESSO
service was working fine, and they were able to restart successfully, and I was
able to connect to the BizTalk SQL Server Instance from the BizTalk Server
machine using SSMS, but none of them resolved the issue with the BizTalk admin
console.
And this error may happen for other different
reasons, but you have to understand my scenario:
in one minute, everything was working fine, and now it was failing.
And the only difference was I had to change my password.
I do not know to explain better the cause of
the issue in my case, but, in simple words, it was related to some bizarre system
credential cache.
Solution
I was able to fix this issue by simply:
logging
out of the BizTalk Server machine where I was executing the BizTalk Server
Administration Console
and
logging back in again.
Once I log in back in the BizTalk Server
machine I was able to work normally with the BizTalk Administration Console once
again.
The BizTalk Documenter has been available for many years and different
BizTalk versions, starting with 2004 to the latest one: BizTalk Server 2016. And once again, without a doubt
for me, BizTalk Documenter is my favorite documentation tool, and I do think that if each
product had a tool like for the generation of technical documentation, it would
be simpler to do, as the existing documentation significantly improved.
However, the default cover page is quite simple
and looks old fashion:
CHM files are the HTML Help 1.0 specification witch
is quite pretty old. Basically, It is a complete HTML based Help system that
uses a Help Viewer that internally uses Internet Explorer to render the HTML
Help content that was introduced somewhere in 1997 when Internet Explorer 4 was
also introduced.
Nevertheless, CHM continues to be a popular
help format because it is very easy to produce content for it, using plain HTML,
which is quite interactive and because it works with many Windows application
platforms out of the box.
And I normally like to personalize my documentation
according to the client like:
Using
the Logotype and there name on the cover;
Using
their color pattern;
Sometimes
using some cover picture;
So, the question here is: Can we customize
the cover page produce by BizTalk Documenter?
And the answer is obviously: yes, of course,
you can.
And I been doing that in my client for a long
time, however, and this also serves me as a personal reminder, because each time
I’m going to a new client I’m always:
Forgetting how to do it;
What king of resources do I need to have? And what are there names? if they are mandatory;
Taking a long time to format the HTML according to my requirements;
And don’t get me wrong there are several blog
posts guiding you on how to do it. But most of them do not provide you the
resources for you to download and:
use
it as a template;
Where
you can easily modify the pictures, colors and so on;
Or
use it as an inspiration, tutorial or sample to your custom cover;
How to customize the BizTalk
Documenter cover page
What is really necessary to transform
your cover page from the default one presented in the picture above to something
like this:
That contains:
1-
Logotype of BizTalk Server version;
2-
Header title and subtitle;
That
can be your platform code name like “<client name> ESB”;
3-
Logotype of the client organization;
4-
Name of the documentation;
5-
Other pictures like for example project logotype;
6-
Disclaimer;
7-
background color;
8-
Copyright;
Or something like this with a background
picture:
And in fact, this is a quite simple task. Fortunately for us, BizTalk Documenter allows us to specify a Resource Folder on the Output Options screen:
For the sake
of simplicity, let’s call it Resources (but it can be other as you see
in the picture Resource V1). In the root of this folder, you should:
Add
a custom HTM page that you mandatory need to called it: titlePage.htm
If
you give it another name it will not work. The file will be ignored and the
default cover will be generated;
And
you should, once again for the sake of simplicity, add all the necessary
picture files on the folder or sub-folder;
This is the content/aspect of my titlePage.htm file for the first sample provide it here:
Without a doubt, BizTalk Documenter is my favorite documentation tool, and I do think that if each product had a tool like for the generation of technical documentation, it would be simpler to do, as the existing documentation significantly improved. I do miss this kind of tool for Azure Integration projects.
However,
each time I install this tool I always got the exact same problem:
Unable to locate the help compiler executable
which I know well, and I know why since it was very well explained by Mitch Vanhelden in this blog post.
So, why this blog post?
Well, for two main reasons:
I’m
always searching to find the link to the component I need to install;
Basically,
this is just an easy personal reminder;
And
second and most important, the link to the resource that Mitch point is
obsolete and not working anymore;
Cause
Has Mitch explained in his blog post, the
reason for this is quite clear, the application can’t locate the help compiler
executable, either because:
it
isn’t installed à most common situation
or
it is also possible if you’re working on a 64-bit machine.
Solution
Make sure you have installed the HTML Help
Workshop compiler because this is the most common cause for this issue and if
not:
First, download and install this compiler that can be found here: HTML Help Workshop and Documentation;
And then install it, by executing the htmlhelp.exe file
On the HTML Help Workshop 1.3 screen, click Yes
On
the HTML Help Workshop 1.3 Setup screen, click Yes
Specify
the installation directory and then click OK
And
finally, when the installation finish, click OK
After these steps, you should be able to
generate the BizTalk Server documentation form the tool. Otherwise, make sure
that the path to this help compiler is configured correctly in BizTalk
Documenter by:
Access
to the installation path of BizTalk Documenter
By
default: C:Program Files (x86)Microsoft ServicesBizTalk Documenter
Open Microsoft.Services.Tools.BiztalkDocumenter.exe.config file and validate
and, if necessary, change the path for the HelpCompilerLocation key
that needs to contain the correct path to the HTML Help Workshop compiler
component.
A message sent to adapter “WCF-Custom” on send port ” STAGING_SQL_WCF_SEND” with URI “mssql://SQL-SERVER-NAME//Database?” is suspended. Error details: Microsoft.ServiceModel.Channels.Common.MetadataException: Object [dbo].[IdRecord] of type StoredProcedure does not exist
Server stack trace: at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result) at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result) at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result) at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)
Exception rethrown at [0]: at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result) at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result) MessageId: {0193EE6F-8DFF-4861-87FB-FC1C82ECF3AB} InstanceID: {59E3F39A-BF24-4583-BEA9-78CED5B621F7}
However, despite this error and despite the
fact that we were talking about the same server and same project of the previous
issue, one thing I was sure about: they were not related at all.
Cause
At a first glimpse, it seems that the stored
procedure does not include the column name: IdRecord, or that this field
was not passed to the stored procedure at all. So, this was my first point to validate,
however, quickly I realized that:
The
stored procedure had that field;
And
I was correctly passing that field to the stored procedure in the message;
Just to precaution, I double-check if that was
not related to security permission, and it was not also. So I went back to my some
conspiracy theories list that I described on my previous post:
You
should regenerate schemas: no, never
or
that may be a mismatch in the namespaces: I believe it could be a
possibility, but no, since I was using the same scheme and it was working fine
that
the “?” character that you normally find in the URI is causing this problem: also
impossible in my case.
And
my favorite is that you should give “sysadmin” rights to the service account
that is running the host instance: never.
But the last one put me thinking: the
operation is not properly set.
Because the last change I did was redesign the
solution that was performing composite operations with the SQL Adapter, in my
case I was sending multiple-rows to update in the same message.
And now I was doing a single operation.
And by doing that the WCF-SQL Adapter was throwing
this strange behavior.
Solution
The solution was quite simple. We just have to
change the Action CompositeOperation with the correct operation action mapping
as show bellow as an example:
Recently a
friend of mine that is working with me in a project send me an email reporting
a quite curious issue that I found while accessing the BizTalk Server
Administration console in our development environment:
This operation failed while accessing at least one of the Message Bix databases. Some results might be omitted. (Microsoft.Biztalk.Administration.SnapIn)
Additional information:
The transaction log for database ‘BizTalkMsgBoxDb’ is full due to ‘LOG_BACKUP’. (Microsoft SQL Server, Error: 9002)
Immediately I point out to the team that this issue
was related to lack of disk space.
Cause
The official cause of
this issue is that when the transaction log becomes full, SQL Server Database
Engine issues a 9002 error. The log can fill when the database is online, or in
recovery. If the log fills while the database is online, the database remains
online but can only be read, not updated.
And for us to know the exact cause for what is
preventing log truncation, we can use the log_reuse_wait and log_reuse_wait_desc
columns of the sys.database catalog view.
In our case, it was indeed a problem with disk
space and what happened was that the disk to where we were doing backup went out
of disk space, because we cannot do the backups the transaction log grow until
the point that disk (that contain the log file) also went out of disk space.
Solution
When you know the issue, the solution is quite easy. In this case, freeing disk space from both hard drives immediately solves the problem. Nevertheless, because the log file got quite big you should think of stopping your BizTalk Server environment and do maintenance in your databases in special reduce the size of the transaction log.
For that you should:
Perform
a full back of your databases;
Stop
all BizTalk Server services (host instances and enterprise Single Sign-on)
And
run the following SQL Script
ALTER DATABASE BizTalkMsgBoxDb
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (BizTalkMsgBoxDb_log, 2048);
GO
ALTER DATABASE BiztalkMsgBoxDb
SET RECOVERY FULL
GO
Do
about the same steps for other databases whose transaction logs are also quite large.
It was only 3 days ago that I released the latest version of this package, but someone (aka Wagner Silveira) alerted me to the existence of new shiny icons in the Azure Portal… so I decided it would be a good time to launch a new major release and here it is! I hope you guys enjoy.
Microsoft Integration, Azure, Power Platform, Office 365 and much more Stencils Pack it’s a Visio package that contains fully resizable Visio shapes (symbols/icons) that will help you to visually represent On-premise, Cloud or Hybrid Integration and Enterprise architectures scenarios (BizTalk Server, API Management, Logic Apps, Service Bus, Event Hub…), solutions diagrams and features or systems that use Microsoft Azure and related cloud and on-premises technologies in Visio 2016/2013:
BizTalk Server
Microsoft Azure
Integration
Integration Service Environments (ISE)
Logic Apps and Azure App Service in general (API Apps, Web Apps, and Mobile Apps)
Azure API Management
Messaging: Event Hubs, Event Grid, Service Bus, …
Azure IoT and Docker
AI, Machine Learning, Stream Analytics, Data Factory, Data Pipelines
SQL Server, DocumentDB, CosmosDB, MySQL, …
and so on
Microsoft Power Platform
Microsoft Flow
PowerApps
Power BI
Office365, SharePoint,…
DevOps and PowerShell
Security and Governance
And much more…
… and now non-related Microsoft technologies like:
SAP Stencils
The Microsoft Integration Stencils Pack is composed of 27 files:
Microsoft Integration Stencils
MIS Additional or Support Stencils
MIS AI and Machine Learning Stencils
MIS Apps and Systems Logo Stencils
MIS Azure Additional or Support Stencils
MIS Azure Mono Color
MIS Azure Old Versions
MIS Azure Others Stencils
MIS Azure Stencils
MIS Buildings Stencils
MIS Databases and Analytics Stencils
MIS Deprecated Stencils
MIS Developer Stencils
MIS Devices Stencils
MIS Files Stencils
MIS Generic Stencils
MIS Infrastructure Stencils
MIS Integration Fun
MIS Integration Patterns Stencils
MIS IoT Devices Stencils
MIS Office365
MIS Power BI Stencils
MIS PowerApps and Flows Stencils
MIS SAP Stencils
MIS Security and Governance
MIS Servers (HEX) Stencils
MIS Users and Roles Stencils
That you can use and resize without losing quality, in particular, the new shapes.
What’s new in this version?
I still have many things to do in this project in terms of organization and cleaning some resources but I will leave that for another occasion. The main goal of this release was to provide the new icons present in Azure Portal. In this version the changes and additions are:
New shapes: I think almost all the new shapes layout present in Azure Portal are now added in this package.
New categories: MIS Azure Mono Color, MIS Azure Old Versions, MIS Azure Others, MIS Integration Fun;
Categories Renaming: MIS Databases and Analytics and MIS AI and Machine Learning
SVG Files: The SVG files, from all these new resources, are now available on GitHub
Microsoft Integration, Azure, Power Platform, Office 365 and much more Stencils Pack it’s a Visio package that contains fully resizable Visio shapes (symbols/icons) that will help you to visually represent On-premise, Cloud or Hybrid Integration and Enterprise architectures scenarios (BizTalk Server, API Management, Logic Apps, Service Bus, Event Hub…), solutions diagrams and features or systems that use Microsoft Azure and related cloud and on-premises technologies in Visio 2016/2013:
BizTalk Server
Microsoft Azure
Integration
Integration Service Environments (ISE)
Logic Apps and Azure App Service in general (API Apps, Web Apps, and Mobile Apps)
Azure API Management
Messaging: Event Hubs, Event Grid, Service Bus, …
Azure IoT and Docker
AI, Machine Learning, Stream Analytics, Data Factory, Data Pipelines
SQL Server, DocumentDB, CosmosDB, MySQL, …
and so on
Microsoft Power Platform
Microsoft Flow
PowerApps
Power BI
Office365, SharePoint,…
DevOps and PowerShell
Security and Governance
And much more…
… and now non-related Microsoft technologies like:
SAP Stencils
The Microsoft Integration Stencils Pack is composed of 23 files:
Microsoft Integration Stencils v4.0.2
MIS Additional or Support Stencils v4.0.0
MIS AI Stencils v4.0.0
MIS Apps and Systems Logo Stencils v4.0.0
MIS Azure Additional or Support Stencils v4.0.0
MIS Azure Others Stencils v4.0.0
MIS Azure Stencils v4.0.2
MIS Buildings Stencils v4.0.0
MIS Databases Stencils v4.0.0
MIS Deprecated Stencils v4.0.0
MIS Developer Stencils v4.0.0
MIS Devices Stencils v4.0.0
MIS Files Stencilsv4.0.0
MIS Generic Stencils v4.0.0
MIS Infrastructure Stencils v4.0.0
MIS Integration Patterns Stencils v4.0.0
MIS IoT Devices Stencils v4.0.0
MIS Office365 v4.1.0
MIS Power BI Stencils v4.0.0
MIS PowerApps and Flows Stencils v4.0.0
MIS SAP Stencils v4.0.0
MIS Security and Governance
MIS Servers (HEX) Stencils v4.0.0
MIS Users and Roles Stencils v4.0.0
That you can use and resize without losing quality, in particular, the new shapes.
What’s new in this version?
I’m doing some changes in the project in terms of organization and resources but instead of taking too much time doing it from top to bottom, I will be releasing small versions until the job is done. In this version the changes and additions are:
New shapes: near 50 new shapes have been added:
3 related to Microsoft Intune – a requested from a community member;
47 related to Security and Governance;
New category: I add a new file to the project called: Security and Governance with 47 shapes;
SVG Files: I’m starting to provide also the SVG files, from all these resources. At the moment these new shapes that were released in this version are now available in SVG Files on GitHub
Last time I spoke both at Integrate UK and Integrate USA were in 2017, that in fact was the last time this event also happened in the USA, in which my session was on this same topic, part one of this topic: BizTalk Server Fast & Loud. And in reality, it was merely a happy coincidence, to do these two events again two years later and have decided to do the second part of this previous lecture, because by the time I chose the topic I didn’t know that I would return to the US… but it was a happy coincidence that fit very well!
And the reason I decide this second part was because I had received so much amazing feedback about my BizTalk Server Fast & Loud session two years ago. I can confess that in the beginning, I was a bit nervous to do this sequel, mainly because I want to keep the same good level of the previous one, avoiding what usually happens with the sequels in the movies, as a new part is released the quality goes down… but in the end I think I did a good job, that was confirmed by the awesome feedback provided by the attendees of both events. Personally, I love this session more than the previous one. But if you were not there two years ago, do not watch this talk without seeing the first part of this session here: BizTalk Server Fast & Loud.
About my session
Session Name: BizTalk Server Fast & Loud Part II: Optimizing BizTalk
Session Overview: Following the success of one of Sandro’s previous presentations at Integrate, Sandro decided to go deep on this hardcore BizTalk topic: Optimizing your BizTalk Server. This session will allow you to gain a better view on how to optimize BizTalk Server for better performance in all phases: your team’s proactivity, your code, your environment, and your goals. Once again, this is a very extensive and complex topic and there is no magic formula you can apply to solve all your problems. This presentation will aim to guide you through some of the most important steps, operations, tasks and best practices that you need to do or be aware of, in order to boost the performance of your BizTalk Server projects and that you can adjust or follow according to your needs.
Like previous years, the event in London is recorded, so if for any reason you could not be present at these events, or if you want to review it again, you can now do it here:
I am currently doing many hybrid integrations, leveraging the capabilities of BizTalk Server and Logic Apps to meet the needs/requirements of my clients/projects. Some of these projects began initially to be completely on-premises, but as the projects grew and new needs emerged, the need to use some cloud services appeared like Power BI, Office365 (Teams, Outlook) and so on, became a reality. And these features are much easier to implement and have more productivity on Azure Logic Apps instead of BizTalk Server as there are already native connectors there.
And you have known that in reality there are several ways to implement this two bidirectionality communication between Logic App and BizTalk Server :
Sending messages from BizTalk Server to Azure Logic Apps
and receiving messages from Azure Logic Apps to BizTalk Server
like using HTTP requests or Azure Service Bus, and by doing so you can create robust and smart hybrid integrations. You may have to use one of these alternative implementations if you are using older versions of BizTalk Server.
The good news is that BizTalk Server 2016 has a better and more user-friendly way to make these hybrid connections. Instead of using HTTP requests, with BizTalk Server 2016 you can use the Logic Apps adapter to receive messages from an Azure Logic App or send messages to an Azure Logic App.
This whitepaper will give you a detailed understanding of the following:
Requirements to install Logic App adapter
Microsoft BizTalk Server Adapter for Logic Apps
On-premises data gateway
How to install and configure the Logic App Adapter
Where I can download it
The whitepaper is completely free and you can download it here:
I would like to take this opportunity also to once again say thanks to Lex Hegt for helping me reviewing this whitepaper especially for the incredible work of him correcting my horrible English and in this way make the document and my thoughts being readable.
I hope you enjoy reading this paper and any comments or suggestions are welcome.
You can also find, download and enjoy several other free whitepapers of my own here:
This week while trying to use the BizTalk Server 2016 JSON Schema Wizard to generate an XML schema from a specific JSON file to be sent to a Logic App I got the following error:
Error in JSON Instance File.XMLNodeConverter can only convert JSON that begins with an object. Path ‘’, line 1, position 1.
Basically, I’m trying to send a list of “objects” that in my case are Locks, to be processed.
Cause
The cause of this “problem” is that a JSON array of objects, it may not make sense in a BizTalk Server XML world as Morten la Cour very well written in this forum. At that sentence, I will add: “at it his being done today”. Because with a few improvements could be smarter and support this type of messages.
Why? The BizTalk
JSON Schema Wizard is a simple and “stupid” converter, it will ask only for you
to provide a root node and a namespace that it will add to the XML Schema and
the XML JSON representation because it will require that information to be able
to uniquely identify this type of message.
But it will not understand what is a “not identified” object array because it needs to give it a Record name in the XML equivalent.
So, that means
that it will not support JSON arrays?
No, it will support JSON arrays if you provide a field name to that array, i.e., instead of having:
[
…
]
You should have:
{
"field name": [
…
]
}
Solution
If you don’t have the control over that JSON message,
you may need to create a custom pipeline component to add or remove this field
name that will identify the array in the XML equivalent message.
If you have control over the structure of the JSON
message the simple way is to modify the structure of the message to include a
field name to identify the array. Lucky it was my case, so I modify the
original structure descrived above to be:
BizTalk Documenter tool: Cover Customization Resources
![BizTalk WCF-SQL Error: Microsoft.ServiceModel.Channels.Common.MetadataException: Object [dbo].[ColumnName] of type StoredProcedure does not exist.](https://www.biztalkgurus.com/wp-content/uploads/2019/11/biztalk-server-Object-dbo-ColumnName-of-type-StoredProcedure-does-not-exist-1024x445-1-150x150.png)
![Microsoft.ServiceModel.Channels.Common.MetadataException: Object [dbo].[ColumnName] of type StoredProcedure does not exist.](https://blog.sandro-pereira.com/wp-content/uploads/2019/11/biztalk-server-Object-dbo-ColumnName-of-type-StoredProcedure-does-not-exist.png)
