Set up ODBC

[Adrian]

Hi everybody,

I've been using AMS for a long time, and I just wanted to try ODBC for managing users through an online app.
DSN is set up and running, tested OK.
But there´s no way I can get it to work using AMS.

I've configured it not to need user, password o db info, so the only parameter I need is DSN.
I tryed doing it through the GUI and editing mailserver.ini file itself, both with no success.

I tried DSN="AMS" (AMS is the actual name of my ODBC), "DSN='AMS'", DSN='AMS', "DSN=""AMS""", and any other possible combination.
I always get "Data source name not found and no default driver specified".

I'm running Windows 2008 R2 with SQL Server Express 2008.

Any thoughts?

Thanks!

[Code Crafters]

You don't need the quotes at all. You should use something like:
DSN=AMS

Or optionally other paramaters like:
DSN=<dsnname>;DATABASE=<databasename>;UID=<username>;PWD=<password>

Please see http://www.code-crafters.com/abilitymai ... _odbc.html for more information on using ODBC with AMS.

[Adrian]

Hi Chris,

Yes, I first used no quotes at all (I didn´t mentioned it).
I read and followed the ODBC tutorial prior to asking you here.
But I'm still not getting a connection.

Any ideas?

[Code Crafters]

It's most likely that the ODBC connection you setup isn't correct. What database format are you using? Usually you need to create a new database file then associate a new ODBC connection with that file giving it a name like AMS then just use DSN=AMS as your connection string within Ability Mail Server ODBC settings. However, you have to make sure you select the correct file type for the database you're using when adding a system DSN.

Within AMS then use the Test Database button to see what errors are given and we'll go from there.

[Adrian]

Hi Chris,

Testing the DSN through th Windows ODBC set up results OK. It connects to the DB with no error.
When I try to test it using the ODBC option on AMS, I get "Data source name not found and no default driver specified".
What should I do now?

Thanks again.

[Adrian]

Chris, I add some more information.
If I set up the DSN as USER instead of SYSTEM, AMS crashes when I press TEST on the ODBC tab of AMS, as I've read that happened to other users.

[Code Crafters]

I'll forward this information for further investigation. You should be able to use a DSN of AMS as a System DSN to get this working. What database is it though (e.g. sql server, microsoft access) as this matters for setting up the ODBC driver and DSN correctly.

[Adrian]

Hi Chris.
It's SQL Server 2008. Please let me know. Thanks again.

[Code Crafters]

When creating your DSN it must be a System DSN and you have to select the correct file type for the right driver for SQL Server. Have a google at how to set up a System DSN for SQL Server and also maybe try to get a simple ODBC connect test tool or web site to test the ODBC connection that way too.

[Adrian]

Hi Chis,

I'm a developer, and I've created hundreds of ODBC connections on the last 15 years.
The DSN is OK, AMS is failing to connect. Please tell me what else to test.
Thanks again!

[Code Crafters]

You said you were using SQL Server Express 2008. Could you tell me what type of DSN you added for this and the connect string you're using in AMS. Possibly you may need to google the connect string and use some 3rd party tool to test the connection to try to get things working outside of AMS before testing with AMS in the equation as well.

[Adrian]

Hi Chris,

I'm using a System DSN.
The string that should work on AMS is: "DSN=DSNAMS;UID=ams;pwd=ams" (no quotations).
If I change username o password to something that should return an error, AMS tells me "Login failed for user ***", which is right because the login settings are wrong.
It's not telling me that the data source is not found.
But when I use the correct credentials, AMS just crashes.
Even more, if the correct settngs are saved to AMS before it crashes, AMS won't start until y rename the DSN and AMS cannot connect to the DB.

What do you suggest? Thanks again.

[Code Crafters]

That's very strange but at least good news that the data source seems to be correct. You said "I'm running Windows 2008 R2 with SQL Server Express 2008." so I'll try to reproduce the problem here and if I can should be able to hopefully fix the issue.

In the mean time, maybe try not using a username and/or password for the database to see if that helps.

[Adrian]

Right, Chris, these are better news.
I've tried storing UID an PWD on the ODBC record itself, but no matter what, AMS still wants me to put them on the connection string.
If not, it tries to login with blank user, resulting in a connection error.

The configuration you mentioned is right for you to reproduce the problem.

Thanks again!

[Code Crafters]

Have you tried updating the ODBC driver by the way as this can often cause problems with connections.

[Adrian]

Yes Chris, I have the latest.
Could you install a W2k8 R2 to test it?

Thanks!

[Adrian]

Hi,

Any news on this? Thanks!

[Code Crafters]

I haven't had chance to test this fully yet but hope to make some time for this later this week. Sorry for the delay.

[Adrian]

Hi Chris,

Please, I need you guys to help me fix this. I need to have this working ASAP.
Thanks again,

Adrián.-

[Adrian]

Hi everybody.
Any news on this matter?

Thanks!