Connection to SQL Server 2017 Express

General discussion of Mantis.

Moderators: Developer, Contributor

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

Connection to SQL Server 2017 Express

Post by StudentMantis »

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: 1586
Joined: 11 Mar 2006, 16:08
Contact:

Re: Connection to SQL Server 2017 Express

Post by cas »

Check out the advice here:
viewtopic.php?t=21245
StudentMantis
Posts: 5
Joined: 06 Mar 2019, 10:10

Re: Connection to SQL Server 2017 Express

Post by StudentMantis »

Okay thanks will check it out
obmsch
Posts: 26
Joined: 26 Mar 2013, 22:19

Re: Connection to SQL Server 2017 Express

Post by obmsch »

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: 06 Mar 2019, 10:10

Re: Connection to SQL Server 2017 Express

Post by StudentMantis »

Correct me if i am wrong but PHP 5.6 isn't the latest driver?
obmsch
Posts: 26
Joined: 26 Mar 2013, 22:19

Re: Connection to SQL Server 2017 Express

Post by obmsch »

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: 06 Mar 2019, 10:10

Re: Connection to SQL Server 2017 Express

Post by StudentMantis »

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: 26
Joined: 26 Mar 2013, 22:19

Re: Connection to SQL Server 2017 Express

Post by obmsch »

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: 06 Mar 2019, 10:10

Re: Connection to SQL Server 2017 Express

Post by StudentMantis »

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: 26
Joined: 26 Mar 2013, 22:19

Re: Connection to SQL Server 2017 Express

Post by obmsch »

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