Category Archives: Security

Configure MSDTC for Distributed Transactions

Few days ago, inside a Software project, I had to enable and configure MSDTC (Distributed Transaction Coordinator) between 2 machines: a Web Server using NServiceBus to process messages in a transaction and a SQL Server.
I encountered some issues and I would like to detail the order of few configuration steps as well as testing methods that should be done/checked before MSDTC in fully functional.

 
Step 1: Network connectivity

Make sure both servers can ping each other by the machine name, cause MSDTC uses netBIOS to establish a connection.

Start a command prompt window and execute on both servers:

  • ping [SERVER_NAME1]
  • ping [SERVER_NAME2]

Step 2: Enable MSDTC on both servers.

There are pictures for Windows 2008 Server and Windows 2003 Server because MSDTC location is changed from one WIndows edition to another. The options to check are the same though.

  1. Open Component Services:
  2. Component Services

  3. Access DTC Properties
  4. Windows 2003 - MSDTC location

    Windows 2003 - MSDTC location

    Windows 2003 - MSDTC location

    Windows 2003 - MSDTC location

    Windows 2008 - MSDTC location

    Windows 2008 - MSDTC location

     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     

  5. Enable MSDTC according to below options selected.
    Check only the red check boxes and click Apply.
     
  6. Windows 2008 - MSDTC properties

     
     
     
     
     
     
     
     
     
     
     
     

    A warning message will be popped in to inform that the Distribution Transaction Coordinator Windows service will be started (or restarted).
    MSDTC restart

  7. Set the startup type of Distribution Transaction Coordinator Windows service to Automatic.

If you don’t have Firewalls that prohibit external access to machine’s ports than you can stop here with the MSDTC configuration.
MSDTC will function great with basic configuration (Step 1 & Step 2) done only when there is no Firewall involved in network external communication. What do you do when there is a Firewall afterwards? In most Network configurations it is mandatory for securing the external access. If you follow all the steps detailed below, you should be able to make the MSDTC work without problems. Take a look at next steps.

Step 3: Restrict MSRPC dynamic port allocation.
 
MSDTC service depends on RPC protocol which stands in front of every Windows process that uses RPC. When you deal with MSDTC, you also have to consider the RPC access points. When MSRPC protocol is left with its default setting, all depending services including MSDTC is free to use a dynamically allocated port in the port range 1024-65535. Basically, it means that each re-start of MSDTC will result in a different port number. Fortunately you can restrict this port range which means that, instead of creating a rule in Firewall that opens all the ports from 1024 – 65535 you only need to insert the range of ports restricted in RPC setting.

There is one thing to be considered though:
 
There can be up to 100 services that depend on RPC and will be affected by this change. Make it not too small… not to big. Doing a little reading on the internet I saw that 50 – 100 ports would be a minimum – maximum for RPC depending services to function, but again it depends on each machine and how many processes depend on RPC. If you want to find out which are these look at RPC service at Dependencies tab and count the active ones.

RPC_Service

Perform these steps on both machines in order to configure a different port range. The port range does not have to be the same on both machines.

  1. Open Component Services properties windows
  2. Component Services Properties

  3. Access Default Protocols tab and insert a new port range.
  4. Change Port Range

Next, in order to be able to start a Distributed Transaction through MSDTC service – both participating servers must be configured to trust each other by allowing access to the each other’s port used by MSDTC service.

Step 4: Add Firewall Inbound rules

on SERVER 1: to allow inbound requests to the port range configured.
on SERVER 2: to allow inbound requests to the port range configured.
This will enable the communication between SERVER 1 and SERVER 2 through MSDTC service.

SERVER 1 will be able to access MSDTC allocated port from SERVER 2 and SERVER 2 will be able to access MSDTC allocated port from SERVER1.

Step 5: Restart both machines

You need to restart both machines where the port range was changed in order for these modifications to take effect.

 
Step 4: Testing MSDTC connectivity
 
After you had done all of the above, you might want to test first if a Distributed Transaction can be initialized and committed. There are 2 important Microsoft troubleshooting tools that I used and worked for me: DTCping and DTCtester. I will not detail the testing steps because all of them are covered in this post at step 2 and step 4.

Trace DDL & DCL operations in SQL Server Log – fn_trace_gettable

Did you know that starting with SQL 2005 all future versions of SQL includes the default trace of all DDL or DCL modifications done on all DB objects?

I was inspired by Jonathan Kehayias blog to dig deeper and check other useful output from the default trace.

Let me remind you what are Data Definition Language (DDL) and Data Control language (DCL) statements:
 
DDL statements: are used to define the database structure or schema. Some examples:

  • CREATE – to create objects in the database
  • ALTER – alters the structure of the database
  • DROP – delete objects from the database
  • TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
  • COMMENT – add comments to the data dictionary
  • RENAME – rename an object

 
DCL statements : are used to manage users permissions. Some examples:

  • GRANT – gives user’s access privileges to database
  • REVOKE – withdraw access privileges given with the GRANT command

For example, I am pretty sure that you wanted at least once to find out if somebody with access on Production databases altered a certain Procedure (by create and drop), Deleted an Object or Created without letting you know or even by mistake? As well, granting rights to Logins just because some people can do that is a very bad habit with which  maybe you had to deal at some point. Windows Administrators or DEV team leaders can take advantage of their high rights and escalate the DBA’s  in some urgent matter.

Asking around didn’t bring you any answer right? Perhaps the person who did that doesn’t even know, or maybe it was part of a faulty deployment?

The modification may have caused some errors, or lost in permissions… At last, it is very useful to know right away if this kind of modification took place and you or your team didn’t do that.

To see what Events are logged – query the fn_trace_geteventinfo (trace_id) function by joining it with sys.trace_categories and sys.trace_events tables:

SELECT DISTINCT tge.eventid, tc.name AS CategoryName,te.name AS EventName 
FROM fn_trace_geteventinfo(1) AS tge INNER JOIN sys.trace_events AS te ON tge.eventid = te.trace_event_id 
INNER JOIN sys.trace_categories AS tc ON te.category_id = tc.category_id
ORDER BY CategoryName, EventName

If you are interested in collecting all information in the last week displaying all Created/Deleted/Altered Objects as well as db_roles or server roles assigned to new Logins not by you, than you can issue following SQL code – tested only on SQL 2008 :

SET ANSI_NULLS ON 
SET ANSI_WARNINGS ON
SET NOCOUNT ON

GO

---
IF (object_id('tempdb.dbo.#ddl_stmt') IS NOT Null)
   DROP TABLE tempdb.dbo.#ddl_stmt
 
CREATE TABLE   tempdb.dbo.#ddl_stmt
 (	
	   HostName nvarchar(250) null, 
       ApplicationName  nvarchar(250) null, 
       NTUserName  nvarchar(250) null, 
       NTDomainName  nvarchar(250) null, 
       LoginName  nvarchar(250) null, 
       SPID int null, 
       EventClass int null, 
       Name  nvarchar(250) null,
       EventSubClass int null,      
       TEXTData  nvarchar(250) null, 
       StartTime datetime, 
       ObjectName  nvarchar(250) null, 
       DatabaseName  nvarchar(250) null, 
       TargetLoginName nvarchar(250) null,
       TargetUserName nvarchar(250) null )
	
DECLARE @filename VARCHAR(255) 
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

DECLARE @starttime datetime = getdate () -7

INSERT INTO tempdb.dbo.#ddl_stmt ( [HostName]
      ,[ApplicationName]
      ,[NTUserName]
      ,[NTDomainName]
      ,[LoginName]
      ,[SPID]
      ,[EventClass]
      ,[Name]
      ,[EventSubClass]
      ,[TEXTData]
      ,[StartTime]
      ,[ObjectName]
      ,[DatabaseName]
      ,[TargetLoginName]
      ,[TargetUserName])
SELECT gt.HostName, 
       gt.ApplicationName, 
       gt.NTUserName, 
       gt.NTDomainName, 
       gt.LoginName, 
       gt.SPID, 
       gt.EventClass, 
       te.Name,
       gt.EventSubClass,      
       gt.TEXTData, 
       gt.StartTime, 
       gt.ObjectName, 
       gt.DatabaseName, 
       gt.TargetLoginName,
       gt.TargetUserName
FROM [fn_trace_gettable](@filename, DEFAULT) gt 
JOIN master.sys.trace_events te ON gt.EventClass = te.trace_event_id 
WHERE EventClass in (164, 46,47,108, 110, 152) and gt.StartTime > = @starttime
and gt.LoginName not in ('NT AUTHORITY\NETWORK SERVICE') 
ORDER BY StartTime DESC; 

select * from #ddl_stmt