ColdFusion Active Directory Authentication

My web server is off-site, completely disconnected from our primary network.  As we work to build our portal, we needed to figure out a way to authenticate our in-house users with a single sign-on.   Of course there are lots of LDAP/AD options out there, but that requires holes in the firewall.  Something my company cannot do.

So, after a little head scratching, I decided to try out the <cfexchangeconnection tags that come bundled with CF8.

I created the following function to auth to our AD by way of our M$ Exchange OWA... The only thing that was tricky was making sure the SSL Certificate was happy with ColdFusion.  Rather than dealing with the CACERTS file again, we just bought a well-known authority cert for our webmail server.  

Here's the tiny little function.  Give this a try.  If you want to get more info out of AD, try Boyan Kostadinov's AD/SQL Server idea.  Use the cfexchange methods to auth, and access his method by way of SQL Server 2005 webservice (or direct connection if you have it) to get a complete user profile... 

<cffunction name="authADUser" returnType="query">
<cfargument name="UserID" type="string" required="false" default="0">
<cfargument name="Passwd" type="string" required="false" default="0">

<cfset var newQueryObject = queryNew("ExchangeHost,MailBoxName") />
<cfset ExchangeHost = "webmail.yourcompany.com">
<CFTRY>

<cfexchangeConnection
 action="open"
 username="#lcase(UserID)#"
 password="#Passwd#"
 server="#ExchangeHost#"
 protocol="https"
 connection="exchangeConnection">


 <cfset queryAddRow(newQueryObject, 1) />
 <cfset querySetCell(newQueryObject, "ExchangeHost", ExchangeHost) />
 <cfset querySetCell(newQueryObject, "MailBoxName", lcase(UserID)) />


 <cfexchangeConnection
  action="close"
  connection="exchangeConnection">

 <CFCATCH>
 </CFCATCH>
</CFTRY>

<cfreturn newQueryObject>

</cffunction>

SQL Server 2005 Endpoints and ColdFusion 8

So I have spent the last month fighting with ColdFusion 8 and its irreverence for SQL Server 2005's XML/SOAP Endpoints.  Mind you, I agree with CF's point - Microsoft's implementation is wonky and *of course* unlike every open standard.  

That being said, I had to be able to get along with those IT folks in the main office, so I had no choice.  Here's what I did to make CF8 consume a SQL Server Endpoint.

The first thing is to make sure you are using a valid SSL Certificate (you can use a self-signed certificate, but it has to be imported into the java cacert file...).  Then make sure SQL Server is setup to accept Basic Auth.  Digest, NTML, and Kerberos didn't work for me.

After that basic stuff is out of the way, create your endpoint in the SQL Server... The default configuration seems to be ok.  My only recommendation is to make sure it always returns a dataset.  Anything else will likely bust the converter function described below.

You should know that you cannot use CFINVOKE.  Why?  Because CFINVOKE seems to want to use HTTP/1.o which SQL Server's webserver refuses to service.

So use a CreateObjet ("webservice"....) block instead.  Here's an example:

<cfscript>
 wsargs = structnew();
 wsargs.refreshwsdl="yes";
 wsargs.username="yourlocalaccountusername";
 wsargs.password="yourlocalaccountpassword";
  
 ws = CreateObject("webservice", "https://webservice.yourcompany.com/sql/whatever_endpoint?wsdl", wsargs);
 ws._setProperty("axis.transport.version", "1.1");
 resultSet = ws.JJData().getSqlRowSet();
</cfscript>

Of course, the secret to this is that little line "axis.transport.version", "1.1" -  that's forces Axis to communicate using HTTP/1.1.

You should be able to execute the webservice and get something back.  It won't be a query quite like you were hoping for... instead it will be proprietary DataSet-esque XML set.  Don't forget, if you want to look at what goes in and out of your query, use the GetSOAPResponse and GetSOAPRequest functions.  They basically return a parsed XML structure that ColdFusion displays nicely with CFDUMP.

To convert the dataset to a query object, I borrowed much of Joe Rinehart's code that he used in "ConvertDotNetDataSet" to create "ConvertSQLEndpointDataset"... Here's the code for it:

<cffunction name="convertSQLEndpointDataset" access="public" returnType="query" output="false" hint="takes a .Net dataset and converts it to a CF structure of queries">
 <cfargument name="dataset" required="true">

 <cfset var newQueryObject = queryNew("") />
 <cfset aDataset = dataset.get_any() />
 <cfset xSchema  = xmlParse(aDataset[2]) />
 <cfset xData  = xmlParse(aDataset[3]) />

  <cftry> 
   
   <!--- Create Queries --->
   <cfset xTables = xSchema["xsd:schema"]["xsd:element"]["xsd:complexType"]["xsd:sequence"] />
   <cfloop from="1" to="#arrayLen(xTables.xmlChildren)#" index="i">
    <cfset tableName = xTables.xmlChildren[i].xmlAttributes.name />
    <cfset xColumns = xTables.xmlChildren[i].xmlChildren[1].xmlChildren[1].xmlChildren/>
    <cfloop from="1" to="#arrayLen(xColumns)#" index="j">
     <cfset queryAddColumn(newQueryObject, xColumns[j].xmlAttributes.name, arrayNew(1)) />
    </cfloop>
   </cfloop>
  <cfcatch>
   <cfthrow message="Schema not defined in the result set.  JJ-fConvertDotNetDataset">
  </cfcatch>
  </cftry>
  
  <cftry>
   <!--- Populate Queries --->
   <cfset xRows = xData["diffgr:diffgram"]["SqlRowSet1"] />
   <cfloop from="1" to="#arrayLen(xRows.xmlChildren)#" index="i">
    <cfset thisRow = xRows.xmlChildren[i] />
    <cfset tableName = thisRow.xmlName />
    <cfset queryAddRow(newQueryObject, 1) />
    <cfloop from="1" to="#arrayLen(thisRow.xmlChildren)#" index="j">
     <cfset querySetCell(newQueryObject, thisRow.xmlChildren[j].xmlName, thisRow.xmlChildren[j].xmlText, newQueryObject.recordCount) />
    </cfloop>
   </cfloop>
  <cfcatch>
   
  </cfcatch>
  </cftry>


 <cfreturn newQueryObject>
</cffunction>

So put those two little tid-bits together and you get this:

<cfscript>
 wsargs = structnew();
 wsargs.refreshwsdl="yes";
 wsargs.username="yourlocalaccountusername";
 wsargs.password="yourlocalaccountpassword";
  
 ws = CreateObject("webservice", "https://webservice.yourcompany.com/sql/whatever_endpoint?wsdl", wsargs);
 ws._setProperty("axis.transport.version", "1.1");
 resultSet = ws.JJData().getSqlRowSet();
</cfscript>

<cfset returnedquery = convertSQLEndpointDataset(resultSet)>

Dump the "returnquery" and set "refreshWSDL='no'" and I bet you'll feel the weight of world lift from your shoulders.

Happy coding!

John J