My how the years fly and things change.
Even in 2017 I still find value in making quick enterprise applications in Coldfusion. However the world is a changing, many of my endpoint are beyond the boundaries of my corporate firewalls.
I have ended up with a ton of nodeJS webservices endpoints running as docker containers in Azure jamming away data in Azure SQL. I want Coldfusion to be able to utilize that data.
The Solution
The solution is stupid easy… you can use the native Microsoft SQL Driver, no need to mess with anything else.
Go ahead put in the basics
- Database: Name as shown in Azure
- Server: something.database.windows.net
- Port: 1433
- Username: <sqlaccountname>@<databasename>
- Password: <password>
Then for the secret sauce
- Hit Show Advanced Settings
- In the connection string put the following:
EncryptionMethod=SSL;Encrypt=yes;TrustServerCertificate=no;
Note: Encrypt=yes may not be needed but since its working I am not touching it.
And that’s it!
If this was helpful or have a way to make it better? Let me know in the comments.
-Eric
Hi Eric,
Thanks a lot this helped get me connected to our Azure database however I believe the username should be @ not @
Once I made that change it worked for me.
Eric, Thank you SO MUCH for posting this–helped me out tremendously!
Hi, we use Azure with geo-replication to ensure our data is accessible during fail-over. CF requires a DSN to be set in the CF Admin and this means that fail-over cannot be automatic since the DSN needs to be changed manually if there is a fail-over. Do you know that can be overcome?
Thanks Man … it helped me a lot …
for the databasename is that the server name or the actual db name?
This is exactly what I was looking for, but to add that CF2016 with update 11 or greater (including CF2018) the string will fail due to changes in the JDBC driver. The string below is what worked for us to connect CF2016/2018 to Azure SQL (all other settings were the same):
Encrypt=true;hostNameInCertificate=*.database.windows.net; loginTimeout=30;EncryptionMethod=SSL;ValidateServerCertificate=false;
Hi Eric,
Did you have to upgrade the JDBC drivers on CF10 first
I did not at the time, these days it doesnt work anymore and I had to switch to a jdbc connection instead (at least for CF2016)
Awesome, thank you soo much – Why this is such a secret is beyond me.