Friday 26 July 2013

BizTalk BAM View How to attach related documents and restrict viewing to a select AD Group only.



This blog entry is all about BAM and in particularly how to attach related documents. But also as part of this blog entry I talk about BAM security in particularly how to restrict normal business users from viewing the related documents. Yet through IIS Authorisation we can allow a subset of users to view the related documents with the associated BAM activity.

Well attaching messages to a BAM activity is quite easy.

Here is the code.

First you will need to do
// Add document reference
bamDocument = msgRequest.Body;
bamDocumentName = "Inbound Request";
bamDocumentLink = System.String.Format(
                    "/BAM/BAMManagementService/BamManagementService.asmx/GetReferences?viewName={0}&activityName={1}&activityInstanceId={2}&referenceType={3}",
                    bamViewName,
                    bamActivityName,
                    bamActivityID,
                    bamDocumentName);
Microsoft.BizTalk.Bam.EventObservation.OrchestrationEventStream.AddReference(bamActivityName, bamActivityID, bamDocumentName, "Message Body",  System.DateTime.UtcNow.ToString(), bamDocument.DocumentElement.OuterXml);
Microsoft.BizTalk.Bam.EventObservation.OrchestrationEventStream.AddReference(bamActivityName, bamActivityID, "DocumentUrl", bamDocumentName,  bamDocumentLink, "");

Enabling HTTP GET on the Portal (BamManagementService.asmx web.config)

    <webServices>
        <protocols>
            <add name="HttpGet"/>
        </protocols>
    </webServices>
Now lets talk about security. As you probably already know all BAM Users can view the related documents via the BAM web portal. We now want to restrict these users from viewing the message as it may contain sensitive content. Hence we only want to allow a subset of privileged users the authorization to view these messages.

An effective easy solution is to use the ASP.NET authorisation components in web.config. As the “view message” action is the only action to issue GET verbs to the BAMManagementService.asmx as described above we can create an additional authorisation rule to only allow a subset (AD Group) to view these related documents/messages.

Lets review the web.config

<
configuration>
  <system.web>
    <authorization>
      <allow roles="Everyone" verbs="POST" />
      <allow roles="MICROANGELO\BAM MESSAGE VIEWERS" verbs="GET" />
      <deny users="*" />
    </authorization>       
  </system.web>
</configuration>

What I have done above is allow an ad-group MICROANGELO\BAM MESSAGE VIEWERS access to the message to view. All other users will not be allow to view the message.

There it is enjoy.

Saturday 15 June 2013

SQL Concurrent Upsert (Insert/Update) without duplicates and Thread Safe



This blog entry discusses how to do a concurrent and safe upsert using T-SQ. Depending on whether there is an entry in the table it will either do an insert or an update. In this example there will be the primary key which will be unique. The primary key will be used to determine the uniqueness of the record and decide accordingly to do an insert or update. This also is handy for testing because 2 inserts of the same id will fail.

Here I will discuss how to handle whether to do an update or insert for a record concurrently and thread-safe. As you are well aware there is no upsert in T-SQL. There is however in SQL 2008 and greater a merge function. I will demonstrate that as well and show them both

First for SQL 2005 and earlier versions.

The code below is run on two or more sql query windows to prove that

DECLARE @ev_id as int
DECLARE @ev_description as varchar(50)

DECLARE @UpdateFlag as bit

SET @ev_id = 1999
SET @ev_description = 'THREAD 1'


WHILE 1=1       
BEGIN
      BEGIN TRANSACTION

      SET @UpdateFlag = 0
     
      SET @ev_id = @ev_id + 1
     
      INSERT INTO [dbo].[Event]
         ([ID]
         ,[Description]
         ,[TimeStamp])          
      SELECT @ev_id, @ev_description, getUTCDate()
      WHERE NOT EXISTS (SELECT * FROM [Event] WITH (UPDLOCK, HOLDLOCK) WHERE [ID] = @ev_id)

      IF @@ROWCOUNT > 0
      BEGIN
            SET @UpdateFlag = 1
      END
      ELSE
      BEGIN
            UPDATE [MicroangeloConccurentInsert].[dbo].[Event]
                  SET [ID] = @ev_id
                        ,[Description] = @ev_description
                        ,[TimeStamp] = getUTCDate()
            WHERE ID = @ev_id

--------------------
-----START TO BE DISCUSSED
--------------------
           
IF @@ROWCOUNT = 0
            BEGIN
                  INSERT INTO [dbo].[Event]
                     ([ID]
                     ,[Description]
                     ,[TimeStamp])          
                  SELECT @ev_id, @ev_description, getUTCDate()
                  WHERE NOT EXISTS (SELECT * FROM [Event] WITH (UPDLOCK, HOLDLOCK) WHERE [ID] = @ev_id)
                  IF @@ROWCOUNT = 0
                  BEGIN
                        UPDATE [MicroangeloConccurentInsert].[dbo].[Event]
                              SET [ID] = @ev_id
                                    ,[Description] = @ev_description
                                    ,[TimeStamp] = getUTCDate()
                        WHERE ID = @ev_id
                  END
            END

--------------------
-----END TO BE DISCUSSED
--------------------

      END
     
      COMMIT TRANSACTION
     
      if @ev_id = 20100
      begin
           
            BEGIN TRANSACTION
           
            DELETE FROM [Event] WHERE [ID] BETWEEN 2000 AND 20100
            PRINT 'DELETED ROWS'
            set @ev_id = 1999

            COMMIT TRANSACTION           
      end
           
     
      PRINT @UpdateFlag      
END

To be discussed (What does that mean)

Under normal circumstances the code between the comments “to be discussed” would not be needed. But because I am doing a delete then I need to check whether to upsert again. Ideally this would be in a while loop with while rowcount = 0 try inserting and or updating.

Now for the merge equivalent.

DECLARE @ev_id as int
DECLARE @ev_description as varchar(50)

DECLARE @UpdateFlag as bit

SET @ev_id = 1999
SET @ev_description = 'THREAD 1'


WHILE 1=1       
BEGIN
      BEGIN TRANSACTION

      SET @UpdateFlag = 0
      SET @ev_id = @ev_id + 1

      MERGE [dbo].[Event] as t
            USING ( VALUES (@ev_id) )
                              as s (ev_id)
            ON    (t.[id] = s.ev_id)
            WHEN MATCHED THEN
                  UPDATE SET [Description] = @ev_description,    
                                 [TimeStamp] = getUTCDate()
            WHEN NOT MATCHED THEN
                  INSERT
                     ([ID]
                     ,[Description]
                     ,[TimeStamp])
                  VALUES
                     (@ev_id
                     ,@ev_description
                     ,getUTCDate()) ;
                    
      COMMIT TRANSACTION
     
      if @ev_id = 5100
      begin
           
            BEGIN TRANSACTION
           
            DELETE FROM [Event] WHERE [ID] BETWEEN 2000 AND 20100
            PRINT 'DELETED ROWS'
            set @ev_id = 1999

            COMMIT TRANSACTION           
      end
     
END

As you can see the merge is much simpler. The only problem here is that you don’t know if you are doing an insert or an update. The main reason why I have used the first version is because I needed to insert a different data base on whether I was inserting or updating. Otherwise for 99% of all cases the second version is a better solution

So there you have it a multi threaded concurrent insert or update (upsert) and its thread safe.

Enjoy