SQL Server: Invalid prefix or suffix characters error message

When trying to do different operations in SQL Server Management Studio such as "Edit Top 200 Rows", you might get the error message "Invalid prefix or suffix characters. (MS Visual Database Tools)".

This is most likely due to the fact that the Management Studio tool is an older version than the database you are connected to and trying to perform the operation on. For instance, using SQL Server Management Studio for SQL Server 2008R2 and connecting to a SQL Server 2012 database will render the above error message when trying to perform the "Edit Top 200 Rows" operation on a table or view.

The solution is to use the same version of Management Studio as the database in question.

Recreating a BizTalk Active Directory group will cause access to BizTalk to fail

When setting up and configuring a BizTalk environment you will also add the necessary Active Directory groups to allow access for different accounts to the platform.

In most cases, you will not touch these groups again, but there might come a time when you remove and add the groups with the same name within Active Directory. Doing so will change the SID of the group and it will no longer match the SID stored in SQL Server for the Login.

Trying to access the BizTalk Server Administration Console as a BizTalk Server Operator will for instance yield the following error when the corresponding AD group has been recreated:

You will get an error saying "BizTalk Server cannot access SQL Server. [...] Access permissions have been denied to the current user. [...] Login failed for user [...]".

You will also notice a logged error in SQL Server saying that "Login failed for user [...] Reason: Could not find a login matching the name [...]"

When looking at both the Active Directory accounts/groups as well as the SQL Server security settings, everything will look ok.

In order to correct this, you will have to remove and then add the corresponding Login in SQL Server. This might also cause you to have to delete and then add the corresponding User in the different BizTalk Databases (in general the BizTalkMgmtDb, BizTalkDTADb and BizTalkMsgBoxDb). This will also be notified by SQL Server when removing the Login.

After doing these remove/adds in SQL Server, you should be able to access the resources as expected.

Note that this is documented briefly in the Troubleshooting BizTalk Server Administration page on MSDN, but you more or less have to know where/and for what to look in order to find it.

Captain Obvious to the rescue!

The serverRuntime@appConcurrentRequestLimit setting is being exceeded.

Most likely causes: Setting is too low.
Things you can try: Try increasing the value of the setting.

Well, duh.

Leaving password blank in BizTalk 2013 SFTP adapter will cause host instance to run at 100% CPU without warning

A very interesting tidbit appeared on a server running BizTalk 2013 and using the brand new SFTP adapter that is bundled with the product.

As soon as a file was sent to an SFTP server, the host instance running the adapter would spin up to 100% CPU and stay there. The file would not be sent and no warnings or errors written to the event log.

After being tasked with trying to find out the issue behind this, I started with the normal tasks coupled with a host spinning up without any reason: checking the tracking settings, looking at the messagebox regarding rouge messages, zombies etc. Nothing could be found.

It wasn't until I tried to set up a copy of the flow on another 2013 server that I noticed that the password field in the Send Port properties was blank. Even with an entered password of "" the password field should display a bunch of stars to mask the password (or lack of one).

I set the password to the correct one, and started the host again. Now the file was sent and the host stayed at normal CPU load.

When looking into this, I noticed that when exporting the binding for the send port when the password wasn't set, the CustomProps in the TransportTypeData element never included the Password element. When setting either a blank password or a proper one, the binding will include a password placeholder of which can be translated to NULL, i.e. there is no password defined (it is never written to the binding file for security reasons). But if you never set a password, this field is never written to the binding file at all!

This is important to be aware of since I also noticed that if you try to set the password to "blank", by entering a character and then deleting it again and then saving, it is not entered since the GUI will interpret it as nothing has changed and therefore not set the password field to blank, leaving it completely undefined. Instead you have to enter a dummy character, save, enter the property dialog again and delete the character before doing the final save. That way the blank password will be properly saved.

Error when importing bindings: "Failed to update binding information."

When importing bindings into a BizTalk application, the following error message might appear:

TITLE: Import Bindings
------------------------------
Failed to update binding information. (mscorlib)
------------------------------
ADDITIONAL INFORMATION:
Cannot update send port "MoAGSendPort". (Microsoft.BizTalk.Deployment)

Cannot update transport information (address "C:\temp\SHS\ut\1.mb510i1_%SourceFileName%"). (Microsoft.BizTalk.Deployment)

The following items could not be matched up to hosts due to name and/or trust level mismatches:
Item: 'FILE' Host: 'SendHost' Trust level: 'Untrusted'
You must do one of the following:
1) Create hosts with these names and trust levels and try again
2) Re-export the MSI without the binding files and have a post import script apply a suitable binding file. (Microsoft.BizTalk.Deployment)

While the message might be correct regarding the host name or trust level, a more common reason to the failure is that the host doesn't have an adapter handler specified that matches the bindings.

In the Admin Console, browse to BizTalk Server 2009 Administration > BizTalk Group > Platform Settings > Adapters and then look at the adapter mentioned in the message. In my case it says "Item: FILE", so the File adapter is where I'm heading and it is indeed missing a send handler for the SendHost host.

To add a send handler, right-click on the adapter (or in the detail view of the window) and select New > Send Handler...

Then select the host that needed the specific send handler and click Ok.

Restart the host instance in question and then try to import the binding again.

WCF exception Could not establish trust relationship for the SSL/TLS secure channel with authority 'server:port'

I was recently working with some WCF services using the wshttp binding and therefore calling them over SSL. I had a certificate set up, but when trying to browse the wsdl in my test client, I couldn't browse the metadata. In the eventlog, I found the following error messages.

Exception Information Type[SecurityNegotiationException] Source[mscorlib] Message[Could not establish trust relationship for the SSL/TLS secure channel with authority 'server:port'.]

Exception Information Type[WebException] Source[System] Message[The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.]

Exception Information Type[AuthenticationException] Source[System] Message[The remote certificate is invalid according to the validation procedure.]

The next step was to try to browse the wsdl in Internet Explorer, where I got this:

Now it made sense. I tried to browse the service using an endpoint URL of https://localhost:11001/path when the certificate I was using was issued to the actual server name as can be seen both in Internet Explorer when checking the certificate information as well as in the MMC Certificate snap-in.

In other words, even if localhost and my servers full name can be used interchangeably in most cases, it isn't so when we are talking about security certificates where the server name is quite vital. After switching to the correct endpoint URL, in my case https://server1.domain.com:11001/path, it worked as expected.

It should be noted that there is a way of bypassing the certificate verification in the client by setting the ServerCertificateValidationCallback property as below:

using System.Net.Security;
using System.Security.Cryptography.X509Certificates;

System.Net.ServicePointManager.ServerCertificateValidationCallback += delegate(object sender, X509Certificate cert, X509Chain chain, SslPolicyErrors sslError)
{
return true;
};

This is however quite dangerous and should not be used in production code. A good practice if code as this is used is to wrap it in #if DEBUG statements to keep it from getting to production (but then you risk having all tests go through without any problems and have a hard to find error in the production environment that cannot be replicated in test).

WCF exception PlainXmlWriter+MaxSizeExceededException

When logging and tracing are switched on for a WCF service, the following exception might be thrown

A message was not logged.
Exception: System.InvalidOperationException: There was an error generating the XML document. ---> System.ServiceModel.Diagnostics.PlainXmlWriter+MaxSizeExceededException: Exception of type 'System.ServiceModel.Diagnostics.PlainXmlWriter+MaxSizeExceededException' was thrown.

The reason is that the maxSizeOfMessageToLog configuration parameter is set to a value that is lower than the size of the message that was trying to be logged.

<system.servicemodel>
<diagnostics>
<messagelogging logentiremessage="true" logmalformedmessages="true" logmessagesatservicelevel="true" logmessagesattransportlevel="true" maxmessagestolog="30000" maxSizeOfMessageToLog="200000">
</diagnostics>
</system.servicemodel>

And while on the subject, it can be worth checking out the MSDN recommended settings for tracing and message logging at http://msdn.microsoft.com/en-us/library/aa702726.aspx.

"Root element is missing" from a WCF service hosted in BizTalk

When hosting a WCF service in BizTalk, one can come across the error message "Root element is missing" when trying to browse the service metadata.

This error is due to the account running the application pool which in turn is hosting the service in IIS not having access to the BizTalk databases. Make sure that the account has access to the appropriate BizTalk databases, and the service should be fully browsable.

BizTalk configuration error: Failed to connect to the SQL database 'SSODB'

Sometimes when configuring a fresh installation of BizTalk Server, the following error might appear during the configuration of the Enterprise SSO:

Failed to connect to the SQL database 'SSODB' on SQL Server ''. (SSO) Additional information: (0x80131700) (Win32)

Most interesting is that the error is due to a failure to connect when the configuration is meant to create the database in question.

I found a solution through this blogpost which while it is for Windows Vista and a bit different, still has the same solution as for the above problem. By some reason the Enterprise SSO is not correctly installed and by registering the SQL module and run the configuration tool again, it will work:

1. Start a Visual Studio command prompt.
2. run regasm "c:\Program Files\Common Files\Enterprise Single Sign-On\SSOSQL.dll"
3. Run the BizTalk Server configuration tool again

BAM deployment error: SQL Analysis Services 2008 Enterprise Edition is not configured. Can not create OLAP cubes for RTAs.

If the BAM OLAP databases are not set up in SQL Server when deploying BAM definitions by running bm.exe deploy-all, the following error message might appear.

Deploying View... ERROR: The BAM deployment failed.
SQL Analysis Services 2008 Enterprise Edition is not configured. Can not create OLAP cubes for RTAs.

Run BizTalk Server 2009 Configuration and select BAM Tools. Make sure that Enable Analysis Services for BAM aggregations is selected and the Data stores valid.

Click Apply configuration after making the necessary changes and then run bm.exe to deploy the configuration once again.