Connection to SQL Server 2017 Express

General discussion of Mantis.

Moderators: Developer, Contributor

Post Reply
StudentMantis
Posts: 5
Joined: Mar 06, 2019 5:10 am

Connection to SQL Server 2017 Express

Post by StudentMantis » Mar 06, 2019 5:17 am

Hello All,

I myself am pretty new to all of this (Mantis and SQL, databases and all).
For a assignment at school i have to configure a helpdesk.
I chose for MantisBT but i get stuck at the part of making a connection from Mantis to my SQL Server to create a database.

Anybody who could help me out please?
Would be much appreciated

cas
Posts: 528
Joined: Mar 11, 2006 11:08 am
Contact:

Re: Connection to SQL Server 2017 Express

Post by cas » Mar 06, 2019 7:44 am

Check out the advice here:
viewtopic.php?t=21245

StudentMantis
Posts: 5
Joined: Mar 06, 2019 5:10 am

Re: Connection to SQL Server 2017 Express

Post by StudentMantis » Mar 06, 2019 7:46 am

Okay thanks will check it out

obmsch
Posts: 14
Joined: Mar 26, 2013 5:19 pm

Re: Connection to SQL Server 2017 Express

Post by obmsch » Mar 06, 2019 6:31 pm

That link is pretty old, and a lot of things have improved on the MantisBT side with SQLServer. The current version (2.19.0) should install
out of the box, if the prerequisites are fulfilled (https://github.com/Microsoft/msphpsql).

As the latest driver (5.6.0) has a serious bug (https://github.com/Microsoft/msphpsql/issues/937), which stops MantisBT from
working, use 5.3.0 until that's gets fixed.


And then there's authentication. As you are using SQLEXPRESS and MantisBT currently only supports 'SQL Server Authentication' then, make sure
SQLServer is running in mixed mode and the MantisBT user (on install/config) matches a SQLServer user with sufficient rights.
Use SSMS to check/setup.

StudentMantis
Posts: 5
Joined: Mar 06, 2019 5:10 am

Re: Connection to SQL Server 2017 Express

Post by StudentMantis » Mar 13, 2019 10:47 am

Correct me if i am wrong but PHP 5.6 isn't the latest driver?

obmsch
Posts: 14
Joined: Mar 26, 2013 5:19 pm

Re: Connection to SQL Server 2017 Express

Post by obmsch » Mar 14, 2019 5:08 am

I didn't talk about PHP versions, but the version of the php extension to access a specific
database (SQL Server in your case). I only warned you to not use the latest release (5.6.0) there,
but to take an older (5.3.0) one. All prerequisites, installation instructions and appropriate links are on
https://github.com/Microsoft/msphpsql too.

Following is a brief list of steps to get MantisBT (>= 2.19.0) running on IIS with SQLEXPRESS (Make
sure all components match the same bitness x86/x64).
1) Install Microsoft ODBC Driver (https://www.microsoft.com/en-us/downloa ... x?id=56567)
2) Use IIS Webplattform-Installer (https://www.microsoft.com/web/downloads/platform.aspx) to install
PHP (>=v7.2.x) and SQL Server driver(php extension).
3) IIS: Create Applicationpool MantisBT
- Identity: ApplicationPoolIdentity
- Load User Profile: True
- Create MantisBT site with that applicationpool
4) SSMS (SQL Server Management Studio)
- Make sure your instance of SQL Server is running in mixed mode
- Create a user for MantisBT. If you don't want that user to have rights to create a
database, create a database for MantisBT manually.
5) Setup/Install MantisBT on your MantisBT site.
Follow the 'Admin-Guide' (doc-dir) to install/update/configure. During the install use:
- 'your-sqlserver-instance-name'
- 'mssqlnative',
- 'your-database-name'
- 'your-sqlserver-user-name'
- 'your-sqlserver-user-pwd'

StudentMantis
Posts: 5
Joined: Mar 06, 2019 5:10 am

Re: Connection to SQL Server 2017 Express

Post by StudentMantis » Apr 02, 2019 6:42 am

I followed these steps. Now as i am at step 5 trying to install the Mantis Application the following error occurs:

Attempting to connect to database as admin BAD
Does administrative user have access to the database? ( SQLState: 28000 Error Code: 18456 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'SA_Mantis'. SQLState: 28000 Error Code: 18456 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'SA_Mantis'. SQLState: 28000 Error Code: 18456 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'SA_Mantis'. SQLState: 28000 Error Code: 18456 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'SA_Mantis'. )


What should my next step be?

obmsch
Posts: 14
Joined: Mar 26, 2013 5:19 pm

Re: Connection to SQL Server 2017 Express

Post by obmsch » Apr 02, 2019 10:55 am

Are you sure
- your SQL Server Instance is running in mixed mode?
- that user 'SA_Mantis' exists?
- the password is correct?

Try to connect with this credentials in SSMS. Make sure, that in the authentication dropdown 'Sql Server Authentication'
is selected. This is just for testing. Switch back to 'Windows Authentication', if you want to configure/manage your instance.

StudentMantis
Posts: 5
Joined: Mar 06, 2019 5:10 am

Re: Connection to SQL Server 2017 Express

Post by StudentMantis » Apr 05, 2019 3:03 am

SQL Server is indeed running in mixed mode. The user does exist also it probably is the password, but i honestly can't remember which password i have used. Any way i could change the password? Or should i create a new user instead

obmsch
Posts: 14
Joined: Mar 26, 2013 5:19 pm

Re: Connection to SQL Server 2017 Express

Post by obmsch » Apr 05, 2019 3:53 am

As I said in my previous post, login to SSMS with 'Windows Authentication' to manage/configure your instance.
Users are under 'Security->Logins'. You can change any property (including pwd) of a user from there.

And before you come back with the next basic question how to configure/manage your database backend, make
your homework. There are plenty of tutorials/informations available all over the web. You chose SQL Server, so
make yourself familiar with that.

Post Reply