Daemonite: SQL Datasource Mayhem Archive

With the move to Java, CFMX datasources now run through JDBC. This seems to be causing some trouble for folks who were use to connecting through ODBC to SQL Server. Thought I might post some collective thoughts on breathing life into your SQL Server connection when your drowning in the misery of [SQLException occurred in JDBCPool while attempting to connect]

First thing to remember is that it's now JDBC you're dealing with. Windows specific connectivity features that made ODBC hum along nicely are not all the useful here. Jump start your connection:

• update the server to the latest service packs for your version of SQL (should go without saying)
• use the SQL JDBC driver supplied by Merant (ships with CFMX), the JDBC/ODBC bridge may work but it's not as good
• do not use (local) as the server address, use the full domain name or if that doesn't work the server IP address (it appears you need to be running across TCP/IP for the best results)
• the password length cannot exceed 16 chars (see http://blog.daemon.com.au/archives/000062.html)
• make sure you are using SQL Server authentication and not Windows authentication
  • check SQL Server security properties in Enterprise Manager - authentication should be set to "SQL Server and Windows", rather than "Windows only" [thanks to Dave Gassner]
  • to check SQL Server's own authentication settings, right-click on the SQL Server registration, then choose Properties. Then from the Properties dialog, choose the Security pane [thanks to Dave Gassner]
• consider yoga, I hear it is good for stress

One of the things I do miss is the ability to have your datasource automatically authenticate against your windows login. We use to have all our development workstations set up like that. If anyone knows a way to get this to work I'd love to hear it.

Posted by modius at 07:32 PM | Permalink
Trackback: http://blog.daemon.com.au/cgi-bin/dmblog/mt-tb.cgi/33