I have a data source that is setup to do large text retrievals from a SQL Server. The only advanced setting override it has is Large Text Buffer : 640000
When Sandbox security is turned on, I get the following error:
Connection verification failed for data source: Scorecard3_large
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.
I did to change any of the Sandbox settings. I am on ColdFusion 9.01hf4. Is there something I need to enable or change?
My bet is your using a SQL instance and connecting to it via a name like myServerName\myInstanceName. This fails after enabling sandbox security in ColdFusion 9. I just noticed after looking up my answer from a previous post that you had actually commented on that post. Back then you commented that you were not using sandbox security. The issue is not with the large text datasource, it is a bug when using named instances and sandbox security in ColdFusion 9. I have copied my answer below.
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. 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.