Category: Uncategorized

BizTalk Server 2009, RosettaNet Accelerator, & Client Certificates

 

By default, the RosettaNet Accelerator in BizTalk Server 2009 does not support sending Client Certificates via BATRNApp/RNIFSend.aspx. To achieve this functionality, you need to download the Elemica Connectivity Pack from MSDN. The Elemica Connectivity Pack contains a modified version of the Microsoft.Solutions.BTARN.RNIFSend.dll which can be tweaked to send your Client Certificate.

You can download the Elemica Connectivity Pack from MSDN at:

http://go.microsoft.com/fwlink/?LinkId=46195

How to customize our new Microsoft.Solutions.BTARN.RNIFSend.dll

1.       Run the BTARN30 Elemica Connectivity Packe.exe to extract the files.

2.       Next, Open the RNIFSend project

3.       Sign the project with a Strong Name (if you don’t know how to do this, check this article out http://msdn.microsoft.com/en-us/library/xc31ft41.aspx)

4.       Build the project (make sure the configuration is set to Release)

5.       Open IIS, expand websites and find the BTARNApp virtual directory…

6.       Right click it then click Explore to open the physical folder in Windows Explorer (by default it is located in \Program Files (x86)\Microsoft BizTalk 2009 Accelerator for RosettaNet\RNApp)

7.       Backup your RNApp directory

8.       Next, copy the Microsoft.Solutions.BTARN.RNIFSend.dll that you built in step 4 above to the \RNApp\bin directory (when prompted to overwrite, click Yes)

9.       Open the web.config file located in the RNApp directory

10.   Add the following key/value pair to the appSettings section (if the appSettings section doesn’t exist just add it) to the bottom (right before the </configuration> element). Make sure to change the certificate path/filename to the one you want to use.

  <appSettings>

    <add key=CertificateFile

value=D:\BizTalk Client Certificate\YourPublicCert.cer />

  </appSettings>

11.   Save the web.config file but make sure your certificate is in the path you specified. Also, please make sure the certificate you selected is capable of Client Authentication. You can check that in the Certificates MMC by right clicking your certificate then looking at the Certificate Purposes list.

12.   In IIS, assign the user that is used by the Host Instance (in the BizTalk Administration Console) to the IIS App Pool associated with your BTARNApp virtual directory

13.   Restart IIS

14.   Restart the BizTalk Host Instance

15.   Test it

Turn on Tracing to debug your BTARNApp

One of the coolest tools I found that helped me was turning on tracing to see what was actually going in and out of the BTARNApp web app. To turn on tracing just add the following code snippet to the RNApp/web.config file (you don’t need to copy the configuration elements):

<configuration>

 

<system.diagnostics>

    <trace autoflush=true />

    <sources>

      <source name=System.Net>

        <listeners>

          <add name=System.Net />

        </listeners>

      </source>

      <source name=System.Net.HttpListener>

        <listeners>

          <add name=System.Net />

        </listeners>

      </source>

      <source name=System.Net.Sockets>

        <listeners>

          <add name=System.Net />

        </listeners>

      </source>

      <source name=System.Net.Cache>

        <listeners>

          <add name=System.Net />

        </listeners>

      </source>

    </sources>

    <sharedListeners>

      <add name=System.Net type=System.Diagnostics.TextWriterTraceListener initializeData=c:\traces\BTARNApp.log traceOutputOptions=DateTime />

    </sharedListeners>

    <switches>

      <add name=System.Net value=Verbose />

      <add name=System.Net.Sockets value=Verbose />

      <add name=System.Net.Cache value=Verbose />

      <add name=System.Net.HttpListener value=Verbose />

    </switches>

  </system.diagnostics>

 

</configuration>

 

 

addEventListener Error Internet Explorer

Are you sick and tired of getting that annoying debugger break in Visual Studio caused by the addeventlistener Null Object Reference exception!? Well, after tons of googling I still couldn’t find the resolution. What I noticed is that after doing Windows Updates sometimes after the PC restarts the error would stop (for a short time). For me the error was occurring on Internet Explorer 9 but it’s possible that you may experience the same symptoms on prior version of the IE.

Resolution

1.       Open Internet Explorer 9

2.       Click the Tools menu (if you don’t see it, don’t worry, just hit the ALT key on your keyboard and it will appear) then click Manage add-ons

3.       In the list of addons on the right pane, click on any add-on then press CTRL+A (this will select all your add-ons… also you may want to take a screenshot or write down which add-ons you have enabled so that you won’t forget which ones you had enabled)

4.       Next, click the Disable All button at the bottom right hand side of the screen

 

5.       Click the Close button

6.       Open Visual Studio .NET and load up the website that you know causes the addeventlistener error and vuala, no more error!

 

At this point what I did is enable each add-on one by one and test my project to try to figure out which one was causing the exception (in my case the add-on was called “Discuss”).

 

I hope this article was helpful, happy programming! J

 

Management Studio 2012 – Save Table Changes

In SQL Server 2008/2012 the default table designer behavior is to disallow you from saving changes to table that require the designer to delete and recreate your table. In SQL Server 2005 SSMS the default setting allowed you to save your change by acknowledging a message prompted. If you’d like SSMS 2008 or 2012 to behave the same way your SSMS 2005 did, just follow these simple steps:

1.    Open SQL Management Studio

2.    Click menu Tools > Options

3.    In the Options window click Designers

4.    On the right pane, uncheck the checkbox that says Prevent saving changes that require table re-creation

5.    Click the OK button

You should be all set at this point. Test it out by opening the table designer, making your changes, and saving them.

 

SQL Management Studio 2012 (SSMS) Keyboard Shortcuts

So I’ve installed SQL Server 2012 on my Windows 7 machine and have encountered an unexpected issue that drove me absolute bonkers. I make extensive use of keyboard shortcuts for comments code, showing/hiding results panes, auto formatting code, etc… In the new SQL Management Studio much to my surprise, the good old fashion CRTL+R shortcut to toggle the results pane did not work. My first instinct was to try different combinations of shortcuts like CRTL+R+R or CRTL+D+R etc… Nothing worked. So I did some googling and found the following MS article http://msdn.microsoft.com/en-us/library/ms174205.aspx which states that the Ctrl+R shortcut is suppose to work.

Luckly, after some more searching I stumbled upon this MSDN blog http://blogs.msdn.com/b/managingsql/archive/2011/07/13/enhanced-keyboard-shortcuts-in-ssms-in-denali.aspx which helped me figure out how to fix it. What I learned is that SSMS 2012 ships with two different keyboard schemes “Default” and “Visual Studio 2010 Compatible”. Even though mine was set to Default, my Ctrl+R shortcut still didn’t work. So let me cut to the chase, here’s how you fix it:

1.    Open SSMS 2012

2.    Click menu Tools > Options

3.    Expand Environment > Keyboard

4.    Select “Default” if it isn’t already and click the Reset button

5.    Click the OK button

 

At this point, you can test your shortcut and you will find that it now works as expected.

 

C# .NET & RegEx

Create a new Windows Application project in Visual Studio .NET. Open the Form Designer and add the following controls:

1. Button

a. Name: btnTestRegEx

b. Text: Test RegEx

2. TextBox

a. Name: tbxRegEx

b. Dock: Left

c. MultiLine: True

d. Text: Site Name:(?’SiteName’.*)Site Address:(?’SiteAddress’.*)Site Contact:(?’SiteContact’.*)

3. TextBox

a. Name: tbxInput

b. Dock: Left

c. MultiLine: True

d. Text:

Site Name: www.NunoSolutions.com

Site Address:

100 Main Street

MyCity, NJ 012345-1234

Site Contact: Nuno F. Pereira

4. TextBox

a. Name: tbxSiteName

b. MultiLine: False

c. Text: Site Name

5. TextBox

a. Name: tbxSiteAddress

b. MultiLine: False

c. Text: Site Address

6. TextBox

a. Name: tbxSiteContact

b. MultiLine: False

c. Text: Site Contact

You want to make sure your form design view looks something like the following:

Next, double click on the Test RegEx button to hook the Click event. You should now be in the code view for your form and VS.Net should present you with an event declaration similar to the following:

private void btnTestRegEx_Click(object sender, EventArgs e)

{

}

Let’s add the following code to the button Click event. Copy and Paste the following within the scope of btnTestRegEx_Click event. Your code should now look like the following :

private void btnTestRegEx_Click(object sender, EventArgs e)

{

string SiteName = “”, SiteAddress = “”, SiteContact = “”;

Match regExMatch = Regex.Match(textBox1.Text,

myRegExpression, RegexOptions.Singleline);

if (regExMatch.Success)

{

SiteName = regExMatch.Groups[“SiteName”].Value.Trim().ToUpper();

SiteAddress = regExMatch.Groups[“SiteAddress”].Value.Trim().ToUpper();

SiteContact = regExMatch.Groups[“SiteContact”].Value.Trim().ToUpper();

}

tbxSiteName.Text = SiteName;

tbxSiteAddress.Text = SiteAddress;

tbxSiteContact.Text = SiteContact;

}

When you run your application and click the Test RegEx button, the values should properly get parsed and set to their respective textboxes.

 

Here’s how it should look:

Force SSL in IIS7 (via URL Rewrite Module)

In order for this to work you’ll need to install the URL Rewrite module on your machine first.

Just know that after installing the module on your machine might force you to restart IIS and/or the machine itself.

Add the following to your web.config:

<system.webServer>

 

  <rewrite>

    <rules>

      <clear />

      <rule name=Redirect to https stopProcessing=true>

        <match url=(.*) />

        <conditions>

          <add input={HTTPS} pattern=off ignoreCase=true />

        </conditions>

        <action type=Redirect url=https://{HTTP_HOST}{REQUEST_URI} redirectType=Permanent />

      </rule>

    </rules>

  </rewrite>

 

</system.webServer>

 

IIS 7 AppPool NTFS Permissions

Grant Default AppPool identity NTFS permissions

1. Right click the directory you want to grant modfiy permissions to then click Properties

2. Click the Edit button

3. Click Add button

4. Click the Locations button and select the computer name (should be the first node in the treeview) then click OK

5. Enter “IIS AppPool\DefaultAppPool” in the Select User or Groups textbox then click Check Names

6. The value should automatically change to DefaultAppPool

 

Reporting Services RDL Report

The following video shows how you can create a simple RDL report, deploy it to the Report Server, and view it from the Report Manager in SQL Server 2008 Reporting Services:

Video 1 – Create a simple server-side report in Reporting Services.

Video 2 – Export a server-side RDL report from a .NET Windows Application.

Report.cs (6.76 kb) to download Report Class that’s used in video 2.

Click NunoSolutions.7z (69.20 kb) to download the sample project used in the video 2.

One more thing, make sure to update the user credentials with an user that has access to run reports.

Export SRS 2008 Report from Windows Applications

The following code was written and tested against SQL Server 2008 R2 Reporting Services. The helper classes help reduce and simplify the amount of code you’ll have to write (and remember) when exporting reports from SRS. I included the ReportExecutionService proxy class that I generated using the WSDL.exe tool. However, you can add a web reference to it using the GUI tools in VS.NET. For those of you who are interested in the command I used:

Open the Visual Studio 2010 Command Prompt (it should be located in Start>All Programs>Visual Studio 2010>Visual Studio Tools>Visual Studio Command Prompt) and enter the following:

 

·         C:\wsdl http://localhost/reportserver/reportexecution2005.asmx

 

 

Here’s what the code will look like to export a report:

NunoSolutions.Report rpt = new NunoSolutions.Report();

rpt.ReportServerUrl = http://localhost/ReportServer; // Change localhost in this path to your server’s name

rpt.ReportTitle = “My Northwind Customers”; // This value is used as the file name

rpt.ReportFormat = NunoSolutions.REPORT_FORMAT.PDF;

rpt.ReportPath = “/Northwind Reports/Northwind Customers”; // The “/Northwind Reports” part is the folder name in Report Server/Manager

// The credential set below must have access to the report server

NunoSolutions.ReportHelper.UserName = “<YourUserName>”;

NunoSolutions.ReportHelper.Password = “<YourPassword>”;

NunoSolutions.ReportHelper.Domain = string.Empty; // In corporate environments you’ll need to set this

// You can change this method declaration to support a file path input parameter.

// For demo purposes, I have a Folder Browser Dialog popup to prompt you where

// you’d like to save the report file.

NunoSolutions.ReportHelper.Export(rpt);

 

Download the sample source code here:

NunoSolutionsWindows.7z (61.39 kb)

Cool NEW Feauture in SQL Server 2005 – OUTPUT Clause

 

A new Clause was introduced in SQL Server 2005 called “OUTPUT” that enables you to do some pretty cool stuff. For example, let’s say you wanted to create an audit table and wanted it updated each time a query executes an INSERT or UPDATE statement (works with DELETE too) but you don’t want to create a Trigger in your table. This is the perfect scenario where you could use the OUTPUT clause. In the example below, I created a table named employee and an audit table named emp_audit that I’ll be using to store a copy of successfully affected row values from the employee table anytime an INSERT and/or UPDATE is executed:

 

 

CREATE TABLE employee(

      emp_id int IDENTITY(1,1),

      first_name varchar(20),

      last_name varchar(25),

      address1 varchar(50),

      Primary Key (emp_id)

);

 

CREATE TABLE emp_audit(

      emp_id int,

      first_name varchar(20),

      last_name varchar(25),

      address1 varchar(50),

      insert_date datetime

);

 

 

DECLARE @emp_id int

 

INSERT INTO employee(first_name, last_name, address1)

OUTPUT      inserted.emp_id, inserted.first_name, inserted.last_name,

            inserted.address1, getdate() INTO emp_audit

VALUES (‘bobby’, ‘jones’, ‘1234 main st’)

 

/*

The OUTPUT clause will cause the SCOPE_IDENTITY() function to return the IDENTITY value of the emp_audit table (instead of employee) if the emp_audit table has an identity seed column.

*/

SELECT @emp_id = SCOPE_IDENTITY()

 

UPDATE employee

SET first_name = ‘BOB’, address1 = ‘300 broad st’

OUTPUT      inserted.emp_id, inserted.first_name, inserted.last_name,

            inserted.address1, getdate() INTO emp_audit

WHERE emp_id = @emp_id

 

SELECT *

FROM employee

WHERE emp_id = @emp_id

 

SELECT *

FROM emp_audit

WHERE emp_id = @emp_id

 

Another reason to use the OUTPUT clause is to retrieve a list of Identities for an INSERT, UPDATE, or DELETE statement that affects one or more records (especially useful when more than one record gets affected IMO).

 

CREATE TABLE employee2

(

      emp_id int identity(1,1),

      first_name varchar(20),

      last_name varchar(25),

      address1 varchar(50),

      PRIMARY KEY (emp_id)

);

 

CREATE TABLE vendors2

(

      vendor_id int identity(1,1),

      company_name varchar(55),

      address1 varchar(50),

      Primary Key (vendor_id)

);

 

INSERT INTO employee2 VALUES(‘michael’, ‘jackson’, ‘111’)

INSERT INTO employee2 VALUES(‘jane’, ‘doe’, ‘222’)

INSERT INTO employee2 VALUES(‘johnny’, ‘bravo’, ‘333’)

 

 

— Variable Table that will store all the inserted vendor_id values

DECLARE @InsertedVendorIDs TABLE(vendor_id int)

 

INSERT INTO vendors2 (company_name, address1)

 — The line below adds the inserted IDs to a variable table and is the recommended approach by Microsoft in SQL 2005 to retrieve identities since both Scope_Identity()and @@Identity could return incorrect identity values when a parallel query plan is generated (yup, it’s a bug in SQL Server click here to view the MS KB article)

OUTPUT inserted.vendor_id INTO @InsertedVendorIDs

SELECT      first_name + ‘ ‘ + last_name AS company_name, address1

FROM employee2

EXCEPT  — <– another cool query feature that I like but isn’t new. insert only records employees2 that don’t exist in vendors2

SELECT company_name, address1

from vendors2

 

— retrieves all the inserted identity values that were created by the previous query

SELECT * FROM @InsertedVendorIDs