Connection problems between SQL Server 2012 databases and ColdFusion 10

[VincentL12]

I am running ColdFusion 10 and SQL Server 2012.

I am not able to connect databases through the CF Administrator.

The error displayed is:

Connection verification failed for data source MyDB.

java.sql SQL.NonTransientException" [Macromedia][SQL Server JDBC Driver][SQL Server][Cannot open database "C:\ColdFusion10\cfusion\db\MyDB" requested by the login. The login failed.

The root cause was that java.sql.SQLNonTransientException [Macromedia][SQL Server JDBCDriver][SQL Server][Cannot open database C:\ColdFusion10\cfusion\db\MyDB" requested by the login. The login failed.

I have tried the things suggested in the forums - checking that the TCP/IPs are enabled in SQL Server, that the port is set to 1433 in each case, authentication is mixed (SQL Server and Windows), both programs are allowed through Windows Firewall, etc.

Could the problem be that the latest versions of SQL Server and CF are somehow not compatible?

Thanks in advance for any comments/suggestions.

[BKBK]

The problem obviously relates to the database name. This is wrong: C:\ColdFusion10\cfusion\db\MyDB. Follow the instructions on connecting Coldfusion with Microsoft SQL Server.

[VincentL12]

Thanks for the link.

I followed the instructions there; first when creating a new log-in in SQL Server I received a strange message:

An exception occurred while executing a Transact SQL statement or batch.

(Microsoft SqlServer ConnectionInfo)

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name. (Microsoft SQL Server, Error: 1038).

I managed to get past this problem, and completed the work with SQL Server.

Upon going into the CF Administrator and following the linked instructions, firstly I have to enter a User name, otherwise I am prompted for one. I then receive the message:

The password of the account must be changed.

This is despite entering the correct password. If I enter a wrong password, the CF Administrator error changes to:

Login failed for user 'xxxxx'.

Anyone any ideas?

[VincentL12]

I solved the last problem mentioned by unclicking "User must change password at next login".

However the CF Administrator now throws up the error:

Cannot open database requested by the login.

This is after several seconds of trying to connect.

I have tried putting the database in different directories (C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyDB,  C:\ColdFusion10\Cfusion\wwwroot\MyDB, C:\ColdFusion10\Cfusion\db\MyDB, etc), all to no avail.

[Carl Von Stetten]

Vincent,

SQL Server stores it's databases as *.MDF files, and the associated transaction logs as *.ldf files.  You should NEVER move these files around after a SQL Server database is created, unless you absolutely have to, and even then only if you really know what you are doing.  Also, you shouldn't need to specify the file location of your SQL Server database in ColdFusion Administrator.  The server name or IP address and database name should be sufficient, along with username and password of a SQL Server user that has access to the database.  So if you haven't repointed SQL Server to the new database file location, put the files back where they originally were and make sure that you can access and query the database from within SQL Server Management Studio or whatever SQL tool you are using.

I don't believe your problems stem from SQL Server not knowing where the databases are.  It is much more likely an issue with the ColdFusion connection to SQL Server.

From your previous messages, I think maybe you are confusing the login/password to the CF Administrator itself with the login and password that are needed in your data source.  Can you confirm that you are only talking about the database username and password in your last two posts?

Confirm all of the above, and then we can try to help you get your CF connection working.

-Carl V.

[AlbusTeck]

Hi

SQL Server 2012 is not there in the ColdFusion 10 Support Matrix. Please visit this link http://wwwimages.adobe.com/www.adobe.com/content/dam/Adobe/en/products/coldfusion/pdfs/cf10/coldfusi... and check the database supported section.

Swaraj

[carl type3]

I find CF10 with SQL 2012 works OK. Granted SQL 2012 (aka 11.0) is not listed in the support matrix although neither is SQL 2008 r2 (aka 10.5). I would think much of the Windows CF community would be using SQL 2008 r2 versa say SQL 2008 or 2005 (aka 10.0 or 9.0).

Sometimes confusing with SQL is if there is an instance or not. If you connect to SQL via SQL Server Management Studio what is in "Server name" for "Connect Object Explorer"?

HTH, Carl.