Calling secured WCF from VBA

Using Azure ACS secured WCF in VBA

Another day and another weird requirement of using one of our secured WCF service from VBA. Now, I did write a lot of VBA code before but never a WCF service and that too which is secured.

Right, so the requirement is we have a service which is secured via Azure Access Control service and can only be called when detects a proper SWT token or BasicAuth credentials in request header. And we wish to use this service in excel.

There are couple of way by which you can call a WCF service in VBA but it is not straight forward and when you have to add secured header to your request, believe me it is not at all easy. So instead of testing the limits of VBA, I decided to keep my logic in something I know like C# and we know already that you can use C# Dlls in VBA.

Step 1: Create a C# Dll which has the logic to call the service and return the response.

In Visual Studio, Create a new class library project and name it as you want the name of your DLL.

You have to take care of below things in order to use this DLL properly in VBA:-

1. If you have custom request and response objects for your service then expose those in this DLL so that they can be used in VBA
2. WCF settings in configuration file
3. Expose project for COM interop

Now, first one is easy, make your request/response as public so they can be used in VBA. The problem is second one. Your endpoint and behaviour configuration for a WCF usually goes either in Web.config or app.config and you can't have any of them in VBA. So, you would have to hard code the binding settings and endpoint in the class library itself unfortunately.

Below is what I did in order to call the service.

            request = new ProviderApi.SubmitInvoiceRequest() { Invoice = new Invoice() };

            var username = "userName";

            var password = "passWord";

            var headerBytes = Encoding.ASCII.GetBytes(username + ":" + password);

            var headerCredentials = "Basic " + Convert.ToBase64String(headerBytes);

            var header = new MessageHeader<string>(headerCredentials);

            var untyped = header.GetUntypedHeader("Authorization", "http://schemas.bupa.com/ukmu/security/claims/authorization");

            BasicHttpBinding binding = new BasicHttpBinding();

            binding.Security = new BasicHttpSecurity();

            binding.Security.Mode = BasicHttpSecurityMode.Transport;

            var invoiceClient = new ProviderApi.InvoiceServiceClient(binding, new EndpointAddress("https://providerapitest.bhwcloud.com/InvoiceService.svc"));

            using (var contextScope = new OperationContextScope(invoiceClient.InnerChannel))

            {

                OperationContext.Current.OutgoingMessageHeaders.Add(untyped);

                var response = invoiceClient.SubmitInvoice(request);

                return response.InvoiceSubmissionReference;

            }

In short, green is where I created a header value and blue is hard coding the binding and endpoint in the client call itself. This takes away the need of having the configuration in web or app config file and when this code will be called from VBA, it will not give you behaviour or endpoint not found exception. 

And finally, changing the properties of your project to make it COM interop. Below are the two places where you have to do this. Right click on project and click properties and under build, check the box which says Register for Com interop as below:-

and then click Application and the under Assembly Information, click check box Make Assembly Com visible as show below:-

And save the project and rebuild. Go to bin folder and you shall now see for different format of files there as show below:-

A C# DLL, one config file with same project name, one visual studio pdb file and the last one which we need to use is .tlb file. We are going to reference this .tlb file in VBA and use all the code we have written in the class library project.

Step 2: Use the .tlb in VBA and call our service

In VBA, reference the .tlb as show below:-

Click Tools -> References and in the dialog opened, browse and select the .tlb file. Make sure before pressing the OK button, your DLL is checked. 

Now you have the reference added, create the required objects like request object and the object of the class where you written the method and call the method with the request object. Mine is shown below:-

Sub Button3_Click()
 Dim iService As Bupa_ProviderApi_Caller.Bupa_ProviderApi_Caller_InvoiceService
 Set iService = New Bupa_ProviderApi_Caller_InvoiceService <Method class object>

 Dim request As Bupa_ProviderApi_Caller.SubmitInvoiceRequest 
 Set request = New Bupa_ProviderApi_Caller.SubmitInvoiceRequest <Request object>
 Set request.Invoice = New Bupa_ProviderApi_Caller.Invoice

 response = iService.CreateInvoice(request) <Calling the method with request>
 MsgBox (response)

End Sub

And there you go, got the response from the service in VBA without writing anything unique which I didn't know already. The only downside of this would be, you need separate DLLs for calling test and production environment as the binding and endpoint settings are hardcoded in DLL. If you find anyway to config out that value then do let me know as well. Have fun !!!