Skip navigation

Database Access

HiTopp

Currently Being Moderated

ColdFusion caching SQL Server credentials?

Mar 13, 2012 1:10 PM

Tags: #cache #coldfusion9 #stored_procedure #cfstoredproc #credentials #sql_server

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?

1021 Views 12 Replies Latest reply: HiTopp, Mar 21, 2012 6:46 AM

Replies

Currently Being Moderated

1. HiTopp,

Mar 14, 2012 7:36 AM in reply to HiTopp

Report

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.

|

Mark as:

Currently Being Moderated

2. Owain North,

Mar 14, 2012 7:38 AM in reply to HiTopp

Report

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

|
Mark as:
Currently Being Moderated

3. HiTopp,

Mar 14, 2012 8:20 AM in reply to Owain North

Report

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

|
Mark as:
Currently Being Moderated

4. Owain North,

Mar 14, 2012 8:25 AM in reply to HiTopp

Report

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

|
Mark as:
Currently Being Moderated

5. HiTopp,

Mar 14, 2012 8:32 AM in reply to Owain North

Report

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.

|
Mark as:
Currently Being Moderated

6. HiTopp,

Mar 14, 2012 8:34 AM in reply to HiTopp

Report

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

|
Mark as:
Currently Being Moderated

7. Owain North,

Mar 14, 2012 8:35 AM in reply to HiTopp

Report

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

|
Mark as:
Currently Being Moderated

8. HiTopp,

Mar 14, 2012 8:37 AM in reply to Owain North

Report

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.

|
Mark as:
Currently Being Moderated

9. Owain North,

Mar 14, 2012 8:38 AM in reply to HiTopp

Report

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

|
Mark as:
Currently Being Moderated

10. HiTopp,

Mar 14, 2012 8:45 AM in reply to Owain North

Report

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.

|
Mark as:
Currently Being Moderated

11. Owain North,

Mar 14, 2012 8:49 AM in reply to HiTopp

Report

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

|
Mark as:
Currently Being Moderated

12. HiTopp,

Mar 21, 2012 6:46 AM in reply to Owain North

Report

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?

|
Mark as:

Forum guidelines

Answers + Points = Status

10 points awarded for Correct Answers
5 points awarded for Helpful Answers
10,000+ points
1,001-10,000 points
501-1,000 points
5-500 points