• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

ColdFusion caching SQL Server credentials?

Guest
Mar 13, 2012 Mar 13, 2012

Copy link to clipboard

Copied

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?

TOPICS
Database access

Views

3.2K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Mar 14, 2012 Mar 14, 2012

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Mar 14, 2012 Mar 14, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Mar 14, 2012 Mar 14, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Mar 14, 2012 Mar 14, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Mar 14, 2012 Mar 14, 2012

Copy link to clipboard

Copied

cfadmin.png

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Mar 14, 2012 Mar 14, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Mar 14, 2012 Mar 14, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Mar 14, 2012 Mar 14, 2012

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Mar 14, 2012 Mar 14, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Mar 14, 2012 Mar 14, 2012

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Mar 14, 2012 Mar 14, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Mar 21, 2012 Mar 21, 2012

Copy link to clipboard

Copied

LATEST

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?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation