CFMX 7 DSN for SQL Server 2008

Report · Mar 16, 2009

I have encountered numerous challenges creating new SQL Server DSNs for SQL Server 2000. Usually the error is the connection string or other typical error which I can get resolved with a little researching.

However, I have a new error and two days later I cannot find an answer. I am creating a datasource for CFMX 7 to an SQL Server 2008 database. The OS is Win Server 2003 and CFMX and SQL Server are running on the same machine. I have created the System DSN successfully. SQL Server is running in mixed auth mode. The Administrator account is running the SQL server (this is a dev box). I have also created a SQL Server specific admin account named 'sqlserver'. Testing the datasource in the system DSN control panel it works fine. Tests Successfully. However, in the CFAdmin when I create the DSN it simply spits back the error "The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Login failed for user 'sqlserver'".

The local Administrator account as well as the sqlserver named account have been given DBO security rights to the database.

Does anyone have any suggestions? Any, and all, help is greatly appreciated. I cannot find any hints in this error to direct me to the right path for a resolution.

Report · Mar 18, 2009

Wow. Perhaps I should consider it a distinction to have the only post with zero replies. Hard to swallow though.

I guess going to the source and hoping for any help from Adobe sponsored forums was just getting my hopes too high. Worth a try just the same but this is a very surprising, and disappointing, result.

Surely Adobe has done some testing with CFMX 7 on SQL Server 2008?

Report · Mar 18, 2009

Questions:

1. Is SQL authentication enabled on your SQL 2008 server? This feature is (probably) disabled by the default SQL server installation. I realize you stated mixed mode in your post, but I'd double check this as part of the troubleshooting process.

2. Is "sqlserver" a SQL login? A Windows login cannot be used to connect with CF without some additional setup.

Report · Mar 18, 2009

Thanks Bob. Insightful questions. The answer to both is yes. I created the user 'sqlserver' in Windows (as an Administrator) as well as in SQL Server security for the database. The SQLServer.sqlserver user has DBO status.

The error persists. I am still searching for a clue (ie: clueless) :-)

I will also add this is a clean build of the server. This machine is setup to host development of one site using this database connection. At present nearly all progress on the porject has halted until I get the site connected to the DB so development can continue. Hence, my previous tension. Sorry for the sarcastic tone eveyone.

Report · Mar 18, 2009

quote:

I created the user 'sqlserver' in Windows (as an Administrator) as well as in SQL Server security for the database. The SQLServer.sqlserver user has DBO status.

You should be using a SQL login, not a Windows login. I'd also avoid using an admin account, especially in a production environment.

Report · Mar 18, 2009

More Things to Check:

1. Does your SQL server accept TCP/IP connections from other machines. Verify this is enabled and not blocked by a firewall. Also check that the port SQL is configured for the port your CF datasource uses are the same.

2. Does the error message "Login failed for user 'sqlserver'" really indicate what the underlying problem is? I'd configure SQL server to log failed login attempts. If CF login failures appear in the log then check your login setup and security settings, else you have a different problem.

3. Be aware that a Windows System DSN (ODBC) and a ColdFusion SQL Server datasource (JDBC) are not the same type of connection. Are you configuring a Microsoft SQL Server or ODBC Socket datasource in ColdFusion?

4. Have you installed the latest updater (Adobe's term for service pack) for CF7?

5. Try a login name other then "sqlserver"; "sqlserver" is not documented as reserved word, but I'd try something else like "test_account".

6. Bear in mind that CF7 is older then SQL Server 2008. I am not aware of any compatibility issues, but you might try using Microsoft's JDBC driver or the open source jTDS driver. Again, I think that driver incompatibility is unlikely.

Report · Mar 18, 2009

This blog post has some info on enabling TCP/IP connections in SQL Server.
http://blog.mxunit.org/2009/02/connecting-sql-server-2008-to.html

Report · Mar 18, 2009

Bob - Thank you for sharing your knowledge in your responses. Number 5 in your reply triggered the solution. I cannot find any documentation that 'sqlserver' is reserved anywhere but it does seem obvious when I think of it. I created a different account and it worked.

Of course I have tried dozens of other things as well so it may have been a combination of events. Anyway - I have it running. Many, many thanks.