BizTalk WCF-SQL Adapter MSDTC Issue

This was imported from my old blog dated  July 3, 2009.

I was working on a Biztalk integration project that perform a CRUD operation using old SQL adapter (the new one is available via Microsoft Adapter Pack 2.0), everything works fine on the development environment but when we’ve tried to deploy it to test environment which have the same structure with the production server we stumble again on MSDTC issues.

To cut the story short it was too complicated and too many settings (firewall, NETBIOS etc) needs to be change since the database server is located on DMZ. The next day while I’m configuring the mySAP adapter, I found out that license has expired, since we’ve downloaded the trial version of BizTalk adapter pack 1.0. So I have to look for another setup, it so happen that for some reason I can’t find the 1.0 version that’s why I’ve installed the Adapter pack 2.0. I was a little bit surprise to see that there’s a new SQL Adapter included. So I’ve installed it and give it a try and it works fine.

Two important sql binding settings that needs to be set are:

1) useAmbientTransaction = false, to solve MSDTC issue.

2) allowIdentityInsert = true, if you’re trying to add rows to a table that has identity column.

 

SQL Server 2008 R2 Logical name truncation during backup

We’re currently upgrading  an existing solution to use SQL Server 2008R2 from SQLServer 2005, the solution autogenerates database, performs couple of operations afterwhich it will create a backup.

During the restoration process that’s where we encountered the error:

Logical file ‘’ is not part of database ‘’. Use RESTORE FILELISTONLY to list the logical file names. RESTORE DATABASE is terminating abnormally.

So we take a peek on the generated backup file and discovers that the last letter of logical filename is being truncated. It happens when the logical name of the database is renamed before creating a backup.

You can try it using this script: (it works on SQL Server 2008R2 without any updates):

 USE [master]
GO

CREATE DATABASE [Test]
GO
ALTER DATABASE [Test] SET RECOVERY SIMPLE
GO

— Rename Logical Files and Start Backup
ALTER DATABASE [Test]
MODIFY FILE
(NAME = [Test], NEWNAME=’Test_New’)
GO

ALTER DATABASE [Test]
MODIFY FILE
(NAME = Test_log, NEWNAME=’Test_New_Log’)
GO

— Backup Working Copy
BACKUP DATABASE [Test] TO  DISK = N’E:\Test.bak ‘ WITH NOFORMAT, NOINIT,  NAME = N’Test_New’, SKIP, REWIND, NOUNLOAD,  STATS = 10
GO

Message:

The file name ‘Test_New’ has been set.
The file name ‘Test_New_Log’ has been set.
13 percent processed.
22 percent processed.
31 percent processed.
40 percent processed.
53 percent processed.
62 percent processed.
71 percent processed.
80 percent processed.
94 percent processed.
Processed 176 pages for database ‘Test’, file ‘Test_Ne’ on file 2.
100 percent processed.
Processed 2 pages for database ‘Test’, file ‘Test_New_Lo’ on file 2.
BACKUP DATABASE successfully processed 178 pages in 0.027 seconds (51.486 MB/sec).

 Question is how did the QA guys missed this part,  it was working prior to SQL Server 2008R2.

A fix is released to resolve this issue.

Or with workaround:

To work around this issue, use one of the following two methods:

  • After the logical file name is modified, run the following statements to restart the database:
    ALTER DATABASE  SET OFFLINE
    ALTER DATABASE  SET ONLINE
  • When you modify the logical file name, append a dummy space character in the new logical name. For example, if you want to change the logical name to DB_modified, use the following statement to modify the logical file name:
    ALTER DATABASE  MODIFY FILE
     (NAME=N'', NEWNAME='DB_modified ') -- Append ' ' to NEWNAME

IIS 7 Access to the path ‘c:\windows\system32\inetsrv\’ is denied

If you encountered an error related to:

Access to the path ‘c:\windows\system32\inetsrv\’ is denied.

It means that the credential wherein your site or webservice is running doesn’t have permission to that folder.

Steps below describes how to fix it:

1. Browse to the directory -> %windir%\System32

2. Right Click on the inetsrv folder and select Properties

3. Click the Security Tab -> Click Advance Button

4. Select the Owner Tab and click Edit Button, Select Administrators group and Click Apply.

5. Run the command prompt as Administrator and type:

 cacls %windir%\system32\inetsrv /G :F

Ex: cacls %windir%\system32\inetsrv /G RPAULO\WCFServiceUser:F

 

BizTalk Server 2010 RPC Server unavailable

You’re probably right here because you’re trying to set up a multi-box biztalk installation (1 biztalk machine and 1 database server wherein SSO is installed) and encountered a nasty RPC Server unavailable. First thing to check is whether the MSDTC settings is correctly setup, check the guide from Sandro here .

After that is done and still there is an error it’s caused by the Windows Firewall settings. To solve this issue, follow the steps below:

1. In the  command prompt type: Regedt32.exe

With Registry Editor, you can modify the following parameters for RPC. The RPC Port key values discussed below are all located in the following key in the registry:

HKEY_LOCAL_MACHINE\Software\Microsoft\Rpc\Internet\ Key Data Type

Ports REG_MULTI_SZ

Specifies a set of IP port ranges consisting of either all the ports available from the Internet or all the ports not available from the Internet. Each string represents a single port or an inclusive set of ports. For example, a single port may be represented by 5984, and a set of ports may be represented by 5000-5100. If any entries are outside the range of 0 to 65535, or if any string cannot be interpreted, the RPC runtime treats the entire configuration as invalid.

PortsInternetAvailable REG_SZ Y or N (not case-sensitive)

If Y, the ports listed in the Ports key are all the Internet-available ports on that computer. If N, the ports listed in the Ports key are all those ports that are not Internet-available.

UseInternetPorts REG_SZ ) Y or N (not case-sensitive

Specifies the system default policy.

If Y, the processes using the default will be assigned ports from the set of Internet-available ports, as defined previously.

If N, the processes using the default will be assigned ports from the set of intranet-only ports.

Example:

  1. Add the Internet key under:

HKEY_LOCAL_MACHINE\Software\Microsoft\Rpc

  1. Under the Internet key, add the values “Ports” (MULTI_SZ), “PortsInternetAvailable” (REG_SZ), and “UseInternetPorts” (REG_SZ).In this example ports 5000 through 5100 inclusive have been arbitrarily selected to help illustrate how the new registry key can be configured. For example, the new registry key appears as follows:

Ports: REG_MULTI_SZ: 5000-5100
PortsInternetAvailable: REG_SZ: Y
UseInternetPorts: REG_SZ: Y

  1. Restart the server. All applications that use RPC dynamic port allocation use ports 5000 through 5100, inclusive. In most environments, a minimum of 100 ports should be opened, because several system services rely on these RPC ports to communicate with each other.

You should open up a range of ports above port 5000. Port numbers below 5000 may already be in use by other applications and could cause conflicts with your DCOM application(s). Furthermore, previous experience shows that a minimum of 100 ports should be opened, because several system services rely on these RPC ports to communicate with each other.

Final Step is to add a new Firewall Rule that will allow tcp connections with port range you’ve set in the registry settings.