ColdFusion caching SQL Server credentials?

[Guest]

I have been using ColdFusion's storedProc() service for a while with no issue.  However, I have run into a scenario where I have a local stored procedure executing a remote stored procedure.  When I attempt this via ColdFusion, I receive a very cryptic error:

The DBMS has returned the command code 224...

The following is the code I am using:

variables.storedProcService = new storedProc();
variables.storedProcService.clear();
variables.storedProcService.setAttributes(datasource="DATASOURCE", procedure="STOREDPROCNAME");
variables.storedProcService.addParam(cfsqltype="CF_SQL_INTEGER", type="in", value=0);
variables.storedProcService.execute();

However, executing the exact same procedures from SQL Server Management Studio results in no issues.

Then, when I use the following code:

variables.storedProcService = new storedProc();
variables.storedProcService.clear();
variables.storedProcService.setAttributes(datasource="DATASOURCE", procedure="STOREDPROCNAME", username="USERNAME", password="PASSWORD");
variables.storedProcService.addParam(cfsqltype="CF_SQL_INTEGER", type="in", value=0);
variables.storedProcService.execute();

The execution runs without issue.  My question is this, does ColdFusion cache SQL Server/Datasource credentials?  Why does it [seem to] not use the credentials I provided in Jrun?

[Guest]

HiTopp wrote:

variables.storedProcService = new storedProc();

variables.storedProcService.clear();
variables.storedProcService.setAttributes(datasource="DATASOURCE", procedure="STOREDPROCNAME", username="USERNAME", password="PASSWORD");
variables.storedProcService.addParam(cfsqltype="CF_SQL_INTEGER", type="in", value=0);
variables.storedProcService.execute();

The execution runs without issue.

I can actually just include the password attribute and the stored procedure runs just fine.

[Owainnorth]

Quick idea - try disabling the "Maintain Connections" checkbox in the datasource tab, it could be maintaining a connection rather than creating a new one.

[Guest]

Are you talking about CFAdmin or Jrun?  Right off the bat I am not finding your referenced option in either.

[Owainnorth]

In CFAdmin go to the DSN, click "Show Advanced Settings" and it's in there. Should then create a new connection for every request.

[Guest]

This is all that I am given in the "Advanced Settings".  Right now, I am referencing developer edition, but I verified on an enterprise edition that these are the same options as well.

[Guest]

I think it may be because I am using a JNDI driver and not a SQL Server driver.

[Owainnorth]

Ah, quite possibly. Why aren't you using the native driver?

[Guest]

Honestly, I am not entirely sure.  I am not an expert in ColdFusion and the application I am supporting was using JNDI drivers before I started supporting it.

[Owainnorth]

In which case it might be worth creating a second DSN with the native driver, and use that; it may sort your issue.

[Guest]

But if the correct datasource credentials are specified in Jrun, why would ColdFusion not use those credentials?  Using those same credentials in SQL Server Management Studio and running the stored procedures works just fine.

[Owainnorth]

Honestly I have no idea. Just throwing ideas out there.

[Guest]

I kept attempting to troubleshoot the problem from a different standpoint because an associate of mine was able to run the stored procedures with no problem.  He did not have to include the credentials in the procedure call and his CFAdmin datasources used the JNDI driver.

So I compared our two ColdFusion environments and noticed that he was using the System Registry to store client variables (i.e. CFID, CFTOKEN, etc) instead of a database.  I switch my environment to use the System Registry and the two procedure calls worked just fine.  Isn't using a database for this purpose a useable replacement for the System Registry?  Why would using a database not work in this instance?