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 Outbound Product Management at Google Cloud, with a master’s degree in Engineering from the University of Colorado. He’s also an instructor at Pluralsight, the lead InfoQ.com editor for cloud computing, a frequent public speaker, the author of multiple books on software design and development, and a former 12-time Microsoft MVP for cloud. As Director of Outbound Product Management at Google Cloud, Richard leads a team focused on products and customer success for app modernization (e.g. Anthos). 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:

WordPress.com Logo

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

Google photo

You are commenting using your Google 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.