I'm installing a virtual machine with Windows 2003 Web Edition with ColdFusion 9 and a SQL SERVER 2005 Express. I've imported and attached the databases from two files and I can use it correctly from SSMS.
Following the instructions, I've created a System DSN connecting to the server MYSERVERNAME\SQLEXPRESS, combined access Windows Authentication\SQL Authentication and so on. I've tested the connection at the end of process and the test was successful. Then I've enabled TCP connection on lanip+localhost and also the port to 1433.
Finally I went to the ColdFusion Administration panel and I've created a new dsn using Microsoft SQL Driver, setted the dsn name, the name of database and then the server name to MYSERVERNAME\SQLEXPRESS. I used (at least for the moment) the sa credentials. Here come the troubles:
Using MYSERVERNAME\SQLEXPRESS causes a java exception that tells me that the instance is invalid or not running:
java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]The requested instance is either invalid or not running.
Same exception if I use 127.0.0.1\SQLEXPRESS. BUT, if I use 127.0.0.1 The dsn will cause no exception, and more, I can access to database, but some queries crash, telling me (could not be the exact error because I've translated it from my native language):
Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Could not login with username 'sa'.
Surfing the net, I've found a tutorial that tells me to download the Microsoft sqljdbc driver and put it in the WEB-INF directory of cold fusion and then:
JDBC URL: jdbc:sqlserver://{server name}\sqlexpress:1433
Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
Driver Name: SQL Server 2005 JDBC
This leads to the error:
Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Could not login with username 'sa'.
Where can be the problem? If you need more informations about the system I'll post them, just let me know.
you don't need MS's sqljdbc. The error just says it cannot login using sa and the password. So just change the credentials into something that you can log in yourself using MS SQL Server Management
The answer was not in the configuration of ColdFusion, but in the website itself, that has 2 Application.cfm files.