Did you know you could verify DSNs with ColdFusion code?

This post is more than 2 years old.

We are slowly wrapping lunch here at CFUnited Express, and the guy next to me brought up the issue of handling a database that is down. He was considering running a query wrapped in try/catch on every request, but that would be a bit slow. I did a quick check and was pleasantly surprised to see that DSN verification (the same thing you get in the ColdFusion Administrator) is available via the Admin API. Specifically, datasource.cfc, method verifyDSN. You can have it optionally return a message with error details if things go wrong. Here is a quick sample:

<cfscript> adminObj = createObject("component","cfide.adminapi.administrator"); adminObj.login("admin");

datasource = createObject("component", "cfide.adminapi.datasource"); res = datasource.verifyDSN("peanutbutterjellytime", true); </cfscript>

<cfdump var="#res#">

Obviously the admin password would be different in production, but you get the idea. The result was:

coldfusion.sql.DataSourceFactory$DataSourceException: Datasource peanutbutterjellytime could not be found.

Setting the second argument to false makes the method just return a simple false.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Louis Muloka posted on 4/15/2009 at 9:23 PM

This is really cool / helpful. Thanks!

Comment 2 by Edward Beckett posted on 4/15/2009 at 9:24 PM

I tried it on my dev box and when invoking the admin login ...

"adminObj.login("admin");"

I'm getting

ErrorCode: CFACCESSDENIED
Message: The current user is not authorized to invoke this method.

Comment 3 by Raymond Camden posted on 4/15/2009 at 9:26 PM

Is 'admin' your pssword?

Comment 4 by Edward Beckett posted on 4/15/2009 at 9:42 PM

Uhhh ...

Yeah ...

Sure ...

Hey ... It Works Now!

;)

Comment 5 by Rick Hellewell posted on 4/15/2009 at 9:54 PM

Here's a challenge for you (which you may have already done; haven't done a search yet).

I am creating a new server with CF8 and moving content from CFMX. About 20 databases defined in CFMX.

Assuming the same folder/file structure on both servers, is there a quick way to get the databases defined in CF8 -- an 'export' of some type of the data sources from the CFMX server and then an import into CF8? That would save a lot of time in setting up the new server.

Probably related to that is a way to document data sources into a text file.

Comment 6 by Raymond Camden posted on 4/15/2009 at 9:55 PM

CARS. Feature built into CF since CFMX. Allows you to backup/restore settings. Not sure if it will work from 6 to 8, but you can try.

Comment 7 by Rick Hellewell posted on 4/15/2009 at 10:02 PM

CARS sounds interesting, but down at the bottom of the CARS screen when you create a new one: "ODBC Datasources cannot be included in J2EE archives". And since the 'developers' (the quotes are intentional....) around here use Access databases, CARS won't work.

Comment 8 by Raymond Camden posted on 4/15/2009 at 10:10 PM

Ouch. Guess you are out of luck. Sorry. I've heard you can copy the XML file that contains the dns info, but... that kinda concerns me.

Comment 9 by Barney posted on 4/15/2009 at 10:15 PM

Verification just runs a query, so not sure that there's any difference between using that method (which requires the admin password) and try..catch with a SELECT 1 query in it (which anyone can do).

I believe that some of the driver config screens actually let you specify the SQL used for the verification query, but I might be remembering incorrectly.

Comment 10 by Raymond Camden posted on 4/15/2009 at 10:19 PM

Interesting. Yep, I see verification sql in advanced settings, although it is blank. I wonder what SQL it uses then. Perhaps some default 'get tables' type thing that is guaranteed to work in the db being used.

Well, still, good tip I think. You know it will always work no matter what tables you end up in there. :)

Comment 11 by Andy Sandefer posted on 4/15/2009 at 10:25 PM

@Ray
Thanks for this one - a cool and quick trick!

Comment 12 by shag posted on 4/16/2009 at 12:16 AM

I'm sure it's making some sort of meta request that is specific to the DB running rather than if the table space is actually available. Our DBA takes our table space offline to do cold backups in our dev environment, so even though the db was alive, the app would bomb when trying to write data to the table.

In our java app, we run a select * from tablename where 1=0 which actually hits the table, but returns nothing without any cost. You can throw some error handling in there, but we felt this was more graceful code wise.

A long story short, I would recommend using a query that hits a table intended for the app instead.

Comment 13 by JC posted on 4/16/2009 at 1:44 AM

I'm trying to think of where this would be useful at the same time that you'd actually have an admin password.

I could totally see using it with a nice friendly installer script of some kind, testing to see if the DSN is in place before attempting to use it... but that probably wouldn't have the admin password.

Maybe if you were creating DSNs on the fly? I know I was working on a script the other day to basically automate a fresh CF install to meet our security standards... turning off stuff we don't use, requiring username & password, setting complex password for admin, creating individual users for all the developers, creating DSNs and mappings and so on... I suppose I could use something like that after the case to test & see if the DSN was successfully added.

Comment 14 by JC posted on 4/16/2009 at 1:45 AM

...and then I reread the first line and realize you're not really testing to see if it exists, but how to handle it if it's currently *down* for some reason... and things make more sense.

Ignore me. No coffee today. :)

Comment 15 by Miles Jordan posted on 4/16/2009 at 4:42 AM

Though it probably uses the same underlying methods, you can use the java class to get the same result:

dsService = CreateObject("java", "coldfusion.server.ServiceFactory").DataSourceService;
res = dsService.verifyDatasource("vegemiteisgoodalso");

Comment 16 by Robert Burns posted on 4/16/2009 at 6:54 AM

Been using this for awhile...surprised you don't need the admin password for it.

<cfset dsService = CreateObject("java", "coldfusion.server.ServiceFactory").DataSourceService />
<cfset dataSources = dsService.getDataSources() />

<cfloop list="#structKeyList(dataSources)#" index="ds">
<cfset datasource = dataSources[ds] />
<cfoutput>
#datasource.name# : #datasource.username# (#datasource.password#)<br />
</cfoutput>
</cfloop>

Comment 17 by Miguel Ulloa posted on 4/16/2009 at 5:57 PM

I was wondering if there is equivalent code for Railo. I looked around and all that may be possible is to instatiate "com.microsoft.jdbc.sqlserver.SQLServerDriver". I may be totally wrong, but has anybody ported this code to Railo?

Comment 18 by Louis Muloka posted on 4/16/2009 at 6:26 PM

I'm curious if there's any way to pass in an encrypted password into the adminObj.login() method instead of it being simply plain text.

When I do a cfdump var="#adminObj.login#" it returns the list of arguments that the method takes, and one of them is salt.

Does anyone have any ideas?

Comment 19 by JC posted on 4/16/2009 at 8:09 PM

well, there's always #decrypt(MyEncryptedPassword,crypto.key,crypto.method,"hex",crypto.ivector)#

At least then you don't have a password sitting in your code.

Comment 20 by Adam Bellas posted on 4/17/2009 at 6:15 AM

I'm looking forward to seeing how I can wrangle this verifyDSN() into a monitoring solution of sorts. Putting this code somewhere that gets hit by the CF scheduler every few minutes might be a halfway-decent way of setting a flag in the server scope, which in turn tells all the applications running on the server "don't bother trying that DSN!"

Thanks again for your help on this, it really got my wheels turning!

Cheers,
"The Guy Who Was Next To You"

Comment 21 by Jon Woodroof posted on 5/2/2013 at 9:49 PM

Can anyone tell me what my problem is?
I can call/use createObject(), but I'm not able to call VerifyDSN() or setMSAccessUnicode(). I get the error "The current user is not authorized to invoke this method." I'm using ColdFusion 8. It works on my local machine, but not on my server

<cfset datasourceService = createObject("component", "CFIDE.adminapi.datasource")>
<cfif datasourceService.VerifyDSN(datasource_name) eq false>

Comment 22 by Raymond Camden posted on 5/2/2013 at 9:52 PM

I assume you did the login portion first, right?

Comment 23 by Thomas Grobicki posted on 5/9/2013 at 11:19 PM

Previously working code has stopped working when using the cfide "datasource" feature with the same error about "user not authorized". I suspect one of the recent security hotfixes has broken something. I tried several different variations including creating a users specifically for API calls, also adding the "admin" username but nothing helped. I think this is just a bug that was introduced in the hotfix.

Comment 24 by Raymond Camden posted on 5/10/2013 at 7:22 AM

I'd encourage you to file a bug report if you can.

Comment 25 by Martin Parry posted on 7/6/2013 at 10:17 AM

I was experiencing the "The current user is not authorized to invoke this method" error this morning and have spent an hour or so to finally find out what caused it to not work in my case.

I use a common code set which includes the Application.cfc from a CF Mapping - So, in the application.cfc in the actual website I do this:-

<cfinclude template="/UberDirectory/Application.cfc">

Then, in the /UberDirectory/Application.cfc, I was initialising a CFC which checks if the datasource was created for the website. The datasource checking code attempts to log into the Admin API and check & create if necessary the datasource.

This has previously worked without fail for me - But in this instance it failed!! I was doing two things wrong - Firstly, the CFC should only be called in the Application.cfc in the onRequestStart section as the Application had to be initialised first - This is maybe because I've invoked the application.cfc in a "non-standard" manner.

Secondly, once I'd moved the CFC invocation into oNRequestStart I saw the following error:-
The string COOKIE.CFAUTHORIZATION_uber-directory is not a valid ColdFusion variable name.

I had this as the app name .... <cfset this.name = 'uber-directory'>

Changedthe dash to an underscore and I was away and could once again check the datasources

Hope it helps

Martin

Comment 26 by Raymond Camden posted on 7/6/2013 at 7:17 PM

Thanks for sharing that, Martin.

Comment 27 by Martin Parry posted on 7/7/2013 at 6:18 AM

No probs