Cannot use linked server in CFQuery in ColdFusion 2018

[paul_durrant]

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?

[Charlie Arehart · Accepted solution]

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:

• https://blog.sqlauthority.com/2019/06/07/sql-server-fix-msg-7416-access-to-the-remote-server-is-deni...
• https://stackoverflow.com/questions/32084453/sql-linked-server-returns-error-no-login-mapping-exists... (which offers several more things to consider)

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.

[paul_durrant]

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

[Charlie Arehart]

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.