Add MsSQL DB capabilities to Unit test setup & teardown.

[NathanGibbs3]

Item	Description
Link to example:
Build Environment:	Travis-CI & Local
PHP Version:
PHPUnit Version:
Composer Version:
ADODB Version:
Similar Issues:
Depends on Issue(s):
Dependency Type:
Misc Info:	What else might be helpful to know.

---

Expected Behavior:
The Ability to run PHPUnit test set using MsSQL DB server.
Current Behavior:
Currently Test set only designed to run on MySQL / MariaDB & PostgreSQL.
Solution:

• Setup MsSQL configuration.
• Modify tests to use MSSql configuration if requested.
• Test / Verify.

[NathanGibbs3]

@mesteele
When you get a chance, can you post the command lines you used to set this up for MsSQL.

I'm sure like MySQL & PostgreSQL, MsSQL has it's own CLI tool with its own unique command line options to do this.

I'll incorporate them into the setup script for the PHPUnit Tests.

[mesteele]

@mesteele When you get a chance, can you post the command lines you used to set this up for MsSQL.

I'm sure like MySQL & PostgreSQL, MsSQL has it's own CLI tool with its own unique command line options to do this.

I'll incorporate them into the setup script for the PHPUnit Tests.

I install MSSQL Server as the backend and I've always used the Microsoft SQL Server Management Studio to configure all the BASE stuff.

There is a command line (sqlcmd) for MSSQL but I have never used it. I toyed around with it, and it could take some time to migrate the MySQL/PostgreSQL commands to MSSQL.

SQLCMD

[NathanGibbs3]

Thank you, that's what I was looking for. 😄

[mesteele]

Here is the script that works in windows. It requires SQLCMD mode to be enabled before running the actual script. The line to set that is first Usage: and the second Usage: is running the actual script. They both run from a Windows command prompt.

Script name: mssql_apache.sql

-- This script will create the necessary databases and users for BASE
-- Usage: sqlcmd -E -S WinIDS\SQLEXPRESS -Q "use master"
-- Usage: sqlcmd -S WinIDS\SQLEXPRESS -i D:\winids\activators\db_tools\mssql_apache.sql
--

USE [master]
GO
CREATE DATABASE [archive]
GO
CREATE DATABASE [snort]
GO
USE [snort]
GO
:r d:\winids\apache24\htdocs\base\sql\create_snort_tbls_mssql.sql
GO
:r d:\winids\apache24\htdocs\base\sql\create_base_tbls_mssql.sql
GO
:r d:\winids\apache24\htdocs\base\sql\enable_RI.sql
GO
USE [archive]
GO
:r d:\winids\apache24\htdocs\base\sql\create_snort_tbls_mssql.sql
GO
:r d:\winids\apache24\htdocs\base\sql\create_base_tbls_mssql.sql
GO
:r d:\winids\apache24\htdocs\base\sql\enable_RI.sql
GO
USE [master]
GO
CREATE LOGIN [base] WITH PASSWORD=N'an@l1st', DEFAULT_DATABASE=[snort], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [base]
GO
USE [snort];
GO
USE [archive]
GO
CREATE USER [base] FOR LOGIN [base]
GO
USE [archive];
GO
USE [snort]
GO
CREATE USER [base] FOR LOGIN [base]
GO
USE [master]
GO
CREATE LOGIN [snort] WITH PASSWORD=N'l0gg3r', DEFAULT_DATABASE=[snort], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [snort]
GO
USE [snort]
GO
CREATE USER [snort] FOR LOGIN [snort]
GO
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO

I believe I gave both users full access to both databases (sysadmin) so permissions will need to be tailored for each user?