Set up ODBC

[HVGS]

Hi, we have been using ODBC on 2003 server for a year. The database is SQL 2008 on another box.

Last night we moved the mail server to 2008R2. Got a crash every Ability start in a system ODBC dll.

After a few hours playing turns out the crash was due to the 2 ODBC DLLs in the Ability program folder. I renamed them to something else so Ability would use the proper system DLL's and all is OK again.

The connection string we use is DSN=webmail;uid=username;pwd=password

This is the same for both 2003 and 2008R2

The bad DLL's also cause crash on Windows 7 32bit and 64bit


IMPORTANT: When setting up ODBC datasource on 64bit OS you have to use the 32bit ODBC manager as Ability is 32bit app.

i.e. %WINDIR%\SysWOW64\odbcad32.exe

Hope this helps someone.

Regards,
Phil

[Code Crafters]

Sorry for the delay. I managed to get this working ok. First I tried adding a System DSN which didn't work. Next I tried a User DSN which worked fine.

All I did was call it AMS with the SQL server name localhost\SQLExpress (you can use any instance), I then used the default settings for the rest then tested the database and created the AMSUSERS table using the 2 test buttons within AMS ODBC settings. This correctly created an AMSUSERS table in the master database (under Databases\System Databases\master\Tables in SQL Server Management Studio) for localhost\SQLExpress. You could also have changed the settings to place this in a different database. Let me know if you need to do this and if this works ok for you. I also used SQL server express 2008 R2 for this.

[Adrian]

Hi guys,

I've tried as Chris says, but surprisingly it didn't work. And it's exactly the same enviroment I have here!

So, I tried Phil's approach, not very convinced that it would do the trick. And it worked!

Phil, you solved us literally weeks of trying to put this to work, so I'm really thankfull for your post.
I wonder why Chris solution didn't work for me. Anyway, I'm not touching this again any time soon, so the mystery will have to wait.

Thanks a lot, issue solved!

Adrián.-

[Code Crafters]

Thanks Phil for your bug report on this. I didn't have this issue personally but then I only tested the database and created the AMSUSERS table. Did yours crash just on testing and creation or only when using the database later? I have Vista x64 on my machine with SQL Server 2008 R2 and had no issue. Possibly I'm running the 32 bit version of SQL server and avoiding the issue. I'll forward the bug report to the team for further testing anyway.

[HVGS]

Hi Chris, We were upgrading the mail server OS (from Win2003 64bit to 2008R2), so we already had the database previously created on a remote SQL2008 server.

Ability crashed every time we started on 2008R2 whenever ODBC was enabled in the config file.

It also crashed whenever test odbc connection was selected.

Soon as the 2 ODBC dll files in the Ability folder were renamed to something else, Ability and ODBC worked fine on 2008R2.


Regards,
Phil

[Code Crafters]

Thanks for the extra info Phil. We'll look at upgrading the ODBC drivers we provide with the software to properly support 64 bit data sources.

[ColinABQ]

Sorry to revive this old thread, but I thought I'd kick in a bit more info here, rather than create yet another thread ...

I also had AMS 2011 hang up completely when I tried to use ODBC, no matter what I did, even though my ODBC string and settings worked with all other programs and interfaces. I recently upgraded to version 3/2011 Enterprise with the express intent of using some AMS domains and users as an authentication clearing house of sorts, with other programs and services accessing the database, for which ODBC is critical - without that working, I might as well not have bothered. (I offer that info simply to illustrate how major an issue this is for me!)

I tried the trick of renaming the ODBC DLLs supplied with AMS, and that worked!

My specs are ...
I'm using MySQL Community, currently at version 5.1.41 (yes, I know that's out of date, but that is not the point)
I am running both MySQL and AMS on a single Windows 7 Ultimate, 32-bit machine, so the problem is not limited to 32- vs 64-bit installations, nor is it an issue of network connectivity.

Hope that info helps.

[Code Crafters]

If you wouldn't mind, could you remind me again in sufficient detail of the problem and steps to the solution so that we can try to implement this in the next update for AMS 2011?

[ColinABQ]

Sure. I'll describe what happens using the application interface, because things change a bit with AMS running as a service, or accessed via the web admin interface. This will seem redundant and silly, but it's a sure way to find the bug.

Testing The Bug:
1. Make sure that a valid ODBC is set up; in Windows, Start -> Administrative tools -> Data source (ODBC); I'm using MySQL, so I use their connector to create and configure a System DSN, and their interface for testing it. My example will use mailodbc as the DSN name. ALWAYS TEST THE ODBC INDEPENDENTLY, FROM ANOTHER APP! Mine works, using the MySQL connector test function.
2. In the AMS app, Click Settings. Then go to General -> ODBC. Click the ODBC radio button.
3. Enter the Connect String as DSN=mailodbc
- note: do NOT use ANY brackets of any type, no < or >, no quotes, nothing else! Enter it exactly as shown in my example. (There has been some confusion about that.)
4. Enter the database table and field (column) names, as appropriate for your database. I am not using the defaults, and the table already exists. I'll paste in my table create statement at the end of this post.
5. Click the Test Database button! If it works, you'll get a dialog (popup) saying so, and you should be good to go, even if you need to create the table using the button provided.
However, if this bug is in play on your system, the Test Database function, or the create table function, will result in an hourglass or spinning cursor (Win 7). It will churn for a bit, and then Windows will tell you that the application has crashed! I do not recall the exact language, and don;t want to kill my server to snag a screen shot - will do that on your request, though.

Sure enough, if AMS was running as an application, you'll have to re-run it. Note that the ODBC change will not have been placed in effect, which is a really good thing. Otherwise, you'd have to manually edit config files or uninstall and reinstall AMS to fix this. That's where you're in real trouble here, Chris - with AMS running as a service and accessed via the web interface, the "Use ODBC" selection WILL be implemented! But then the service will crash, just as the application did! Maybe the watchdog service kicks in and restarts it, but it will never actually work! Infinite recursion. Crash. Watchdog. Crash. Watchdog. Etc.

The fix:
1. If AMS is running, shut it down (exit the app or stop the service).
2. In Windows Explorer, browse to the AMS install folder; in my case, that's C:\Code-Crafters\Ability Mail Server 3
3. Rename the following two files: odbc32.dll and odbcint.dll
- note: It does not matter what you change the names to. You can change .dll to something impossible, like .bad_dll, or simply change the base file name. I used odbc32-ams.dll and odbcint-ams.dll. You can also move the files to another folder. I would not delete them, but that would also work. The whole point is to make it impossible for AMS to actually use those DLL files, and to force it to use the counterparts provided by the OS (Win 7 in my case).
4. Restart AMS; relaunch the app, restart the service, or reboot the computer.
5. Repeat the test sequence above ("Test The Bug").

This fix worked for me.

Hope that info is what you wanted or needed.

Here's the create query for my table. Please note that with AMS2, I had trouble with the enabled field defined as a BIT. Changing it to TINYINT fixed that. With AMS3, I left the same table in place, and AMS 3likes it just fine, so I didn't take any chances with it. (Plus, I have my own code expecting these field names.)

Code: Select all

CREATE TABLE  `sitegate`.`mail_users` (
  `strdomain` varchar(100) NOT NULL,
  `struser` varchar(100) NOT NULL,
  `strpass` varchar(100) DEFAULT NULL,
  `strgroup` varchar(100) NOT NULL DEFAULT '',
  `strdir` varchar(255) NOT NULL,
  `intenabled` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`strdomain`,`struser`,`strgroup`,`intenabled`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Thanks!
Colin

[Code Crafters]

Thanks. I'll pass these details on to our team to be looked at in the next update. If it's a simple fix it will be fixed then.

[Millennium]

My comment to help others:

Abilty Mail server 2
Windows server 2008 64bit
MySQL 5.3


Had the same problems today, but with some help from this topic I managed:

1. download en install the 32bit ODBC MySQL connector.
2. Open the 32Bit ODBC manager ( %WINDIR%\SysWOW64\odbcad32.exe) and make a Data Source.
3. Shutdown AMS"
4. Remove/Rename "odbc32.dll" and "odbcint.dll" in the AMS2 Folder.
5. Start AMS and start using the DSN in tab odbc following the tutorial