Cannot verify connection to MS SQL Server database instance

[Miguel-F]

I have come across an interesting issue and hopefully I'm not the only one with this scenario.  First the environment: 64-bit Windows Server 2008 (I found the same issue on SP2 and R2), ColdFusion 9.0.1 Enterprise edition, ColdFusion HotFix 1, IIS 7 (native mode), Sun Java 1.6.0_22, Microsoft SQL Server 2005 (on a different server).

The issue that I have is I cannot get my datasource to verify.  ColdFusion returns this error:

Connection verification failed for data source: clients
java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]The requested instance is either invalid or not running.
The root cause was that: java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]The requested instance is either invalid or not running.

Now here is what I have found so far.  I think I have finally narrowed this down to an issue with the Sandbox Security and the fact that we are using a database instance on our SQL server.  With the Sandbox Security enabled in ColdFusion I can verify a datasource that connects directly to the SQL server, for example the Server is "server-name".  I cannot verify a datasource that connects to a database instance on that same server, for example the Server is "server-name\db-instance-name".  I get the error noted above.  With the Sandbox Security disabled in ColdFusion both datasources verify correctly (without error).

Has anyone else come across this situation?

Does anybody know what additional sandbox security ColdFusion needs to make these database instances work?

Does anybody know any additional debugging/logging I can turn on to track this down further?  In watching the server's network connections ColdFusion does not even appear to attempt and make the tcp connection to the database server.  It appears to fail before a connection is attempted.

Thanks in advance for any help.
Miguel Fernandez

[Miguel-F]

I have created a ColdFusion bug for this issue; bug id 84928. I am curious to see if it can be reproduced elsewhere.

[Miguel-F]

I finally found a workaround for this issue thanks to Dave Watts and his replies to my post over in the Database Access forum.  I am putting the details here as well in case anybody comes across this post in the future.

Dave - Thanks so much.  Your response has led me in another direction and I think I have a workaround now.  You are absolutely correct in that the database instance can be accessed via the unique port number instead of the name.  I have always used the name and never had an issue until now.  Anyway, by using the port number my connection verifies correctly with the Sandbox enabled or disabled.  THANK YOU!  I still think this is a bug in ColdFusion 9.0.1 because it has worked in previous versions.  I have updated my bug submission with these findings as well.

Here are my findings from trying different things:

I downloaded the Microsoft JDBC drivers from here: http://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx.  I copied the sqljdbc4.jar file to my cfusion\lib directory and restarted ColdFusion to pick up the new file.  I then created a new datasource in ColdFusion using the "Other" driver.  For the JDBC URL I entered "jdbc:sqlserver://{server-name}\{db-instance-name};databasename=clients". Again, this datasource worked with the Sandbox disabled but failed with the Sandbox enabled.  However it did give a different error message that is more inline with the Sandbox being enabled:

Connection verification failed for data source: clients_jdbc
java.sql.SQLException: access denied (java.netSocketPermission localhost:1024- listen,resolve)
The root cause was that: java.sql.SQLException: access denied (java.net.SocketPermission localhost:1024- listen,resolve)

NOTE that I did not specify the port number in the JDBC URL above.  When I did specify the default port number I got a login failure.  It turns out that if you specify a port number using the Microsoft JDBC driver then it will ignore the instance name you specify (found that here: http://msdn.microsoft.com/en-us/library/ms378428.aspx)

I got the JDBC datasource to work by specifying the actual port in the JDBC URL like this "jdbc:sqlserver://{server-name}\{db-instance-name}:4058;databasename=clients".

I then went back to my original datasource that uses the Microsoft SQL Server driver and removed the instance name from the server and changed the port number and it works now too.

[lg84]

thank you! thank you! thank you! This post along with another stackoverflow post saved me a lot of hassle!!

jdbc - ColdFusion 11 and Azure Database - Stack Overflow