Re: [otrs] Problems with ODBC and Ms SQL

28 Jul 2009

      We had this problem as well,  and we fixed it, we had to update the FreeTDS to the latest version or you could go with easysoft but the license is 1500$ Can, FreedTDS is free.  I know your heartache so hope this helps, it’s worked flawless since.  If you have any problems let me know.

We are using:

OTRS 2.4.1 but had it working on beta 4 and 5 as well

 unixODBC

FreeTDS for our ODBC driver

Sql Server 2005

Here is the odbc.ini file we use

[otrs]

Driver          = TDS SQL Server

Description             = OTRS

User            = otrs

Password        = [your password for the sql db you specified for the otrs login]

Trace           = Yes

Database        = otrs

TraceFile               = /tmp/sql.log

ForceTrace              = Yes

Server          = [your server address]

Port            = 1433

TDS Version             = 8.0

Database                = otrs

Here is a snippet from my config file

# DatabaseHost

    # (The database host.)

    $Self->{DatabaseHost} = '[IP or name to your Database]';

    # Database

    # (The database name.)

    $Self->{Database} = 'otrs';

    # DatabaseUser

    # (The database user.)

    $Self->{DatabaseUser} = 'otrs';

    # DatabasePw

    # (The password of database user. You also can use bin/CryptPassword.pl

    # for crypted passwords.)

    $Self->{DatabasePw} = '[password to sql server for user otrs]';

    # DatabaseDSN

       $Self->{"Database::Type"} = 'mssql'; 

    $Self->{DatabaseDSN} = "DBI:ODBC:otrs";

    $Self->{DatabaseUserTable} = 'users';

    $Self->{DatabaseUserTableUserID} = 'id';

    $Self->{DatabaseUserTableUserPW} = 'pw';

    $Self->{DatabaseUserTableUser} = 'login';

    # (The database DSN for PostgreSQL ==> more: "man DBD::Pg")

    # if you want to use a local socket connection

#    $Self->{DatabaseDSN} = "DBI:Pg:dbname=$Self->{Database};";

    # if you want to use a tcpip connection

#    $Self->{DatabaseDSN} = "DBI:Pg:dbname=$Self->{Database};host=$Self->{DatabaseHost};";

From: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Michiel Beijen
Sent: Saturday, July 11, 2009 6:53 AM
To: User questions and discussions about OTRS.
Subject: Re: [otrs] Problems with ODBC and Ms SQL

Hi Stefan,

For a simple connection with MS-SQL from Linux you could use DBD::Sybase. Here's a sample configuration for a Customer Database on Windows / MS-SQL :

   # CustomerUser
    # (customer user database backend and settings)
    $Self->{CustomerUser} = {
        Name => 'Customer DB',
        Module => 'Kernel::System::CustomerUser::DB',
        Params => {
            DSN => 'DBI:Sybase:server=192.168.60.1',
            User => 'database_user',
            Password => 'password',
            Table => 'customer_user',
        },
        # customer unique id
        CustomerKey => 'login',
        # customer #
        CustomerID => 'customer_id',
        CustomerValid => 'valid_id',
        CustomerUserListFields => ['first_name', 'last_name', 'email'],
        CustomerUserSearchFields => ['login', 'last_name', 'customer_id'],
        CustomerUserSearchPrefix => '',
        CustomerUserSearchSuffix => '*',
        CustomerUserSearchListLimit => 250,
        CustomerUserPostMasterSearchFields => ['email'],
        CustomerUserNameFields => ['salutation','first_name','last_name'],
        CustomerUserEmailUniqCheck => 1,
        Map => [
            # note: Login, Email and CustomerID needed!
            # var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly
            [ 'UserSalutation', 'Salutation', 'salutation',  1, 0, 'var', '', 0 ],
            [ 'UserFirstname',  'Firstname',  'first_name',  1, 1, 'var', '', 0 ],
            [ 'UserLastname',   'Lastname',   'last_name',   1, 1, 'var', '', 0 ],
            [ 'UserLogin',      'Username',   'login',       1, 1, 'var', '', 0 ],
            [ 'UserPassword',   'Password',   'pw',          0, 1, 'var', '', 0 ],
            [ 'UserEmail',      'Email',      'email',       0, 1, 'var', '', 0 ],
            [ 'UserCustomerID', 'CustomerID', 'customer_id', 0, 1, 'var', '', 0 ],
            [ 'UserComment',     'Comment',   'comments',    1, 0, 'var', '', 0 ],
            [ 'ValidID',         'Valid',     'valid_id',    0, 1, 'int', '', 0 ],
        ],
        # default selections
        Selections => {
            UserSalutation => {
                'Mr.' => 'Mr.',
                'Mrs.' => 'Mrs.',
            },
        },
    };

Just install the package libdbd-sybase-perl and you're good to go!

Kind regards,

--
Michiel Beijen
Software Consultant
+31 6 - 457 42 418
Bee Free IT + http://beefreeit.nl

On Thu, Jul 9, 2009 at 09:53, Stefan-Michael Guenther  wrote:

Hi Afshar,
...
We are using OTRS in MS Windows platform with MS SQL database and have >not any problem till now. What's your problem with MS SQL?
just to make sure that we are talking about the same configuration:

The OTRS is currently running on a debian server. Did you install OTRS on the windows machine, too?

Stefan

Hello,

are there any members on this mailing list who store the OTRS agents in a MS SQL database?

We have used different OTRS installations on Debian, SuSE, Redhat
and Ubuntu but always get the same login error message which obviously has something to do with the DBD::ODBC module.

It would be helpful if we could get the version numbers MS
SQL, DBD::ODBC and OTRS of such a successful installation.

Thanks in advance,

Stefan-Michael G?nther

---------------------------------------------------------------------
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

NEW! ENTERPRISE SUBSCRIPTION - Get more information NOW!
http://www.otrs.com/en/support/enterprise-subscription/

Re: [otrs] Problems with ODBC and Ms SQL

Robby Garon