Install OTRS using SQL Server 2008

[altjx]

Forgive me for being a noob, but I'm trying to install OTRS and on the second step to create a database, the only option is MYSQL. I'm using SQL Server 2008 for some other software as well and would like to create a database using SQL Server 2008.

Under the Type: MYSQL, it says to refer to the README.database. First of all, what am I supposed to use to open this? A text editor shows all kind of scrambled unorganized text. This is crazy. On OTRS side it shows "Access denied"... I don't even have MySQL installed, just SQL Server 2008...

I used the windows installer to install OTRS. Really in need of some help here. Not even sure where to start googling considering there's not much talk about SQL Server 2008 and OTRS.. I know I'm pretty new.

[crythias]

http://www.google.com/search?q=site:for ... .org+mssql
http://source.otrs.org/viewvc.cgi/otrs/ ... iew=markup
http://faq.otrs.org/otrs/public.pl?Acti ... ItemID=121

[altjx]

http://www.google.com/search?q=site:for ... .org+mssql
http://source.otrs.org/viewvc.cgi/otrs/ ... iew=markup
http://faq.otrs.org/otrs/public.pl?Acti ... ItemID=121

Thanks!!

[altjx]

http://www.google.com/search?q=site:for ... .org+mssql
http://source.otrs.org/viewvc.cgi/otrs/ ... iew=markup
http://faq.otrs.org/otrs/public.pl?Acti ... ItemID=121

Quick question, how would I import those .sql files into the otrs database in the SQL Management Studio 2008?

[altjx]

Using those settings

Code: Select all

# MSSQL database settings 
$Self->{DatabaseDSN} = "DBI:ODBC:driver={SQL Server};Server=localhost,1433;database=otrs;uid=otrs;pwd=password;"; 
$Self->{"Database::Type"} = 'mssql'; 
$Self->{DatabaseUserTable} = 'system_user2'; 

and modifying it with the correct password, I'm still getting a

Code: Select all

Can't connect to MySQL server on 'localhost' (10061)

error.

Am I getting this error because of what I just modified? Or is this something else? MySQL service is indeed stopped, as the faq suggested.

[altjx]

Based off the site below:

http://faq.otrs.org/otrs/public.pl?Acti ... ItemID=121

Is this from a Unix/Linux machine? How do I run the CheckDB.pl from a Windows machine? Crythias can you continue helping with this? I'm digging very deep in the forums and google articles and just seem to keep getting stuck with a few things.

[crythias]

Please post your Config.pm with personal information redacted.
If the error is saying it can't find MySQL, it's probably because it's trying to connect to it, still, which probably means that information is still in Config.pm.

I'll try to help, but I'm limited in my knowledge to what's here, more or less. That is to say, (sorry for sounding mercenary), in my $business, I would probably be able figure this out for a client. If I'm able to help you out from what I can glean from the boards, google, and wiki, great, but I don't have a test case or reason to make it work for myself on mssql, so I haven't spent a lot of time troubleshooting it.
(Experience level: low. Aptitude: high.)

[crythias]

How do I run the CheckDB.pl from a Windows machine?

command prompt,
perl CheckDB.pl

or
"c:\Program Files\OTRS\StrawberryPerl\perl\bin\perl.exe" CheckDB.pl

[altjx]

Thanks for the reply. Here my config.pm file based off the FAQs:

Code: Select all

# --
# Kernel/Config.pm - Config file for OTRS kernel
# Copyright (C) 2001-2010 xxx, http://otrs.org/
# --
# $Id: Config.pm.dist,v 1.23 2010/01/13 22:25:00 martin Exp $
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file COPYING for license information (AGPL). If you
# did not receive this file, see http://www.gnu.org/licenses/agpl.txt.
# --
#  Note:
#
#  -->> OTRS does have a lot of config settings. For more settings
#       (Notifications, Ticket::ViewAccelerator, Ticket::NumberGenerator,
#       LDAP, PostMaster, Session, Preferences, ...) see
#       Kernel/Config/Defaults.pm and copy your wanted lines into "this"
#       config file. This file will not be changed on update!
#
# --

package Kernel::Config;

sub Load {
    my $Self = shift;
    # ---------------------------------------------------- #
    # ---------------------------------------------------- #
    #                                                      #
    #         Start of your own config options!!!          #
    #                                                      #
    # ---------------------------------------------------- #
    # ---------------------------------------------------- #

    # ---------------------------------------------------- #
    # database settings                                    #
    # ---------------------------------------------------- #
    # DatabaseHost
	
	# MSSQL database settings 
$Self->{DatabaseDSN} = "DBI:ODBC:driver={SQL Server};Server=localhost,1433;database=otrs;uid=otrs;pwd=MyPassword;"; 
$Self->{"Database::Type"} = 'mssql'; 
$Self->{DatabaseUserTable} = 'system_user2'; 
	
    # (The database host.)
#    $Self->{DatabaseHost} = 'localhost';
    # 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/otrs.CryptPassword.pl
    # for crypted passwords.)
#    $Self->{DatabasePw} = 'some-pass';
    # DatabaseDSN
    # (The database DSN for MySQL ==> more: "man DBD::mysql")
#    $Self->{DatabaseDSN} = "DBI:mysql:database=$Self->{Database};host=$Self->{DatabaseHost};";

    # (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};";

    # ---------------------------------------------------- #
    # fs root directory
    # ---------------------------------------------------- #
    $Self->{Home} = 'C:/PROGRA~2/OTRS/OTRS';

    # ---------------------------------------------------- #
    # insert your own config settings "here"               #
    # config settings taken from Kernel/Config/Defaults.pm #
    # ---------------------------------------------------- #
    # $Self->{SessionUseCookie} = 0;
    # $Self->{CheckMXRecord} = 0;

    # ---------------------------------------------------- #

    # ---------------------------------------------------- #
    # data inserted by installer                           #
    # ---------------------------------------------------- #

    $Self->{LogModule}          = 'Kernel::System::Log::File';
    $Self->{LogModule::LogFile} = 'C:/PROGRA~2/OTRS/OTRS/var/log/otrs.log';
    # $DIBI$


    # ---------------------------------------------------- #
    # ---------------------------------------------------- #
    #                                                      #
    #           End of your own config options!!!          #
    #                                                      #
    # ---------------------------------------------------- #
    # ---------------------------------------------------- #
}

# ---------------------------------------------------- #
# needed system stuff (don't edit this)                #
# ---------------------------------------------------- #
use strict;
use warnings;

use vars qw(@ISA $VERSION);
$VERSION = qw($Revision: 1.23 $)[1];

use Kernel::Config::Defaults;
push (@ISA, 'Kernel::Config::Defaults');

# -----------------------------------------------------#

1;

Just to confirm... After importing the tables using the .sql files and disabling the MySQL server, creating the username and database... I'm supposed to still fill out the form on the installer.pl page right?

User: otrs
Password: OTRS's password
Host: localhost
Type: MySQL

I'm not sure why but in netstat -a there doesn't seem to be anything listening on port 1433. I can't telnet into it from a remote computer but I can get into the web server on port 80. I've changed nothing in the SQL Server Configuration Manager other than enabling TCP/IP.

[crythias]

I'm supposed to still fill out the form on the installer.pl page right?

Probably not. (a bit fuzzy, but you probably don't need to use installer.pl if it's talking correctly. Or you could have installed on MySQL, first, then changed to MSSQL after).

I've changed nothing in the SQL Server Configuration Manager other than enabling TCP/IP.

Did you restart MSSQL after enabling? (I guess...?) http://support.microsoft.com/kb/968872
but ... if you're using ODBC, you probably don't have to worry about things like localhost and ports...

http://forums.otrs.org/viewtopic.php?f=63&t=9049 You might ask arvobowen about his experience.

[altjx]

I'm supposed to still fill out the form on the installer.pl page right?

Probably not. (a bit fuzzy, but you probably don't need to use installer.pl if it's talking correctly. Or you could have installed on MySQL, first, then changed to MSSQL after).

I've changed nothing in the SQL Server Configuration Manager other than enabling TCP/IP.

Did you restart MSSQL after enabling? (I guess...?) http://support.microsoft.com/kb/968872
but ... if you're using ODBC, you probably don't have to worry about things like localhost and ports...

http://forums.otrs.org/viewtopic.php?f=63&t=9049 You might ask arvobowen about his experience.

Yep, I restarted the MSSQL Service after enabling that.

If I don't fill out the form and leave everything default, it gives me the same

Code: Select all

Can't connect to database, read comment!
Can't connect to MySQL server on 'localhost' (10061)

Let me ask you this though. From just a fresh install of Server 08, Sharepoint, and enabling TCP/IP, installing OTRS, does that from my Config.pm seem to be ok?

Also, this is what I get when I run the perl otrs.checkDB.pl

Code: Select all

C:\Program Files (x86)\OTRS\OTRS\bin>perl otrs.CheckDB.pl
DBI connect('driver={SQL Server};Server=localhost,1433;database=otrs;uid=otrs;pw
d=password;','otrs',...) failed: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQ
L Server does not exist or access denied. (SQL-08001) [state was 08001 now 01000
]
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (SQL-01
000) at C:/Program Files (x86)/OTRS/OTRS/Kernel/System/DB.pm line 210
ERROR: OTRS-otrs.CheckDB.pl-10 Perl: 5.12.3 OS: MSWin32 Time: Thu Apr 21 21:16:5
5 2011

 Message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
 or access denied. (SQL-08001) [state was 08001 now 01000]
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (SQL-01
000)

 Traceback (1224):
   Module: Kernel::System::DB::new (v1.127) Line: 181
   Module: otrs.CheckDB.pl (v1.4) Line: 52

Trying to connect to database
DSN: DBI:ODBC:driver={SQL Server};Server=localhost,1433;database=otrs;uid=otrs;p
wd=password;
DatabaseUser: otrs

No database connect!

[altjx]

Now if I change the code to:

Code: Select all

$Self->{DatabaseDSN} = "DBI:ODBC:driver={SQL Server Native Client 10.0};Server=localhost,1433;database=otrs;uid=otrs;pwd=password;"; 
$Self->{"Database::Type"} = 'mssql'; 
$Self->{DatabaseUserTable} = 'system_user2'; 

And run the perl otrs.checkDB.pl I get:

Code: Select all

C:\Program Files (x86)\OTRS\OTRS\bin>perl otrs.CheckDB.pl
DBI connect('driver={SQL Server Native Client 10.0};Server=localhost,1433;databa
se=otrs;uid=otrs;pwd=password;','otrs',...) failed: [Microsoft][SQL Server Nativ
e Client 10.0]TCP Provider: No connection could be made because the target machi
ne actively refused it.
 (SQL-08001) [state was 08001 now HYT00]
[Microsoft][SQL Server Native Client 10.0]Login timeout expired (SQL-HYT00) [sta
te was HYT00 now 08001]
[Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific
 error has occurred while establishing a connection to SQL Server. Server is not
 found or not accessible. Check if instance name is correct and if SQL Server is
 configured to allow remote connections. For more information see SQL Server Boo
ks Online. (SQL-08001) at C:/Program Files (x86)/OTRS/OTRS/Kernel/System/DB.pm l
ine 210
ERROR: OTRS-otrs.CheckDB.pl-10 Perl: 5.12.3 OS: MSWin32 Time: Thu Apr 21 21:26:5
0 2011

 Message: [Microsoft][SQL Server Native Client 10.0]TCP Provider: No connection
could be made because the target machine actively refused it.
 (SQL-08001) [state was 08001 now HYT00]
[Microsoft][SQL Server Native Client 10.0]Login timeout expired (SQL-HYT00) [sta
te was HYT00 now 08001]
[Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific
 error has occurred while establishing a connection to SQL Server. Server is not
 found or not accessible. Check if instance name is correct and if SQL Server is
 configured to allow remote connections. For more information see SQL Server Boo
ks Online. (SQL-08001)

 Traceback (4924):
   Module: Kernel::System::DB::new (v1.127) Line: 181
   Module: otrs.CheckDB.pl (v1.4) Line: 52

Trying to connect to database
DSN: DBI:ODBC:driver={SQL Server Native Client 10.0};Server=localhost,1433;datab
ase=otrs;uid=otrs;pwd=password;
DatabaseUser: otrs

No database connect!

C:\Program Files (x86)\OTRS\OTRS\bin>

So it looks like it likes the SQL Server Native Client 10.0 ODBC driver, just something not configured right now... hmm. Am I on the right path you think?

[crythias]

if the odbc connection works ... test it in windows.

[altjx]

if the odbc connection works ... test it in windows.

I'm not exactly sure how to test an obdc connection. But I'm definitely getting closer and closer. I've learned that SQL Server is listening on 49163. After I changed the Config.pm file to that port, I'm now getting the error below:

Code: Select all

C:\Program Files (x86)\OTRS\OTRS\bin>perl otrs.CheckDB.pl
DBI connect('driver={SQL Server Native Client 10.0};Server=localhost,49163;datab
ase=otrs;uid=otrs;pwd=password;','otrs',...) failed: [Microsoft][SQL Server Nati
ve Client 10.0][SQL Server]Login failed for user 'otrs'. (SQL-28000) [state was
28000 now 42000]
[Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot open database "otrs
" requested by the login. The login failed. (SQL-42000) at C:/Program Files (x86
)/OTRS/OTRS/Kernel/System/DB.pm line 210
ERROR: OTRS-otrs.CheckDB.pl-10 Perl: 5.12.3 OS: MSWin32 Time: Thu Apr 21 21:37:2
3 2011

 Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for
 user 'otrs'. (SQL-28000) [state was 28000 now 42000]
[Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot open database "otrs
" requested by the login. The login failed. (SQL-42000)

 Traceback (1036):
   Module: Kernel::System::DB::new (v1.127) Line: 181
   Module: otrs.CheckDB.pl (v1.4) Line: 52

Trying to connect to database
DSN: DBI:ODBC:driver={SQL Server Native Client 10.0};Server=localhost,49163;data
base=otrs;uid=otrs;pwd=password;
DatabaseUser: otrs

No database connect!

C:\Program Files (x86)\OTRS\OTRS\bin>

Really feel like I'm getting close. I think I'm just having a credential issue now... In SQL Management Studio > Connect > Security > Right click otrs > Properties... I set the password here. Is this the correct place to set the password? Not sure why it's getting a login failed now. At least I'm thankful that it's showing connectivity.

[crythias]

oh, and *perhaps*

Code: Select all

[Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot open database "otrs" requested by the login. The login failed. 

Code: Select all

Login failed for user 'otrs'. (SQL-28000)

means that um. well, the login failed. username/password combo doesn't work but http://forums.devshed.com/perl-programm ... 58271.html seems to have a different idea of your DBI connection path.

But, mssql knows how to authenticate. If it's trying to auth versus a mssql account or an active directory account, you should know how to create that user and test that user's login from ... something, like odbc.

[altjx]

oh, and *perhaps*

Code: Select all

[Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot open database "otrs" requested by the login. The login failed. 

Code: Select all

Login failed for user 'otrs'. (SQL-28000)

means that um. well, the login failed. username/password combo doesn't work but http://forums.devshed.com/perl-programm ... 58271.html seems to have a different idea of your DBI connection path.

But, mssql knows how to authenticate. If it's trying to auth versus a mssql account or an active directory account, you should know how to create that user and test that user's login from ... something, like odbc.

I don't have AD in this environment so it has to be testing against MSSQL... But the thing is, I created an account in the Instance. I can login using that account using the sql management studio, the same account I have in the configuration. I'm not sure where that login failed is coming from

If I add a new OBDC connection and use the Servername\Instance, and the otrs credentials, it tests just perfectly. This is from the local and a remote machine.

[altjx]

I GOT IT!!!!!!!!!!!

Code: Select all

C:\Program Files (x86)\OTRS\OTRS\bin>perl otrs.CheckDB.pl
Trying to connect to database
DSN: DBI:ODBC:driver={SQL Server Native Client 10.0};Server=localhost,49163;data
base=otrs;uid=otrs;pwd=password;
DatabaseUser: otrs

It looks Ok!

I forgot to re-create the otrs database... Now I got it working!! Dude thanks SO much for your help.

Now on the installer.pl side, I'm getting the:

Code: Select all

Can't connect to database, read comment!
Can't connect to MySQL server on 'localhost' (10061)

but yet my CheckDB.pl works fine? Oh god. Here we go again, lol. Sigh.

[crythias]

you cannot use installer.pl with mssql. basically, you need to seed the otrs database with files as indicated in the readme.database given previously. You'll also need to manually (?) adjust apache's httpd.conf to point properly (see readme.webserver)

the following is edited for your sanity:

Code: Select all

23	You will find all generated SQL files under "$HOME_OTRS/scripts/database/*.sql"
24	
25	mssql files:
26	----------------
27	$HOME_OTRS/scripts/database/otrs-schema.mssql.sql
28	$HOME_OTRS/scripts/database/otrs-initial_insert.mssql.sql
29	$HOME_OTRS/scripts/database/otrs-schema-post.mssql.sql

[altjx]

Great! I'm able to get to the logon prompt. Now the root@localhost:root isn't taking for username:password.

Damnit. I logged on from the localhost using the credentials and ran into a :

Code: Select all

Error Message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access[..]

Code: Select all

Backend ERROR: OTRS-CGI-10 Perl: 5.12.3 OS: MSWin32 Time: Thu Apr 21 22:47:37 2011 Message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (SQL-08001) [state was 08001 now 01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (SQL-01000) Traceback (4148): Module: Kernel::System::DB::new (v1.127) Line: 181 Module: Kernel::System::Web::InterfaceAgent::Run (v1.58.2.1) Line: 140 Module: ModPerl::ROOT::ModPerl::Registry::C_3a_Program_20Files_20_28x86_29_OTRS_OTRS_bin_cgi_2dbin_index_2epl::handler (unknown version) Line: 49 Module: (eval) (v1.89.2.1) Line: 204 Module: ModPerl::RegistryCooker::run (v1.89.2.1) Line: 204 Module: ModPerl::RegistryCooker::default_handler (v1.89.2.1) Line: 170 Module: ModPerl::Registry::handler (v1.99) Line: 31 

Eh...