RT3 tuning - web interface seems slow

bbaker · June 25, 2013, 8:39pm

Here is an update

Config being used.
#Testing SQL Connection
‘Connect_MSSQL’ => { ## GENERIC SECTION
‘type’ => ‘db’,
‘server’ => '192.168..,
‘database’ => ‘TheDataBase’,
‘table’ => ‘TheTable’,
‘user’ => ‘UserWithReadAccess’,
‘pass’ => ‘PasswordToAboveUser’,
‘port’ => ‘MSSQLDefaultPort’,
‘dbi_driver’ => ‘DBI_ODBC’,
‘u_field’ => ‘Email’,
‘p_field’ => ‘password’,
‘p_enc_pkg’ => ‘Digest::SHA1’,
‘p_enc_sub’ => ‘password’,
‘d_field’ => ‘disabled’,
‘d_values’ => [‘0’],
‘attr_match_list’ => [ ‘Gecos’
],
‘attr_map’ => { ‘Name’ => ‘Firstname’,
‘EmailAddress’ => ‘Email’,
‘ExternalAuthId’ => ‘Email’,
‘Gecos’ => ‘Email’
}
},

Getting following error

[Tue Jun 25 20:32:34 2013] [debug]: Attempting to use external auth service:
Connect__MSSQL
(/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth.pm:186)
[Tue Jun 25 20:32:34 2013] [debug]: Calling UserExists with $username
(bbaker@copesan.com) and $service (Connect__MSSQL)
(/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth.pm:227)
[Tue Jun 25 20:32:34 2013] [debug]: Invalid service type for UserExists:
Connect__MSSQL
(/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth.pm:490)

Thanks in advance for the help

View this message in context: http://requesttracker.8502.n7.nabble.com/External-Authentication-tp54436p54441.html

Thomas_Sibley · June 25, 2013, 8:43pm

'Connect_MSSQL'   =>  {      ## GENERIC SECTION

Typo. Compare above to message below.

Thomas_Sibley · June 25, 2013, 8:48pm

Not a typo

I am trying to us a Microsoft SQL database not MySQL.

Please keep replies on the list.

And look more closely at what you typed. One has a single underscore,
the other two. I know you’re using MSSQL not MySQL.

bbaker · June 25, 2013, 9:05pm

Sorry I screwed on both.

Made change and now I get the following messages. LDAP works by the way.

[Tue Jun 25 20:58:13 2013] [debug]: Attempting to use external auth service: Connect_MSSQL (/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth.pm:186)
[Tue Jun 25 20:58:13 2013] [debug]: SSO Failed and no user to test with. Nexting (/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth.pm:214)
[Tue Jun 25 20:58:13 2013] [debug]: Attempting to use external auth service: Connect_LDAP (/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth.pm:186)
[Tue Jun 25 20:58:13 2013] [debug]: SSO Failed and no user to test with. Nexting (/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth.pm:214)
[Tue Jun 25 20:58:13 2013] [debug]: Autohandler called ExternalAuth. Response: (0, No User) (/usr/share/request-tracker/local/plugins/RT-Authen-ExternalAuth/html/Elements/DoAuth:11)

Thanks
Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.com

“Servicing North America with Local Care”-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Thomas Sibley
Sent: Tuesday, June 25, 2013 3:48 PM
To: RT Users
Subject: Re: [rt-users] External Authentication

On 06/25/2013 01:45 PM, Bryon Baker wrote:

Not a typo

I am trying to us a Microsoft SQL database not MySQL.

Please keep replies on the list.

And look more closely at what you typed. One has a single underscore, the other two. I know you’re using MSSQL not MySQL.

Thomas_Sibley · June 25, 2013, 9:12pm

Sorry I screwed on both.

Made change and now I get the following messages. LDAP works by the way.

[Tue Jun 25 20:58:13 2013] [debug]: Attempting to use external auth service: Connect_MSSQL (/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth.pm:186)
[Tue Jun 25 20:58:13 2013] [debug]: SSO Failed and no user to test with. Nexting (/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth.pm:214)
[Tue Jun 25 20:58:13 2013] [debug]: Attempting to use external auth service: Connect_LDAP (/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth.pm:186)
[Tue Jun 25 20:58:13 2013] [debug]: SSO Failed and no user to test with. Nexting (/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth.pm:214)
[Tue Jun 25 20:58:13 2013] [debug]: Autohandler called ExternalAuth. Response: (0, No User) (/usr/share/request-tracker/local/plugins/RT-Authen-ExternalAuth/html/Elements/DoAuth:11)

This means that your MSSQL auth source didn’t find a user, so it moved
on to your LDAP source where it also didn’t find a user (presumably
because some users are in MSSQL and others are in LDAP).

bbaker · June 25, 2013, 9:25pm

I change the dbi_Driver option to ODBC. Now I get the following error

[Tue Jun 25 21:21:29 2013] [error]: DBI connect(‘database=RapidTrax6a;host=192.168.250.29;port=1433’,‘HelpDeskOTRS’,…) failed: [unixODBC][Driver Manager]Data source name not found, and no default driver specified (SQL-IM002) at /usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth/DBI.pm line 446.

Odbc Information
linux-jrlt:~ # perl -MDBD::ODBC -e ‘print $DBD::ODBC::VERSION;’
1.33

linux-jrlt:~ # perl -MDBI -e ‘DBI->installed_versions;’
Perl : 5.016002 (x86_64-linux-thread-multi)
OS : linux (3.4.6-2.10-xen)
DBI : 1.627
DBD::mysql : 4.021
DBD::Sponge : 12.010002
DBD::Proxy : 0.2004
DBD::ODBC : 1.33
DBD::Gofer : 0.015326
DBD::File : 0.41
DBD::ExampleP : 12.014310
DBD::DBM : 0.08

Still don’t know what I am missing.

Thanks
Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.com

“Servicing North America with Local Care”-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Thomas Sibley
Sent: Tuesday, June 25, 2013 4:12 PM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] External Authentication

On 06/25/2013 02:05 PM, Bryon Baker wrote:

Sorry I screwed on both.

Made change and now I get the following messages. LDAP works by the way.

[Tue Jun 25 20:58:13 2013] [debug]: Attempting to use external auth
service: Connect_MSSQL
(/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth.pm:186)
[Tue Jun 25 20:58:13 2013] [debug]: SSO Failed and no user to test
with. Nexting
(/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth.pm:214)
[Tue Jun 25 20:58:13 2013] [debug]: Attempting to use external auth
service: Connect_LDAP
(/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth.pm:186)
[Tue Jun 25 20:58:13 2013] [debug]: SSO Failed and no user to test
with. Nexting
(/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth.pm:214)
[Tue Jun 25 20:58:13 2013] [debug]: Autohandler called ExternalAuth.
Response: (0, No User)
(/usr/share/request-tracker/local/plugins/RT-Authen-ExternalAuth/html/
Elements/DoAuth:11)

This means that your MSSQL auth source didn’t find a user, so it moved on to your LDAP source where it also didn’t find a user (presumably because some users are in MSSQL and others are in LDAP).

bbaker · June 26, 2013, 2:49pm

I am pretty sure the issue is with the install for the unixodbc and Freetds see output below.
linux-jrlt:/usr/bin/gcc # odbcinst -q -d
[FreeTDS]
[SQLServer]
linux-jrlt:/usr/bin/gcc # odbcinst -q -s
[MSSQL]
[Default]
linux-jrlt:/usr/bin/gcc # odbcinst -j
unixODBC 2.2.12
DRIVERS…: /etc/unixODBC/odbcinst.ini
SYSTEM DATA SOURCES: /etc/unixODBC/odbc.ini
USER DATA SOURCES…: /root/.odbc.ini
linux-jrlt:/usr/bin/gcc # isql -v MSSQL
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect

ODBC.ini
[ODBC Data Sources]
MSSQL=TDS connection

[MSSQL]
Description = Microsoft SQL Server
Driver = FreeTDS
Server = 192.168.000.000
Database = DBName
Port = 1433
UsageCount = 1
FileUsage = 1
TDS_Version = 8.0

[ODBC]
Trace=255

[Default]
Driver = SQLServer

ODBCinst.ini
[FreeTDS]
Description = FreeTDS unixODBC Driver
Driver = /usr/lib64/libtdsodbc.so.0
UsageCount = 1
FileUsage = 1
Trace = Yes
TraceFile = /tmp/freetds.log

[SQLServer]
Description = FreeTDS unixODBC Driver
Driver = /usr/lib64/libtdsodbc.so.0
Trace = Yes
TraceFile = /tmp/freetds.log
FileUsage = 1
UsageCount = 1

FreeTDS.conf
[global]
timeout = 10
connect timeout = 10
text size = 64512

[MSSQL]
host = 192.168.000.000
port = 1433
tds version = 8.0
instance = SSLSQLDB
dump file = /tmp/dump.log

I think the unixodbc is not reading these files I am not even getting a trace file. I do not know why. Can anyone see the issue?

Thnaks
Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.com

"Servicing North America with Local Care"From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Bryon Baker
Sent: Tuesday, June 25, 2013 4:26 PM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] External Authentication

I change the dbi_Driver option to ODBC. Now I get the following error

[Tue Jun 25 21:21:29 2013] [error]: DBI connect(‘database=RapidTrax6a;host=192.168.250.29;port=1433’,‘HelpDeskOTRS’,…) failed: [unixODBC][Driver Manager]Data source name not found, and no default driver specified (SQL-IM002) at /usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth/DBI.pm line 446.

Odbc Information
linux-jrlt:~ # perl -MDBD::ODBC -e ‘print $DBD::ODBC::VERSION;’
1.33

linux-jrlt:~ # perl -MDBI -e ‘DBI->installed_versions;’
Perl : 5.016002 (x86_64-linux-thread-multi)
OS : linux (3.4.6-2.10-xen)
DBI : 1.627
DBD::mysql : 4.021
DBD::Sponge : 12.010002
DBD::Proxy : 0.2004
DBD::ODBC : 1.33
DBD::Gofer : 0.015326
DBD::File : 0.41
DBD::ExampleP : 12.014310
DBD::DBM : 0.08

Still don’t know what I am missing.

Thanks
Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.com

“Servicing North America with Local Care”

bbaker · June 26, 2013, 3:38pm

Update
I uninstall FreeTDS and UnixODBC, then install FreeTDS first and then UnixODBC and edited the ini files and now I have both isql and tsql working.

I am now moving back to try and get Request Tracker to work.

Progress I love it. Even when it is slow and painful.

Thanks
Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.com

"Servicing North America with Local Care"From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Bryon Baker
Sent: Wednesday, June 26, 2013 9:50 AM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] External Authentication

I am pretty sure the issue is with the install for the unixodbc and Freetds see output below.
linux-jrlt:/usr/bin/gcc # odbcinst -q -d [FreeTDS] [SQLServer] linux-jrlt:/usr/bin/gcc # odbcinst -q -s [MSSQL] [Default] linux-jrlt:/usr/bin/gcc # odbcinst -j unixODBC 2.2.12
DRIVERS…: /etc/unixODBC/odbcinst.ini SYSTEM DATA SOURCES: /etc/unixODBC/odbc.ini USER DATA SOURCES…: /root/.odbc.ini linux-jrlt:/usr/bin/gcc # isql -v MSSQL [IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect

ODBC.ini
[ODBC Data Sources]
MSSQL=TDS connection

[MSSQL]
Description = Microsoft SQL Server
Driver = FreeTDS
Server = 192.168.000.000
Database = DBName
Port = 1433
UsageCount = 1
FileUsage = 1
TDS_Version = 8.0

[ODBC]
Trace=255

[Default]
Driver = SQLServer

ODBCinst.ini
[FreeTDS]
Description = FreeTDS unixODBC Driver
Driver = /usr/lib64/libtdsodbc.so.0
UsageCount = 1
FileUsage = 1
Trace = Yes
TraceFile = /tmp/freetds.log

[SQLServer]
Description = FreeTDS unixODBC Driver
Driver = /usr/lib64/libtdsodbc.so.0
Trace = Yes
TraceFile = /tmp/freetds.log
FileUsage = 1
UsageCount = 1

FreeTDS.conf
[global]
timeout = 10
connect timeout = 10
text size = 64512

[MSSQL]
host = 192.168.000.000
port = 1433
tds version = 8.0
instance = SSLSQLDB
dump file = /tmp/dump.log

I think the unixodbc is not reading these files I am not even getting a trace file. I do not know why. Can anyone see the issue?

Thnaks
Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.com

“Servicing North America with Local Care”

From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Bryon Baker
Sent: Tuesday, June 25, 2013 4:26 PM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] External Authentication

I change the dbi_Driver option to ODBC. Now I get the following error

[Tue Jun 25 21:21:29 2013] [error]: DBI connect(‘database=RapidTrax6a;host=192.168.250.29;port=1433’,‘HelpDeskOTRS’,…) failed: [unixODBC][Driver Manager]Data source name not found, and no default driver specified (SQL-IM002) at /usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth/DBI.pm line 446.

Odbc Information
linux-jrlt:~ # perl -MDBD::ODBC -e ‘print $DBD::ODBC::VERSION;’
1.33

linux-jrlt:~ # perl -MDBI -e ‘DBI->installed_versions;’
Perl : 5.016002 (x86_64-linux-thread-multi)
OS : linux (3.4.6-2.10-xen)
DBI : 1.627
DBD::mysql : 4.021
DBD::Sponge : 12.010002
DBD::Proxy : 0.2004
DBD::ODBC : 1.33
DBD::Gofer : 0.015326
DBD::File : 0.41
DBD::ExampleP : 12.014310
DBD::DBM : 0.08

Still don’t know what I am missing.

Thanks
Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.com

“Servicing North America with Local Care”

bbaker · June 26, 2013, 4:04pm

When the DBI_Driver string = SQLServer I get the following error…
[Wed Jun 26 15:48:22 2013] [error]: install_driver(SQLServer) failed: Can’t locate DBD/SQLServer.pm in @INC (@INC contains: /srv/www/perl-lib /usr/share/request-tracker/local/lib /usr/lib/perl5/vendor_perl/5.16.2 /usr/lib/perl5/vendor_perl/5.16.2/x86_64-linux-thread-multi /usr/lib/perl5/site_perl/5.16.2/x86_64-linux-thread-multi /usr/lib/perl5/site_perl/5.16.2 /usr/lib/perl5/5.16.2/x86_64-linux-thread-multi /usr/lib/perl5/5.16.2 /usr/lib/perl5/site_perl . /srv/www) at (eval 1431) line 3.

When the DBI_Driver string = ODBC I get the following error…
[Wed Jun 26 15:51:06 2013] [error]: DBI connect(‘database=RapidTrax6a;host=192.168.250.29;port=1433’,‘HelpDeskOTRS’,…) failed: [unixODBC][Driver Manager]Data source name not found, and no default driver specified (SQL-IM002) at /usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth/DBI.pm line 446.

I need to know what the proper DBI_driver string should be when using and ODBC connection to a MS-SQL database. I have test tslq and isql so I know that the ODBC configuration is correct. Just need to get the correct information in the RT_SiteConfig.pm

Thanks
Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.com

"Servicing North America with Local Care"From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Bryon Baker
Sent: Wednesday, June 26, 2013 10:39 AM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] External Authentication

Update
I uninstall FreeTDS and UnixODBC, then install FreeTDS first and then UnixODBC and edited the ini files and now I have both isql and tsql working.

I am now moving back to try and get Request Tracker to work.

Progress I love it. Even when it is slow and painful.

Thanks
Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.com

“Servicing North America with Local Care”

From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Bryon Baker
Sent: Wednesday, June 26, 2013 9:50 AM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] External Authentication

I am pretty sure the issue is with the install for the unixodbc and Freetds see output below.
linux-jrlt:/usr/bin/gcc # odbcinst -q -d [FreeTDS] [SQLServer] linux-jrlt:/usr/bin/gcc # odbcinst -q -s [MSSQL] [Default] linux-jrlt:/usr/bin/gcc # odbcinst -j unixODBC 2.2.12
DRIVERS…: /etc/unixODBC/odbcinst.ini SYSTEM DATA SOURCES: /etc/unixODBC/odbc.ini USER DATA SOURCES…: /root/.odbc.ini linux-jrlt:/usr/bin/gcc # isql -v MSSQL [IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect

ODBC.ini
[ODBC Data Sources]
MSSQL=TDS connection

[MSSQL]
Description = Microsoft SQL Server
Driver = FreeTDS
Server = 192.168.000.000
Database = DBName
Port = 1433
UsageCount = 1
FileUsage = 1
TDS_Version = 8.0

[ODBC]
Trace=255

[Default]
Driver = SQLServer

ODBCinst.ini
[FreeTDS]
Description = FreeTDS unixODBC Driver
Driver = /usr/lib64/libtdsodbc.so.0
UsageCount = 1
FileUsage = 1
Trace = Yes
TraceFile = /tmp/freetds.log

[SQLServer]
Description = FreeTDS unixODBC Driver
Driver = /usr/lib64/libtdsodbc.so.0
Trace = Yes
TraceFile = /tmp/freetds.log
FileUsage = 1
UsageCount = 1

FreeTDS.conf
[global]
timeout = 10
connect timeout = 10
text size = 64512

[MSSQL]
host = 192.168.000.000
port = 1433
tds version = 8.0
instance = SSLSQLDB
dump file = /tmp/dump.log

I think the unixodbc is not reading these files I am not even getting a trace file. I do not know why. Can anyone see the issue?

Thnaks
Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.com

“Servicing North America with Local Care”

From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Bryon Baker
Sent: Tuesday, June 25, 2013 4:26 PM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] External Authentication

I change the dbi_Driver option to ODBC. Now I get the following error

[Tue Jun 25 21:21:29 2013] [error]: DBI connect(‘database=RapidTrax6a;host=192.168.250.29;port=1433’,‘HelpDeskOTRS’,…) failed: [unixODBC][Driver Manager]Data source name not found, and no default driver specified (SQL-IM002) at /usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth/DBI.pm line 446.

Odbc Information
linux-jrlt:~ # perl -MDBD::ODBC -e ‘print $DBD::ODBC::VERSION;’
1.33

linux-jrlt:~ # perl -MDBI -e ‘DBI->installed_versions;’
Perl : 5.016002 (x86_64-linux-thread-multi)
OS : linux (3.4.6-2.10-xen)
DBI : 1.627
DBD::mysql : 4.021
DBD::Sponge : 12.010002
DBD::Proxy : 0.2004
DBD::ODBC : 1.33
DBD::Gofer : 0.015326
DBD::File : 0.41
DBD::ExampleP : 12.014310
DBD::DBM : 0.08

Still don’t know what I am missing.

Thanks
Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.com

“Servicing North America with Local Care”

bbaker · June 26, 2013, 5:03pm

Update
Working with the first error I created a SQLServer.pm with the correct connect string. (I tested using Perl).
Now I get this error.
DBD::SQLServer initialisation failed: Can’t locate object method “driver” via package “DBD::SQLServer” at /usr/lib/perl5/vendor_perl/5.16.2/x86_64-linux-thread-multi/DBI.pm line 820.

Thanks
Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.com

"Servicing North America with Local Care"From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Bryon Baker
Sent: Wednesday, June 26, 2013 11:04 AM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] External Authentication

When the DBI_Driver string = SQLServer I get the following error…
[Wed Jun 26 15:48:22 2013] [error]: install_driver(SQLServer) failed: Can’t locate DBD/SQLServer.pm in @INC (@INC contains: /srv/www/perl-lib /usr/share/request-tracker/local/lib /usr/lib/perl5/vendor_perl/5.16.2 /usr/lib/perl5/vendor_perl/5.16.2/x86_64-linux-thread-multi /usr/lib/perl5/site_perl/5.16.2/x86_64-linux-thread-multi /usr/lib/perl5/site_perl/5.16.2 /usr/lib/perl5/5.16.2/x86_64-linux-thread-multi /usr/lib/perl5/5.16.2 /usr/lib/perl5/site_perl . /srv/www) at (eval 1431) line 3.

When the DBI_Driver string = ODBC I get the following error…
[Wed Jun 26 15:51:06 2013] [error]: DBI connect(‘database=RapidTrax6a;host=192.168.250.29;port=1433’,‘HelpDeskOTRS’,…) failed: [unixODBC][Driver Manager]Data source name not found, and no default driver specified (SQL-IM002) at /usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth/DBI.pm line 446.

I need to know what the proper DBI_driver string should be when using and ODBC connection to a MS-SQL database. I have test tslq and isql so I know that the ODBC configuration is correct. Just need to get the correct information in the RT_SiteConfig.pm

Thanks
Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.com

“Servicing North America with Local Care”

From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Bryon Baker
Sent: Wednesday, June 26, 2013 10:39 AM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] External Authentication

Update
I uninstall FreeTDS and UnixODBC, then install FreeTDS first and then UnixODBC and edited the ini files and now I have both isql and tsql working.

I am now moving back to try and get Request Tracker to work.

Progress I love it. Even when it is slow and painful.

Thanks
Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.com

“Servicing North America with Local Care”

From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Bryon Baker
Sent: Wednesday, June 26, 2013 9:50 AM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] External Authentication

I am pretty sure the issue is with the install for the unixodbc and Freetds see output below.
linux-jrlt:/usr/bin/gcc # odbcinst -q -d [FreeTDS] [SQLServer] linux-jrlt:/usr/bin/gcc # odbcinst -q -s [MSSQL] [Default] linux-jrlt:/usr/bin/gcc # odbcinst -j unixODBC 2.2.12
DRIVERS…: /etc/unixODBC/odbcinst.ini SYSTEM DATA SOURCES: /etc/unixODBC/odbc.ini USER DATA SOURCES…: /root/.odbc.ini linux-jrlt:/usr/bin/gcc # isql -v MSSQL [IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect

ODBC.ini
[ODBC Data Sources]
MSSQL=TDS connection

[MSSQL]
Description = Microsoft SQL Server
Driver = FreeTDS
Server = 192.168.000.000
Database = DBName
Port = 1433
UsageCount = 1
FileUsage = 1
TDS_Version = 8.0

[ODBC]
Trace=255

[Default]
Driver = SQLServer

ODBCinst.ini
[FreeTDS]
Description = FreeTDS unixODBC Driver
Driver = /usr/lib64/libtdsodbc.so.0
UsageCount = 1
FileUsage = 1
Trace = Yes
TraceFile = /tmp/freetds.log

[SQLServer]
Description = FreeTDS unixODBC Driver
Driver = /usr/lib64/libtdsodbc.so.0
Trace = Yes
TraceFile = /tmp/freetds.log
FileUsage = 1
UsageCount = 1

FreeTDS.conf
[global]
timeout = 10
connect timeout = 10
text size = 64512

[MSSQL]
host = 192.168.000.000
port = 1433
tds version = 8.0
instance = SSLSQLDB
dump file = /tmp/dump.log

I think the unixodbc is not reading these files I am not even getting a trace file. I do not know why. Can anyone see the issue?

Thnaks
Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.com

“Servicing North America with Local Care”

From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Bryon Baker
Sent: Tuesday, June 25, 2013 4:26 PM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] External Authentication

I change the dbi_Driver option to ODBC. Now I get the following error

[Tue Jun 25 21:21:29 2013] [error]: DBI connect(‘database=RapidTrax6a;host=192.168.250.29;port=1433’,‘HelpDeskOTRS’,…) failed: [unixODBC][Driver Manager]Data source name not found, and no default driver specified (SQL-IM002) at /usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth/DBI.pm line 446.

Odbc Information
linux-jrlt:~ # perl -MDBD::ODBC -e ‘print $DBD::ODBC::VERSION;’
1.33

linux-jrlt:~ # perl -MDBI -e ‘DBI->installed_versions;’
Perl : 5.016002 (x86_64-linux-thread-multi)
OS : linux (3.4.6-2.10-xen)
DBI : 1.627
DBD::mysql : 4.021
DBD::Sponge : 12.010002
DBD::Proxy : 0.2004
DBD::ODBC : 1.33
DBD::Gofer : 0.015326
DBD::File : 0.41
DBD::ExampleP : 12.014310
DBD::DBM : 0.08

Still don’t know what I am missing.

Thanks
Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.com

“Servicing North America with Local Care”

Thomas_Sibley · June 26, 2013, 5:21pm

Update
Working with the first error I created a SQLServer.pm with the correct connect string. (I tested using Perl).
Now I get this error.
DBD::SQLServer initialisation failed: Can’t locate object method “driver” via package “DBD::SQLServer” at /usr/lib/perl5/vendor_perl/5.16.2/x86_64-linux-thread-multi/DBI.pm line 820.

You shouldn’t be trying to write a DBI driver class; that’s madness.

You probably to use ODBC with a connect (dsn) string that includes the
correct odbc (sub)driver to use.

bbaker · June 26, 2013, 5:25pm

I agree Thomas.

As stated before I have the correct information and setup for the ODBC drivers I have tested this with isql and tsql and I wrote a quick perl scrip to test the connection string and that works.

What I can’s figure out is what information needs to be put in the " ‘dbi_driver’ => ‘DBI_DRIVER’," so that RT will use the DSN entry in the ODBC configs.

Thanks
Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.com

“Servicing North America with Local Care”-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Thomas Sibley
Sent: Wednesday, June 26, 2013 12:21 PM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] External Authentication

On 06/26/2013 10:03 AM, Bryon Baker wrote:

Update
Working with the first error I created a SQLServer.pm with the correct connect string. (I tested using Perl).
Now I get this error.
DBD::SQLServer initialisation failed: Can’t locate object method “driver” via package “DBD::SQLServer” at /usr/lib/perl5/vendor_perl/5.16.2/x86_64-linux-thread-multi/DBI.pm line 820.

You shouldn’t be trying to write a DBI driver class; that’s madness.

You probably to use ODBC with a connect (dsn) string that includes the correct odbc (sub)driver to use.

Thomas_Sibley · June 26, 2013, 6:32pm

I agree Thomas.

As stated before I have the correct information and setup for the
ODBC drivers I have tested this with isql and tsql and I wrote a
quick perl scrip to test the connection string and that works.

What I can’s figure out is what information needs to be put in the "
‘dbi_driver’ => ‘DBI_DRIVER’," so that RT will use
the DSN entry in the ODBC configs.

RT::Authen::ExternalAuth expects to build a DSN like so:

dbi:$dbi_driver:database=$db_database;host=$db_server;port=$db_port

Yet ODBC wants a sub-driver, so try this hack:

dbi_driver => ‘ODBC’,
database => ‘FooBar;driver=FreeTDS’,

bbaker · June 26, 2013, 6:50pm

Looks like to me that is a little closer.

[Wed Jun 26 18:48:08 2013] [debug]: Calling UserExists with $username (bbaker) and $service (Connect_MSSQL) (/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth.pm:227)
[Wed Jun 26 18:48:08 2013] [warning]: DBD::ODBC::db selectall_hashref failed: [unixODBC][FreeTDS][SQL Server]Incorrect syntax near the keyword ‘USER’. (SQL-42000) at /usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth/DBI.pm line 219. (/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth/DBI.pm:219)
[Wed Jun 26 18:48:08 2013] [warning]: Issuing rollback() due to DESTROY without explicit disconnect() of DBD::ODBC::db handle database=RapidTrax6a;Driver=SQLServer;DSN=SQLServer;host=192.168.250.29;port=1433 at /usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth/DBI.pm line 219. (/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth/DBI.pm:219)
[Wed Jun 26 18:48:08 2013] [error]: DBD::ODBC::db selectall_hashref failed: [unixODBC][FreeTDS][SQL Server]Incorrect syntax near the keyword ‘USER’. (SQL-42000) at /usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth/DBI.pm line 219.

Thanks
Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.com

“Servicing North America with Local Care”-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Thomas Sibley
Sent: Wednesday, June 26, 2013 1:32 PM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] External Authentication

On 06/26/2013 10:25 AM, Bryon Baker wrote:

I agree Thomas.

As stated before I have the correct information and setup for the ODBC
drivers I have tested this with isql and tsql and I wrote a quick perl
scrip to test the connection string and that works.

What I can’s figure out is what information needs to be put in the "
‘dbi_driver’ => ‘DBI_DRIVER’," so that RT will use
the DSN entry in the ODBC configs.

RT::Authen::ExternalAuth expects to build a DSN like so:

dbi:$dbi_driver:database=$db_database;host=$db_server;port=$db_port

Yet ODBC wants a sub-driver, so try this hack:

dbi_driver => ‘ODBC’,
database => ‘FooBar;driver=FreeTDS’,

bbaker · June 26, 2013, 7:09pm

This is the Perl connect string that I test and works.
DBI->connect(“dbi:ODBC:Driver=SQLServer;DSN=SQLServer;UID=UserID;PWD=password”);

Thanks
Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.com

"Servicing North America with Local Care"From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Bryon Baker
Sent: Wednesday, June 26, 2013 1:51 PM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] External Authentication

Looks like to me that is a little closer.

[Wed Jun 26 18:48:08 2013] [debug]: Calling UserExists with $username (bbaker) and $service (Connect_MSSQL) (/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth.pm:227)
[Wed Jun 26 18:48:08 2013] [warning]: DBD::ODBC::db selectall_hashref failed: [unixODBC][FreeTDS][SQL Server]Incorrect syntax near the keyword ‘USER’. (SQL-42000) at /usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth/DBI.pm line 219. (/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth/DBI.pm:219)
[Wed Jun 26 18:48:08 2013] [warning]: Issuing rollback() due to DESTROY without explicit disconnect() of DBD::ODBC::db handle database=RapidTrax6a;Driver=SQLServer;DSN=SQLServer;host=192.168.250.29;port=1433 at /usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth/DBI.pm line 219. (/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth/DBI.pm:219)
[Wed Jun 26 18:48:08 2013] [error]: DBD::ODBC::db selectall_hashref failed: [unixODBC][FreeTDS][SQL Server]Incorrect syntax near the keyword ‘USER’. (SQL-42000) at /usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth/DBI.pm line 219.

Thanks
Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.com

“Servicing North America with Local Care”

bbaker · June 26, 2013, 8:26pm

Update
ok I have successful connection and select of database. Used this configuration
‘type’ => ‘db’,
‘server’ => ‘192.168.000.000’,
‘database’ => ‘DBName;Driver=SQLServer;DSN=SQLServer’,
‘table’ => ‘[USER]’, *** Put square brackets because “user” is a keyword.
‘user’ => ‘SearchUserID’,
‘pass’ => ’ SearchUserPassword ',
‘port’ => ‘1433’,
‘dbi_driver’ => ‘ODBC’,
‘u_field’ => ‘Email’,
‘p_field’ => ‘password’,
‘p_enc_pkg’ => ‘Digest::SHA1’,
‘p_enc_sub’ => ‘password’,
‘d_field’ => ‘disabled’,
‘d_values’ => [‘0’],
‘attr_match_list’ => [ ‘Gecos’
],
‘attr_map’ => { ‘Name’ => ‘Firstname’,
‘EmailAddress’ => ‘Email’,
‘ExternalAuthId’ => ‘Email’,
‘Gecos’ => ‘Email’
}
},

Now on to the next problem the password are stored in the database using SHA.
None of the user I am using to test pass authentication.

Do I have the right configuration for RT to use this kind of Hash?

Thanks
Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.com

"Servicing North America with Local Care"From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Bryon Baker
Sent: Wednesday, June 26, 2013 2:10 PM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] External Authentication

This is the Perl connect string that I test and works.
DBI->connect(“dbi:ODBC:Driver=SQLServer;DSN=SQLServer;UID=UserID;PWD=pas
DBI->sword”);

Thanks
Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.com

“Servicing North America with Local Care”

From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Bryon Baker
Sent: Wednesday, June 26, 2013 1:51 PM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] External Authentication

Looks like to me that is a little closer.

[Wed Jun 26 18:48:08 2013] [debug]: Calling UserExists with $username (bbaker) and $service (Connect_MSSQL) (/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth.pm:227)
[Wed Jun 26 18:48:08 2013] [warning]: DBD::ODBC::db selectall_hashref failed: [unixODBC][FreeTDS][SQL Server]Incorrect syntax near the keyword ‘USER’. (SQL-42000) at /usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth/DBI.pm line 219. (/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth/DBI.pm:219)
[Wed Jun 26 18:48:08 2013] [warning]: Issuing rollback() due to DESTROY without explicit disconnect() of DBD::ODBC::db handle database=RapidTrax6a;Driver=SQLServer;DSN=SQLServer;host=192.168.250.29;port=1433 at /usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth/DBI.pm line 219. (/usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth/DBI.pm:219)
[Wed Jun 26 18:48:08 2013] [error]: DBD::ODBC::db selectall_hashref failed: [unixODBC][FreeTDS][SQL Server]Incorrect syntax near the keyword ‘USER’. (SQL-42000) at /usr/lib/perl5/vendor_perl/5.16.2/RT/Authen/ExternalAuth/DBI.pm line 219.

Thanks
Bryon Baker
Network Operations Manager
Copesan - Specialists in Pest Solutions
800-267-3726 • 262-783-6261 ext. 2296
bbaker@copesan.com

“Servicing North America with Local Care”