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

[Big_Mad_Kev]

Are you able to give us the connection details for the DSN?

As it sounds like you're connecting to the wrong instance on your SQL Server

[Miguel-F]

No need.  As I tried to state in my original post, the connection works fine if I just disable (uncheck) the ColdFusion sandbox security.

I know that the database and instance are correct and that the server is actually up and running.  We are using the same server and instance with some other ColdFusion servers here (running version CF 8 and Windows 2003 on those).  And those servers also have the ColdFusion sandbox security enabled.

[Big_Mad_Kev]

And you have the dsn enabled in the correct sandbox?

[Miguel-F]

Yes - at least I think so.  I am trying to verify the connection using the ColdFusion admin.  So I assume it uses the default sandbox that is created when you enable Sandbox security ( ColdFusion CFIDE system directory ).  I have not created any Sandbox on this new server yet.  Just using the defaults at this point.

I assume that part is okay because ColdFusion generally throws a security exception if the datasource is not enabled in the sandbox.  That's what I have seen before anyway.

[Miguel-F]

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

[Big_Mad_Kev]

Not sure if the Sandbox does anything weird with port numbers but are

you using SQL 2005 Express?

As this may help?

http://www.fusioncube.net/index.php/coldfusion-sql-server-express

[Dave Watts]

To connect to a named instance, you should be able to simply specify the unique port for that instance, without specifying the instance name. Each named instance should have its own unique port.

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on

GSA Schedule, and provides the highest caliber vendor-authorized

instruction at our training centers, online, or onsite.

Read this before you post:

http://forums.adobe.com/thread/607238

[Miguel-F]

Thanks for the responses guys.

Big Mad Kev - We are using MS SQL Server, not SQL Express.  Although the link you posted did remind me to try something else.  I am going to try and create a JDBC datasource in ColdFusion and see if that works with the Sandbox enabled.  I will post the results here.  Also, the Sandbox in ColdFusion does allow for ip port restrictions but I am using the default Sandbox at the moment which allows all ports.

Dave - Our database instance is configured to use the standard port of 1433.  I think this scenario works the opposite in that you connect to the same server/port but the instance name in the connection string is used to connect to the appropriate database.  I did not setup the database server here, our DBA did.  The odd thing with this issue is that the exact same ColdFusion datasource, without making any datasource configuration setting changes, will work if I just disable Sandbox security.  We also have a few other ColdFusion servers (8.0.1) connecting to this exact same database server instance without issue.  Although the error that is displayed references a connection issue I don't think that is really the issue.

[Dave Watts]

Well, it's been a while since I've worked with named instances in SQL Server, but my understanding is that you can access them by name through the default port, or directly through their unique ports without referencing the name of the instance at all. My advice is to do the latter in this case, since the former doesn't appear to work with sandbox security (not too surprisingly).

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on

GSA Schedule, and provides the highest caliber vendor-authorized

instruction at our training centers, online, or onsite.

Read this before you post:

http://forums.adobe.com/thread/607238

[Miguel-F]

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.

[Deepak_Kumar_Padhy]

Thanks, It helped.