Migrating a Coldfusion application to Microsoft Azure - setting up a data source

When you're setting up a new server, there's always something that takes much longer than you originally thought it would. Something that has worked fine every previous time you have done it, but won't work today. It's one of the joys of server admin.

Migrating an existing Coldfusion-based company intranet to Microsoft Azure has been an interesting challenge. There were all sorts of things I was expecting to require a bit of effort - updated Active Directory querying, user authentication, serverless functions. But I didn't expect there to be a problem with one of the most basic aspects of the application server setup - linking to the SQL database.

Normally, I add the data source in the Coldfusion Administrator interface - select "Microsoft SQL Server" from the dropdown, enter the database server address and login credentials and done. Generally, any problems are due to firewall ports or mistyped passwords.

Not this time. Presumably because of the encrypted connection, this time I needed to download new drivers and use a very particular connection string:

1. Firstly, I downloaded the latest Microsoft JDBC driver
2. The .jar file then needed to be put where Coldfusion can use it: {coldfusion_root}/cfusion/lib
3. Then the data source details in Coldfusion Administrator:

   Driver

   other

   Driver class

   com.microsoft.sqlserver.jdbc.SQLServerDriver

   Driver Name

   Microsoft SQL Server Azure

   Username

   {SQL user name}

   Password

   {SQL user password}

   JDBC URL

   Obtained from the Azure Portal web interface - go to "SQL databases", select the database, "Overview", click "Show database connection strings", click "JDBC"

I was very pleased to get it working after quite a lot of head-scratching, and extremely grateful to Dale Fraser on Stack Overflow.