Connect ColdFusion to SQL Server database instance

Question

I am trying to connect to a SQL server (2008) database instance in ColdFusion 9 but there is no where to put an instance name in the CF administrator. I can get in with .net using a connection string like so:

Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;
Password=myPassword;

There is a place to enter a connection string in ColdFusion but I don't seem to be able to connect to the right instance (it seems to connect to the default instance). Is there a way to connect ColdFusion to a SQL Server database instance in the administrator?

Answer 1

Simply enter the Server with a \ and the name of the instance

Answer 2

If you are NOT using ColdFusion's sandbox security then you can specify the instance name in the server field of the datasource definition in the ColdFusion administrator. As you mentioned, you would just specify myServerName\myInstanceName. But...

If you are using ColdFusion's sandbox security then that will not work. I had this same issue when I upgraded to ColdFusion 9 and never did find a resolution. I entered a bug at Adobe for it: ColdFusion 9.0.1 Bug 84928 It has since been closed as "Deferred" with the reason "Not Enough Time".

I found that the connection issue arose when using ColdFusion's sandbox security. Disable it and the datasource will work with the instance name specified per above. Enable it and that same connection will fail.

In order to get around the bug you need to specify the database server only (without the instance name) in the server name field and specify the assigned port number for the named instance in the port number field. See the bug for full details.