SQL Server (RSS)

SQL Server 2000 error 7391 on Windows Server 2003 SP1

Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator.

The default MS DTC configuration under Windows Server 2003 is to have network access disabled. This stops the ability to run transactions across linked servers from MS SQL Server 2000.

Microsoft refers to this error in KB329332 and has a procedure to enable MS DTC network access for Windows Server 2003, however it seems that the dialog box referred to has changed in Service Pack 1.

Another article, KB899191 explains the new functionality in the Distributed Transaction Coordinator fo Windows 2003 SP1 and Windows XP SP2. You now have the ability to enable both inbound and outbound network access as well as the authentication level. If the linked servers you are trying to access are not Windows 2003 SP1 also, the only authentication level that will work is the "No Authentication Required" option.

In summary, the steps to enable MS DTC network access in Windows 2003 are:

  1. Click Start, point to All Programs, point to Administrative Tools, and then click Component Services.
  2. In the Component Services Wizard, expand Component Services, and then double-click Computers.
  3. Right-click My Computer, and then click Properties.
  4. Click the MS DTC tab, and then click Security Configuration.
  5. In the Security Configuration dialog box, click to select the Network DTC Access check box.
  6. (Windows 2003 only) Under Network DTC Access, click Network Transactions.
    (Windows 2003 SP1 only) Under Network DTC Access, select Allow Inbound and/or Allow Outbound depending on whether the server is initiating or receiving the request. Select No Authentication Required if the linked servers are not also running Windows 2003 SP1.
  7. Make sure that DTC Logon Account is set to NT Authority\NetworkService.
  8. Click OK.
  9. In the message box, click Yes to continue.
  10. In the DTC Console Message dialog box, click OK.
  11. In the System Properties dialog box, click OK.
  12. Reboot the computer for these changes to take effect.
posted Tuesday, November 22, 2005 9:31 AM by dgadamson with 11 Comments

Darwin posts our MSFT SQL Support Case

Darwin, a Microsoft Developer Support Engineer from Sydney, Australia has started a blog here: Darwin's Idle Times and posted the resolution to our PSS Support case here: Kerberos Delegation to SQL Server.
SQL Server and Kerberos authentication seems to be a tricky issue when things go wrong. With the problem Darwin describes, we had 2 SQL/Developer consultants, a first-level MS Support Engineer as well as ourselves unable to find the fault.

Well done Darwin for sorting this out for us, and for taking the next step and blogging about it!

As Darwin describes, the following MS White paper is the ultimate guide to troubleshooting kerberos delegation: Troubleshooting Kerberos delegation in Windows 2000 and Windows Server 2003, and the way to find the offending SPN in our case was to use the following LDIFDE tool query: ldifde -f ldif.txt -j c:\ -d -l serviceprincipalname -r (serviceprincipalname=MSSQL*)

posted Friday, October 21, 2005 7:54 PM by dgadamson with 0 Comments

SQL Server error 14274

Encountered the following SQL Server error today that prevented modifying a job:
Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job was not saved.

This occurred in our instance because the server was built and SQL Server installed with a different name prior to going into production. Jobs created in SQL prior to the name change were recorded as having a different 'originating_server' to what the name of the server is now.

A quick google search revealed MS kb281642 that suggested

1. Rename the server back to the original name.
2. Script out all of the jobs and then delete them.
3. Rename the server to the new name.
4. Add back the jobs by running the script generated from step 2.

As this seemed a bit drastic under the circumstances, I took a look into msdb.sysjobs. It revealed the offending originating_server field and the pre-production server name values. Changing these to the current server name resolved the problem in this instance.

 

posted Monday, September 26, 2005 7:48 AM by dgadamson with 25 Comments