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: BizTalk