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
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,
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.