Microsoft SQL Server: SQLNCLI11 – Linked Server DTC Issue

When using a Microsoft SQL Server – Linked Server between a remote database server, you may encounter an error when trying to pass transaction sets between the environments.

[Microsoft][SQL Server Native Client 11.0][SQL Server]OLE DB provider “SQLNCLI11” for linked server “REMOTEDB” returned message “The transaction manager has disabled its support for remote/network transactions.”.
[Microsoft][SQL Server Native Client 11.0][SQL Server]The operation could not be performed because OLE DB provider “SQLNCLI11” for linked server “REMOTEDB” was unable to begin a distributed transaction.

In order to rectify this issue you will want to turn on Distribution Transaction Coordinator (DTC) to allow remote connections. On Windows 2008, go into Administrative Tools > Component Services, expand the component services, computers, my computer, and distributed Transaction Coordinator. Its relatively the same process for Windows 2012 & 2012R2.

Right click on the Local DTC, and select properties. Under the Security Tab, select allow remote clients and agents, and allow inbound and outbound transactions. You will need to do this on both the local and remote databases. When you make changes to the DTC it will restart the service. Depending on your configuration you may need to reboot the server for this to take effect.

What is DTC?

The Distributed Transaction Coordinator (MSDTC) service is a component of modern versions of Microsoft Windows that is responsible for coordinating transactions that span multiple resource managers, such as databases, message queues, and file systems. MSDTC is included in Windows 2000 and later operating systems, and is also available for Windows NT 4.0. MSDTC performs the transaction coordination role for components, usually with COM and .NET
architectures. In MSDTC terminology, the director is called the transaction manager. By default, the Microsoft Distributed Transaction Coordinator (MSDTC) service is installed with Windows 2000 and onwards, and is considered to be core functionality that cannot be uninstalled.

PeopleSoft is a cross platform application, and some databases use tablespaces and some do not.  This error will commonly occur when using Microsoft SQL Server, and to fix this navigate to:

PeopleTools > Utilities > Administration > PeopleTools Options

Uncheck Platform Compatibility Mode

You should now be able to save your record without being prompted for tablespaces what are not present in your world!

I was moving one of my database servers to a new cloud, and I could not find the product key that I used to install the SQL Server install.  So I did some google action and found a bunch of folks that had little to no clue on how to get the real product code.  I found a dude named Laser on the gearbox software website, that is a genius, and this worked for both my 2008 and 2012 versions of SQL Server.

In the registry I navigated to the key:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\DTS\Setup] – DigitalProductID, for SQL Server 2012 it will be in the “110” entries, and it might not be actually under this entry, I found it several spots so if you don’t see it there look around a bit.

Now I did find the the 2012 tag was actually the actual 16 2-digit hex values I was looking for where the 2008 tag was 164 2-digit hex values.  In the case of the 164 code, you need to 53rd pair to 69th pair to get your 16 2-digit hex value.

Next you need to take that value and reverse the entry, so for example,

01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16 becomes:  16,15,14,13,12,11,10,09,08,07,06,05,04,03,02,01

Now remove the commas and take your 32 digit code and put it into a Radix 16 to 24 converter, I found a calculator to do it.

Lastly, for Microsoft codes you need to take your results and do one last conversion:

0 ==> B,  1 ==> C,  2 ==> D,  3 ==> F,  4 ==> G,  5 ==> H,  6 ==> J,  7 ==> K,  8 ==> M,

9 ==> P,  A ==> Q,  B ==> R,  C ==> T,  D ==> V,  E ==> W,  F ==> X,  G ==> Y,  H ==> 2,

I ==> 3,  J ==> 4,  K ==> 6,  L ==> 7,  M ==> 8,  N ==> 9

Yep, it sounds crazy, but it works!  To be totally honest, I have never worked with radix based number systems before, and I am not about to start, and honestly, I get freaked out by hex values in the registry, but those codes cost a lot of money, so if you are like me and either forget to write it down because that would be easy, or inherit a system that was installed by somebody that didn’t write it down, this might just save you!

I have been working on a couple of upgrades for some clients and have found that doing PeopleTools Upgrades to 8.53 from pre 8.48 that I have encountered an error while using SQL Server 2012 and the SQLNCLI11 native client during the upgrade.  The error occurs in the PTUPGCONVERT Application Engine while it is generating new Services for Integration Broker.

I reported the case to PeopleSoft but got nowhere with the problem.  After looking around with google, I found a common theme that made me try something that shouldn’t work but it did.  I changed the ODBC connection to the database to use the SQL Server 2008 Native Client SQLNCLI10.  I do not encounter this issue when I am running the upgrade on SQL Server 2008.  Once the ODBC is changed I reran the upgrade process and everything worked!

It reports a conversion error which appears to be a NULL gets converted to a long int 2097184 which it is trying to insert into a small integer field and therefore generated an error when it tries to insert the data into a table.

I have been working with a client on upgrading their PeopleSoft database from SQL Server 2000 to 2012.  Everything has gone great, except when we tried to setup a Linked Server back to their other SQL Server 2000 databases.  SQL Server 2012 uses the SQLNCLI-11 Native Client which has backwards compatibility for two release levels, so SQL Server 2008 and 2005 are supported by this client.  You can use the SQL Server 2008 Native Client SQLNCLI-10 which does support backwards compatibility to SQL Server 2005 and 2000.

When setting up the linked server using the SQLNCI-10 Native Client, it was failing with an odd error:

The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.

After a looking around I found this knowledge base document 906954 which explains that because of the SQL Server 2012 environment running on x64 and the SQL Server 2000 environment is running on x86 that there are some compatibility issues that require the SQL Server 2000 environment needs to be at SP3 or SP4 patch level.  PeopleSoft certification documents show that their support requires the SQL Server 2000 database to be at SP3a, and they do support SP4.