CF DSNS

[weezerboy]

Last week I spent a week trying to get a MySQL DSN setup. I gave up after 12 hours of trying.

Last month I spent hours trying to set up a cf DSN in access. That didn't work.

This weekend I've tried to set up a CF DSN using MS SQL Server 15.0. Again I can't get it to work.

Is there a database that I can easily connect CF to anymore?

Should I use windows authentication for the MS SQL or SQL Server authentication, do I need a connection string, what do I need to do?

It seems like its harder than ever just to get going using CF.

[Charlie Arehart]

Without sharing the errors and details of what you tried, we're left just to guess.

With mysql, did you already know of the need to add a mysql driver to cf? It doesn't come with one, due to licensing issues. Did you add it, and restart cf? Was the mysql on the same server as cf? It really should be as trivial as it ever was to get cf working with mysql, once the driver is in place.

As for Access, cf no longer supports it, but people try and try to get it to work. Hopefully you're not really needing it.

But if you're willing to use it and "just need a db", had you tried derby, which is embedded in cf? Just an idea.

Finally, as for sql server, it and mysql are easily the most use dbs with cf. It certainly "works". As for what may have failed, a really common problem is if it (sql server) is left with its various defaults--so anything LIKE cf would fail to connect to it.

For instance, it defaults to ONLY supporting windows Auth. You have to TELL it to allow use of sql logins, then you can use one.

And cf CAN support use of windows Auth, in the form of  "trusted authentication". You don't specify ANY username or pw in the dsn, and assuming the user running cf is defined as a user of the DB, it just works.

But either way, cf won't connect to sql server out of the box because it uses tcp and sql server does not. That needs to be enabled. I have a blog on that, from years ago (as it's not a new issue) which I have updated (as the way to configure sql server itself has changed over the years).

But let's hear more, and either I or others can elaborate. 

[WolfShade]

I'm curious, but you don't mention what _versions_ of MySQL and MS-SQL you tried using.  But, as Charlie pointed out, installing the DB servers and keeping the defaults is, IMHO, the most likely culprit.

HTH,

^ _ ^

[Dave Watts]

Well, spending a week at this sucks. I'm sorry. I notice you're using SQL Server 2019. I honestly don't know if that's supported in CF 2018. In my experience with other SQL Server versions, you can usually use older versions of CF to talk to newer versions of SQL Server, but not always. You may need to download and use Microsoft's own JDBC driver for that.

Charlie has already mentioned that CF no longer ships with a MySQL driver. So you'll definitely have to download and use that. In both of these cases, you'll have to use "other", then plug in the right JDBC connection string, which will be different for each. There should be guidance for each online, and there's nothing CF-specific about the connection strings.

As for Access, my advice is really just to stop using Access. If you have data in Access that you still want to use, move it into MySQL if at all possible. It's possible to get Access working with CF, but it's a pain and it's not a good thing to do in general.

Finally, you might have basic network connectivity problems between your CF server and the database servers. This is a surprisingly common issue, but you really can't test it reliably from CF. But you can install a regular database client on your CF server, and use that to try to connect. You can download and install SQL Server Management Studio for free. The same is true for several MySQL clients.

Dave Watts, Eidolon LLC

[Charlie Arehart]

Good catch, Dave, that he mentioned "15.0" as the SQL Server version (which translates to 2019). I can confirm that of course CF2018 did not support that out of the box (given prededing it by a year). 

But I will point out for you and all reading along that Adobe does keep a page called "update releases notes", where they offer key points of what's been added in each CF update, and the one for CF2018 does not mention SQL Server 2019 (https://helpx.adobe.com/coldfusion/release-note/coldfusion-2018-updates-release-notes.html). It does mention that support for Windows Server 2019, but of course that doesn't imply support for SQL 2019.

Even so, it probably WOULD work. But I do think that the problems that the OP has hit are really about any of the things we have identified otherwise, and not really about the db version (but of course, it can happen). 

[weezerboy]

Thank you, Charlie, Wolf, and Dave.
You each have answered dozens of questions for me over the years.

On this yes I remember now that MSSQL with CF, I need to get the drivers, etc. You are all correct!

Last question on this: Is there a best link for reference on how to set up a CF DSN with MSSQL?

[Dave Watts]

Thanks for the kind words!

You should be able to follow the instructions from Microsoft that tell you what's required by your JDBC connection string, then choose "Other", then fill in your connection string. I don't know if there's really a link for that that's related to CF, but the Microsoft documentation will give you everything you need.

Dave Watts, Eidolon LLC

[Charlie Arehart]

I'll echo Dave's sentiment.

And I will add about the link you seek that if you you mean one on how to setup CF with a SQL Server driver from MS (so an "other" driver ni CF), the best link on that (though dated) seems the classic at https://www.hass.de/content/coldfusion-10-how-configure-data-sources-microsoft-jdbc-driver-40-sql-se....

[BKBK]

Hi @weezerboy,

As luck would have it, I also had some difficulty with MySQL only days ago. I wanted to update the MySQL datasource on my ColdFusion 2018 from version 5.x to the current 8.0.19. I should like to share with you the steps that I followed to get it to work.

1) I opened the datasource page of the ColdFusion Administrator and copied the properties (DSN name, database, server, port) of all the MySQL datasources; (Ignore this step if you don't yet have any MySQL datasource)

2) I then stopped ColdFusion, opened the directory C:\ColdFusion2018\cfusion\lib. It contains the MySQL ddriver I wish to replace, namely mysql-connector-java-5.1.48.  I moved the file to a back-up directory outside ColdFusion. (If you have no such driver file, then you should ignore this step)

3) I went to MySQL Community Downloads , chose the "Platform Independent" option for Connector/J 8.0.19 and downloaded the Zip file mysql-connector-java-8.0.19.zip (size: 4.4 MB);

4) After unzipping, I copied the file mysql-connector-java-8.0.19.jar to C:\ColdFusion2018\cfusion\lib.

5) I restarted ColdFusion 2018, and went to the datasource page in the ColdFusion Administrator.

6) For each MySQL datasource, I recreated a new datasource using driver "other" and driver class "com.mysql.cj.jdbc.Driver", as in the following images

As you can see, MySQL is installed on localhost, port 3306. This particular datasource is for the my birds database.

7) I pressed the "Verify All Connections" button (in CF Admin) to verify the datasources, and then ran some queries in CF to confirm.