Cannot use linked server in CFQuery in ColdFusion 2018

New Here ,
Mar 31, 2021 Mar 31, 2021

Copy link to clipboard

Copied

I am using a linked server in the CFQUERY.

The data source is type Microsoft SQL Server.

 

The error page says “access to the remote server is denied because no login-mapping exists”.

Worked OK in ColdFusion 10 on SQL 2012, but has this error in ColdFusion 2018 on SQL 2017.

The linked server on this new SQL 2017 server should match the one on the SQL 2012 server.

 

I created a view that uses the linked server, and then put that in the CFQuery.  Got the same error.

Is there a way to fix or work around this?

TOPICS
Connector, Database access, Server administration

Views

86

Likes

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

correct answers 1 Correct Answer

Adobe Community Professional , Mar 31, 2021 Mar 31, 2021
Paul, I realize you may feel this seems a problem due to the change of your cf version, but perhaps it's not. First, you note also that your sql version  changed, and while you feel that the new SQL 2017 setup is "should match" the one for SQL 2012, perhaps it does not...and especially regarding linked server setup. For example, see these couple of posts (the first which come up in a search for that error), which talk about setting things up correctly in the sp_addlinkedsrvlogin that needs to ...

Likes

Translate

Translate
Adobe Community Professional ,
Mar 31, 2021 Mar 31, 2021

Copy link to clipboard

Copied

Paul, I realize you may feel this seems a problem due to the change of your cf version, but perhaps it's not.

 

First, you note also that your sql version  changed, and while you feel that the new SQL 2017 setup is "should match" the one for SQL 2012, perhaps it does not...and especially regarding linked server setup. For example, see these couple of posts (the first which come up in a search for that error), which talk about setting things up correctly in the sp_addlinkedsrvlogin that needs to be done:

 

Second, you seem to be implying that the CF Admin DSN setup (for your SQL Server datasource) is "the same" between CF10 and CF2018. Of course, it's possible that they are NOT as equal as you expect. Are you saying you have both the CF10 and CF2018 admins available to you, and have looked at the DSN on each, including the "advanced" button of the DSN page (to see things like the conneciton string passed to SQL Server)? And are those things really all "identical"?

 

Just a couple of sanity checks that it seems you should rule out. Perhaps someone else may come along with more specifics. Do please let us know if you try the above and what you find. Thanks.


/Charlie (server troubleshooter, carehart.org)

Likes

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
New Here ,
Apr 01, 2021 Apr 01, 2021

Copy link to clipboard

Copied

Thank you for this information.  Yes, it is not a ColdFusion issue - I tested the same databases using the original and new version, and both have the same problem.  

It looks like there is an extra command in the old Linked Server that could solve our problems (EXEC master.dbo.sp_addlinkedsrvlogin ...)

I was too focused on the migration of both the ColdFusion and databases to new versions in a new organisation's domain (and my DBAs assurances that it was all working).

Thanks 

Paul

Likes

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
Adobe Community Professional ,
Apr 01, 2021 Apr 01, 2021

Copy link to clipboard

Copied

LATEST

Thanks for the update and glad to help. Yep, it was that sp_addlinkedsrvlogin which I'd noted has been the cause of problems for others And I see you marked my first reply as the answer. Thanks for that also. Hope things get working for you soon. If you may think to come back to confirm even good news, that could help future readers.


/Charlie (server troubleshooter, carehart.org)

Likes

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