SSO service failed to start for BizTalk with SQL Server Alias

For one of our customer, we have implemented an integration solution for their on-premise CRM and ERP application using BizTalk Server 2016. Now it’s ready to go LIVE and we are helping them to build PROD environment.

But in next 6 months or so they would move their current database server farm to a new one. So, we suggested them to create a SQL server alias as it will not require to re-configure BizTalk server once the database server is changed.

While configuring BizTalk server we started getting error – SSO service failed to start.

Failed to contact the SSO database: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

Data Source=SQLBIZPROD;Integrated Security=SSPI;Initial Catalog=SSODB

Error code: 0x800710D9, Unable to read from or write to the database.

 

Troubleshooting and fix

First we try to connect the database server from BizTalk server using SQL server Management studio for the SSO admin user and it connected successfully.

Next we started looking into the SQL server alias configurations. You can create aliases using SQL Server Client Network Utility. These aliases need to be implemented on each BizTalk Servers in the group.

There are two cliconfig.exe on the BizTalk server

  • C:\Windows\System32\cliconfg.exe (64bit) and
  • C:\Windows\SysWOW64\cliconfg.exe (32bit)

But the alias was only configured using the 64bit cliconfig.exe and not with 32bit.

Once we configured the alisa using 32bit cliconfig.exe, we got rid of the error and BizTalk configured successfully.

So basically the SQL server alias must be configured using both cliconfig.exe, 32bit and 64bit otherwise you might get the error – SSO service failed to start.

 

Related Links

https://blogs.msdn.microsoft.com/biztalknotes/2013/05/15/biztalk-and-sql-server-alias/

https://msdn.microsoft.com/en-us/library/aa577661.aspx

Advertisements

When to use Logic Apps vs BizTalk Server

Now a days, this is a very common and valid question in the BizTalk community, both for existing BizTalk customer and for new one too.

Here is what Tord answered in the open Q&A with product group at 100th Episode of integration Monday.  Check at ~ 30.30 minutes of the video.

If your solution need to communicate with SaaS application, Azure workloads and cloud business partners (B2B) all in cloud then you should use Azure Logic Apps, but if you are doing lot of integration with on-premise processing by communicating with on-premise LOB applications, then BizTalk is the pretty good option. You can use both if you are doing hybrid integration.

So basically, it depends on scenario to scenario based on your need and architecture of your solution.

 

Cloud Integration

Many enterprises now use a multitude of cloud-based SaaS services, and being able to integrate these services and resources can become complex. This is where the native capability of Logic Apps can help by providing connectors for most enterprise and social services and to orchestrate the business process flows graphically.

If your resources are all based in the cloud, then Logic Apps is a definite candidate to use as an integration engine.

Natively, Logic Apps provides the following key features:

Rapid development: Using the visual designer with drag and drop connectors, you design your workflows without any coding using a top-down design flow. To get started, Microsoft has many templates available in the marketplace that can be used as is, or modified to suit your requirements. There are templates available for Enterprise SaaS services, common integration patterns, Message routing, DevOps, and social media services.

Auditing: Logic Apps have built-in auditing of all management operations. Date and time when workflow process was triggered and the duration of the process. Use the trigger history of a Logic App to determine the activity status:

  • Skipped: Nothing new was found to initiate the process
  • Succeeded: The workflow process was initiated in response to data  being available
  • Failed: An error occurred due to misconfiguration of the connector

A run history is also available for every trigger event. From this information, you can determine if the workflow process succeeded, failed, cancelled, or is still running.

Role-based access control (RBAC): Using RBAC in the Azure portal, specific components of the workflow can be locked down to specific users. Custom RBAC roles are also possible if none of the built-in roles fulfills your requirements.

Microsoft managed connectors: There are several connectors available from the Azure Marketplace for both enterprise and social services, and the list is continuously growing. The development community also contributes to this growing list of available connectors as well.

Serverless scaling: Automatic and built in on any tier.

Resiliency: Logic Apps are built on top of Azure’s infrastructure, which provides a high degree of resiliency and disaster recovery.

Security: This supports OAuth2, Azure Active Directory, Cert auth and Basic auth, and IP restriction.

There are also some concerns while working with Logic Apps, shared by Microsoft IT team at INTEGRATE 2017

You can also refer the book, Robust cloud integration with Azure to understand and get started with integration in cloud.

 

Hybrid Integration

When you have, resources scattered in the cloud and on premise, then you may want to consider BizTalk as a choice for this type of hybrid integration along with Logic Apps.

BizTalk 2016 include an adapter for Logic Apps. This Logic App adapter will be used to integrate Logic Apps and BizTalk sitting on premise. Using the BizTalk 2016 Logic App adapter on-premise, resources can directly talk to a multitude of SaaS platforms available on cloud.

The days of building monolithic applications are slowly diminishing as more enterprises see the value of consuming SaaS as an alternative to investing large amounts of capex to buy Commercial Off the Self (COTS) applications. This is where Logic Apps can play a large part by integrating multiple SaaS solutions together to form a complete solution.

BizTalk Server has been around since 2000, and there have been several new products releases since then. It is a very mature platform with excellent enterprise integration capabilities.

Below is a short comparison matrix between BizTalk and Logic Apps:

Conclusion

Microsoft Integration platform has all the option for all kind of customer’s integration need.

Missing 997 for 850 PO in BizTalk EDI

In last couple of months quite often we were been reported by one of our EDI trading partner that they have not received the 997 acknowledgement message for the 850 PO message they sent us. They did not even receive MDN.

When we check the particular PO in our logging system, BizTalk admin console and SAP ERP we didn’t find it and hence we ask them to resend that particular PO. When they re-sent the same PO it got processed successfully and they received the 997 also.

But the frequency of this incident started increasing which forced us to dig deeper to get the root cause of the issue.

 

REASON:

The issue was caused only for the files which have multiple interchanges with different delimiters “ | ” and “ * ”. When they resent the PO it had only one delimiter “ * ” .

image.png

There is a custom pipeline component EDICleaner in the solution which is used in the AS2 receive pipeline.

This custom component is in place to remove any garbage characters before the start of the EDI interchange. But the logic included only for the delimiter   “ * ” and hence the PO with delimiter ” | ” was getting stripped off.

image.png

 

TROUBLESHOOTHING

It was really interesting one to crack. Here is how my team mate, Arindam nailed down the issue using the BAM tables for AS2/MDN.

So basically we asked out trading partner to provide the 850 PO file name which they had sent us. And when we queried the BAM table with the file name we got the record.

image.png

Using the time stamp from the above record, we queried the completed instances in tracking database and we got that the message have been received by the pipelines.

image.png

After debugging the same message in development environment we found the issue which was in the custom pipeline component.

 

Related link:

https://msintegrationblog.wordpress.com/2016/03/08/tracking-as2mdn-messages-in-biztalk/

Agreements and Party Settings for EDI over AS2 in BizTalk Server

Configuring BizTalk for AS2 communications can be challenging and time-consuming at times. The most complex aspect of it is dealing with certificates. In the last post I discussed about Secure messaging and Configuring certificates for AS2. In this post I will discuss about the Agreement and Party settings to handle EDI data over AS2.

EDI relies heavily on the concept of Party. Party really is a representation of a particular group within organization or partner you do business with.

EDI/AS2 settings are defined in party profiles and agreements define relationship between two parties.

image.png

 

Trading partner agreements is an understanding between two business profiles to use a specific message encoding protocol or a specific transport protocol while exchanging EDI messages with each other.

Trading partners are the entities involved in B2B (Business-to-Business) communications. When two partners establish a relationship, this is referred to as an Agreement. The agreement defined is based on the communication the two partners wish to achieve and is protocol or transport specific

image.png

In the above illustration, there’s an agreement between the “Shipping” and “Invoice” profiles of Fabrikam and Contoso respectively to use the X12 encoding for business messages (encoding agreement) and AS2 transport for exchanging messages (transport agreement). There can be many such agreements between various business profiles. For example, there can be an agreement between the “Payments” and “Invoice” profiles to use the EDIFACT message encoding standard. All such agreements for all the profiles for a pair of trading partners constitute a Partnership between the two trading partners.

In order to specify how to handle the EDI data is being sent via AS2, you will need to set up a BizTalk party and two agreements. One agreement is for the AS2 messaging (transport agreement), and one agreement is for dealing with the actual EDI data (encoding agreement).

The basic steps for setup are as follows for receiving AS2 data from a trading partner (sending data to a trading partner is very similar):

– Create a new BizTalk party with the name of the trading partner from which you will be receiving data.

image.png

–  If you will be sending a 997 to the trading partner, specify the send port that the 997 will be sent out on.

image.png

–  Create a new agreement on this party that will handle AS2 messaging. Let’s give it a name as Agreement_AS2

    • On the General tab, set the Protocol property to AS2, the First Party to your home organization, and the Second Party to the trading partner. Once you’ve set the General tab this way, two additional tabs will appear: one for inbound data from the trading partner, and one for outbound data to the trading partner. If you are just receiving data from the trading partner and returning an MDN, you only need to configure the inbound trading partner tab.

image.png

– On the inbound trading partner tab, on the Identifiers tab, set the AS2-From and AS2-To properties to the appropriate values as defined in your trading partner agreement.

image.png

– On the Validation tab for the AS2 agreement, you can set the appropriate values for validation of the data. For example, if you are receiving a signed and encrypted inbound post from a trading partner, then you would set the properties.

image.png

– On the Acknowledgement tab of the AS2 agreement, you can set the properties that pertain to the MDN response back to the trading partner

image.png

The MDN is the acknowledgement for AS2 posts. There are two possible methods for postback of an MDN: synchronous and asynchronous.

The synchronous response is  posted back via the same open HTTP connection that the original document came in on, and does not require any additional BizTalk Components. Simply set the Request MDN checkbox in the BizTalk agreement, and it will automatically post back.

For asynchronous MDNs, a send port must be created.There are some additional properties that will likely need to be set or adjusted on the other tabs; a few of the most common are noted here.

  • On the Receiver MDN Settings tab, enable the Sign Requested MDN setting if you want to always send an MDN, regardless of what is noted on the inbound AS2 request from the trading partner.
  • In the HTTP Settings for Messages, enable everything except for the Ignore SSL Certificate Name Mismatch property.
  • In the HTTP Settings for MDN, enable everything except for the Unfold HTTP Headers property.
  • On the Signature Certificate tab, set the certificate that you want to use for signing the outbound MDN. If nothing is selected here, the default certificate for the BizTalk group will be used.

 

Create a new agreement for the EDI data that will be consumed. Setting this up will depend on the specifics of the EDI document type(s) that are being received over AS2. What is important to know here is that you must have this additional agreement in place so that BizTalk knows how to process the EDI data once the AS2 agreement has successfully completed the data transfer.

 

Testing your AS2 Configuration

As mentioned by Mark Beckner in his EDI book, that one of the most challenging (and frustrating) aspects of AS2 configuration is the actual trading partner testing. The best advice is to plan to set up your AS2 configuration in stages. Try to exchange plain text data (unencrypted and unsigned) first before dealing with the various settings requiring certificates. If you can get the plain, unencoded data to flow (and the MDN to return) successfully, then you can move into testing encryption and signing.

There are many things that can go wrong during testing, and the error messages are often very generic and cryptic. The errors could be on your side, or they could be on the trading partner’s side. The more you can do to limit what is being tested at any given stage, the quicker you will be able to get to resolution and completion.

 

Resources:

BizTalk 2013 EDI for Supply Chain Management: Working with Invoices, Purchase Orders and Related Document Types

Advanced BizTalk Server 2010

Related Links:

https://msdn.microsoft.com/en-us/library/bb245935.aspx

https://msdn.microsoft.com/en-us/library/ee920490.aspx

http://www.synegrate.com/#!BizTalk-Server-Troubleshooting-SSLTLS-Handshake-failures/c1dlv/56afbf3b0cf2fb0f6fe7e5a1

 

AS2 setup to exchange EDI messages using BizTalk Server

AS2 is one of the most popular methods for transporting data, especially EDI data, securely and reliably over the Internet.

  • Security is achieved by using digital certificates and encryption
  • AS2 messages are always sent using the HTTP or HTTPS protocol
  • Messages may request a Message Disposition Notification (MDN) back if all went well. Upon the receipt of the message and its successful decryption a “success” MDN will be sent back to the original sender.

Trading partner agreements play a key role in AS2 support in BizTalk Server. Most configuration and administrative functions related to AS2 processing in BizTalk Server are performed by configuring the trading partner agreements between business profiles.

You can specify the AS2 properties as part of the “transport protocol settings” for a business profile or by directly specifying the AS2 settings in the trading partner agreement.

image.png

 

Secure messaging

Let’s say trading partner A want to send EDI message to trading partner B. Following must need to be taken care

  • Authentication: B authenticates A before accepting its message
  • Data Privacy: intruders must not be able to understand the message
  • Data Accuracy (Integrity): a modified message must be detected by System B

Secure messaging of EDI data is achieved using public and private keys. Organization (Trading Partner) generates keys; distributes the public and keeps the private secret. Data encrypted by public key can only be decrypted by private key.

So in our scenario A wants to send private data to B

  • B generates the public/private key pair
  • B gives public key to A and keeps the private key secret.
  • A uses B’s public key to encrypt a message
  • Only B can decrypt the message using its private key

 

Digital Certificates and Certificate Authorities

Digital certificates are just electronic documents that contains public key. These certificates are signed by a trusted Certificate Authority (CA) and the signature binds owner’s identity to the public key.

Again back to our scenario: Trading partner A wants to send encrypted message to B

  • B must send A its public key to encrypt the message
  • Evil system / entity Z wants to trick A into thinking it’s B
  • Z generates a private/public pair. And sends public key to A
  • How can A tell that it’s being modified by some other entity / system?

Here comes the role of a CA because CA only certifies that public key is owned by a certain entity

  • B asks a CA to sign its public key
  • CA verifies B and generates a certificate holding B’s information and public key – CA signs certificate with its own private key
  • A receives certificate from B
  • A verifies it trusts CA’s by validating its signature
  • A now is sure the certificate belongs to B

How does A verify CA’s signature?

When A install the certificate on its server it can see the CA details

image.png

 

Configuring certificates for AS2

Windows has several different certificate stores. Using certmgr.msc allows a certificate to be installed for the current user.

However, to make a certificate available to services and other process that run under the Local System or Local Service accounts, you must import the certificate into the Local Computer store.

To import the certificate, set up a connection the local computer’s certificate store:

  • Start -> Run: mmc.exe
  • Menu: File -> Add/Remove Snap-in…
  • Under Available snap-ins, select Certificates and press Add>.
  • Select Computer Account for the certificates to manage. Press Next.
  • Select Local Computer and press Finish.
  • Press OK to return to the management console.

Import External Trading Partner Public Certificate:

  • Select: Console Root -> Certificates (Local Computer)
  • Continue and select: Trusted Root Certification Authorities -> Certificates.
  • Right click on Certificates and select All Tasks -> Import…

image.png

Also install the Trading Partner Public Certificate in  Other People. Right-click Other People, point to All Tasks, and then click Import.

OtherPeople

 

Generate the Private certificate on the server. Alternatively, a certificate can be purchased from VeriSign, DigiCert or other providers so that the CA Root Authority is more standard and available when dealing with outside Trading Partners. Especially when Servers are not exposed to the Internet.

Install the Private Key on the BizTalk Server Certificate Store under Personal

image.png

Generate a Public key and send this off to the External Trading Partners.

In the BizTalk Administration Console, right-click the BizTalk Group and select properties. Click the Certificate option and select Browse. Select the Private certificate for your home organization. This will be your primary certificate used to sign outbound data.

image.png

Note: In some scenarios,  for specific parties this default private certificate can be overridden.  This can be done in Certificate page of the AS2 properties for your trading partner.

AS2Certificate

Select Trading Partner’s Public Certificate under Send Port

image.png

You  also need to check the Schannel SSP registry entries for TLS/SSL Settings. You might face SSL/TLS handshake failures while sending message to the trading partner. Please refer the excellent post by Riaan for troubleshooting  SSL/TLS handshake failures.

 

IIS and the BizTalk HTTP Receive Location

AS2 is communication over HTTP, so you need to set up a site within IIS on the BizTalk server. The most common is to create a virtual directory for a specific trading partner that maps inbound requests to the BTSHTTPReceive.dll

Configure an HTTP Receive Handler  and IIS for an HTTP Receive Location to receive the EDI message from trading partners.

Handler Mapping

HandlerMapping

AS2 Web site set up

AS2BizTalk

This should complete the first part of AS2 setup for exchanging the EDI message with Trading Partner. Next part would be Agreements and Party Settings 

Related Links:

https://msdn.microsoft.com/en-us/library/bb226506.aspx

https://msdn.microsoft.com/en-us/library/bb728096.aspx

http://biztalk-dish.blogspot.com/2012/07/guidance-on-edi-over-as2-in-biztalk.html

SAP .NET Connector(NCo) in BizTalk Server

SAP .NET Connector 3.0 is the current version of SAP’s development environment for communication between the Microsoft .NET platform and SAP systems. SAP has already announced deprecation of its classic RFC SDK and they will stop supporting it after March 2016.

So BizTalk server product group has added support for the SAP .NET Connector in WCF–SAP adapter. And they have released it with cumulative update 2 for BizTalk 2013 R2.

Currently the BizTalk WCF-SAP adapter will support both the RFC SDK and the SAP .NET Connector (NCo) through the ConnectorType property within the WCF-SAP binding.

Connector type value can be ‘ClassicRfc’ and ‘NCo’ as explained in the whitepaper released on the Codit website.

image.png

 

In order to use the SAP .NET Connector, you must install both

 

Architecture wise it is the re-engineering of Microsoft BizTalk Adapter for mySAP Business Suite to replace Classic RFC SDK from new NetWeaver RFC Library.

image.png

Conclusion

So in conclusion Microsoft has provided the cumulative update / hotfix for WCF –SAP adapter which can be used to make the transition to the new SAP Connector for .NET as smooth as possible.

Both the WCF-SAP and WCF-Custom adapters are updated with a new property that allows you to choose between the Classic RFC and the new Connector for .NET libraries. Please refer the whitepaper for further details.

Resource:

http://www.codit.eu/blog/2016/01/04/microsoft-adds-support-for-sap-net-connector-in-biztalk-server-2013-r2/?utm_source=twitter&utm_medium=post&utm_campaign=biztalk2013r2cu2

http://www.integrationusergroup.com/hybrid-integration-with-sap/

Consuming IDOCs from SAP using BizTalk Server – Part 3

In the previous two post I discussed about the overview of SAP , how do we generate schema and configure ports in BizTalk to consume the IDOC. In this post I will discuss about the RFCs / BAPIs overview and setting need to be done on BizTalk server to consume them.

RFCs

RFC stands for Remote Function call and is the standard SAP interface when exchanging data across SAP systems or between non-SAP systems and SAP systems.

RFC is a SAP protocol to handle communications, and is used to call functions in an SAP system by a caller external to SAP or to call programs external to SAP from an SAP system.

It is the process of calling a function module which is residing in a different machine from the caller program. RFCs can be used to call a different program in the same machine as well, but usually it is used when ‘calling‘ and ‘called‘ function modules/ programs are running on separate machines.

image.png

Functions can only be called via RFC, if they are tagged as RFC functions in the SAP development workbench.

BAPIs

BAPI stands for Business Application Programming Interfaces and are basically a RFC-enabled function module. It is a library of functions that are released to the public as an interface into an existing SAP system from an external system.

So a BAPI function is a function module that can be called remotely using the RFC technology. A function module is a logical grouping of domain specific functions that belong together. For instance, we may have a Human Resources (HR) function module that will contain all available HR operations.

 

RFCs/BAPI vs. IDOCs

BAPIs, RFCs and IDOCs are often confused. A question comes up regularly is when to use what?

category

  • BAPI is a business object.
  • An RFC is functional code.
  • IDOCs OR intermediate documents are standard data structures for electronic data interchange (EDI) between application programs.

IDOCs are used for asynchronous transactions: each IDOC generated exists as a self-contained text file that can then be transmitted to the requesting workstation without connecting to the central database.

IDOC types define different categories of data, such as purchase orders or invoices, which may then be broken down into more specific categories called message types. Greater specificity means that an IDOC type is capable of storing only the data required for a particular transaction, which increases efficiency and decreases resource demands.

Synchronous scenarios are better suited to leveraging RFCs/BAPIs due to their immediate request/response mechanisms.

For example, take the situation where SAP is the system of record for customers within your organization, but you have chosen to use Dynamics CRM for a specific application. We do not want to manually key in customer information in both systems if we don’t have to. So we can build an interface that will query SAP to retrieve the master data for an existing customer in our organization.

In this situation, we would not want to use an IDOC, if we are expecting an immediate response, as BizTalk would send the IDOC in and then have to wait for a response from SAP asynchronously. When SAP does send the response back to BizTalk, we may have to correlate this response with the request that was sent into SAP so that BizTalk can provide a correct response to the calling application.

 

Setting BizTalk server to use RFCs/BAPIs

Configure the saprfc.ini file

The first thing is to create a System Variable called RFC_INI.  We then need to provide a path and a filename.  For example I have used C:\SAPINI\saprfc.ini.

image.png

Next we need to add the contents to our saprfc.ini file. The values that I needed to provide include:

image.png

image.png

Now that we have our SAPRFC.ini file and if you have also created SAP Receive location then we can run a connected client test using SM59.

image.png

Launch the SM59 transaction and then expand the TCP/IP Connections node.

image.png

Scroll through the list to find your RFC Destination

image.png

Click on the Connection Test button to execute the test.

image.png

Connection test will fail if something is wrong in SAPRFC.ini file or receive location for receiving the IDOCs is not running.

image.png

Once you have a proper SAPRFC.ini file and receive location for receiving the IDOCs is running, the test connection should pass.

image.png

With connectivity issues out of the way, you can browse for your desired schemas using the BizTalk Consume Adapter Service Wizard and then add them to our BizTalk solution. Please refer the part1 and part2 for schema generation and port configuration in BizTalk.

Resource:

Microsoft BizTalk 2010: Line of Business Systems Integration

http://www.integrationusergroup.com/hybrid-integration-with-sap/

Related links:

http://onlyabap.blogspot.com/2007/08/what-is-difference-between-bapiidoc-and.html

http://kentweare.blogspot.com/2012/03/sap-calling-rfc-hosted-in-biztalk.html