Reusing BizTalk Oracle Adapter DSN

I finally had the excuse to use BizTalk Server 2006 Single Sign On for application configuration data. In one of my current BizTalk projects, I’m using a long running transaction which loops through and starts up other orchestrations. When those orchestrations finish, I’m using partner bound ports to return a response. As I receive batches of responses back, I’m logging the “last update time” to an Oracle table.

Side note: I’m a believer in *not* using the database adapters (i.e. SQL Server, Oracle) for every possible database interaction. That is, for scalar queries, or simple updates, I like just using a basic utility component and ADO.NET. It’s a little less overhead and saves me from unnecessarily creating orchestration messages. Now, there’s plenty of value in using database adapters (scalability, retries, generated schemas, etc), but for quick and dirty database communication, I’ll stick with the no-adapter solution.

So, since this solution already includes Oracle database access (and thus has a System DSN set up), I wanted to try and reuse that configuration in my Utility component. The ADO.NET OracleConnection didn’t like the “DSN” connection property, so I used the OdbcConnection instead. However, I still have to enter a password for the connection string. So using the various resources for doing “SSO as a configuration store” (see here, here, and here) I’m able to store the password in SSO, and still reuse my existing DSN configuration. My final code looked like this …

//get the encrypted password from BizTalk SSO using SSOConfigHelper from BizTalk sample
string oraclePasswordString = SSOConfigHelper.Read(“ProjCode”, “passwordString”);

//create the connection string
oracleConnString = “DSN=ProjDevDb;pwd=” + oraclePasswordString + “;”;

//instantiate the connection and command
OdbcConnection oracleConn = new OdbcConnection(oracleConnString);
OdbcCommand oracleComm = new OdbcCommand(oracleQueryString, oracleConn);

Now I haven’t implemented any sort of caching, but the “oraclePasswordString” is set in the class constructor, and then I have a method that uses it for the logging function. So, I only make the call to SSO once per process.

So there you go. Reusing an existing Oracle DSN in custom code, while securely storing the password in Enterprise Single Sign On.

Technorati Tags:

Author: Richard Seroter

Richard Seroter is Director of Developer Relations and Outbound Product Management at Google Cloud. He’s also an instructor at Pluralsight, a frequent public speaker, the author of multiple books on software design and development, and a former editor plus former 12-time Microsoft MVP for cloud. As Director of Developer Relations and Outbound Product Management, Richard leads an organization of Google Cloud developer advocates, engineers, platform builders, and outbound product managers that help customers find success in their cloud journey. Richard maintains a regularly updated blog on topics of architecture and solution design and can be found on Twitter as @rseroter.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.