SQL Server 2008 and ColdFusion 9: no problems, right?

Sure, once it's set up. lol.

ColdFusion and SQL Server talk to each other very well, except when they don't. In a development environment, like, say, your laptop, they don't talk at all by design: CF wants to connect via TCP/IP and SQL Server ships with TCP/IP disabled by default.

So, thanks to Adobe help, Ben Forta's post, and Marc Esher's post, I got it working ...

• Follow the instructions in Esher's post above to open TCP/IP services in your SQL Server instance (enable TCP/IP, click on Properties, IP Addresses, delete all Dynamic Port values, and enter 1433 as the TCP Port, assuming you do have it running on 1433).
• Follow the instructions in Forta's post as well (enable Windows and SQL Server authentication, create a SQL Server account for ColdFusion[1], and give it access to the database in question).
• In the CF Administrator, enter the username and password for the SQL Server account you just created[2], leave the server name blank, and enter the name of the database to which you want to connect.

That's everything, I think, although I probably skipped over some of the painful details. Anyway, it's more complicated than it should be, and it's not all on Microsoft: there are plenty of JDBC connectors that don't have this issue. Adobe just hasn't improved their built-in connector.

---

[1] Unless, of course, you already have ColdFusion running under a separate user account, which is good, in which case you simply let CF log in using Windows Authentication. (return)

[2] Username and password are required only for SQL Server authentication; for Windows authentication, neither are required (because the user is already logged in). (return)

When is SQL Server not SQL Server?

When it's MySQL, of course.

(note: programming-heavy content. Normal sports, gaming, and random content resumes tomorrow.)

You see, it goes like this. We're using ColdFusion 8 and want to use Windows Authentication to login to SQL Server databases (2005, for the most part). Of course that's not directly supported, so we have to set up the data sources as Other and use Microsoft's JDBC driver to connect.

Now for the most part, that's not that big a deal (once you remember that you have to grant access to that user to the database itself - oops on my part). But when a sequence is involved ... (tangent: SQL Server is absolutely stupid when it comes to sequences. I have no idea why they don't separate them from tables like Oracle does)

So when I add a record to a table, either I have to use janky code that runs multiple queries in a single cfquery tag, or I can take advantage of the new feature in CF 8 that returns the identity (SQL Server's name for sequence) value that was just used. Duh.

Of course Adobe "helpfully" set it up so that each database returns a different key-value pair for the same purpose, rather than using a single key for any database. That would have been entirely logical and reasonable. And of course Microsoft's driver prevents CF from realizing that it's connected to a SQL Server database for these purposes, so as a result, it returns the key for a MySQL database (GENERATED_KEYS) instead of the one for a SQL Server database (IDENTITYCOL).

not. helpful.

+1 to me. -1 to Adobe. times 2.