Microsoft SQL Server

This page prepares Microsoft SQL Server for myDBR. The driver is sqlsrv, together with the Microsoft ODBC Driver for SQL Server (see Requirements). In the examples the reporting database is mydbr, the login is mydbr and your data is in the database mydata.

myDBR connects with an SQL Server login and password, so the server must allow SQL Server authentication (mixed mode).

Create the reporting database and the user

Run the following with an administrator account, for example in SQL Server Management Studio:

create database mydbr;
go
create login mydbr with password = 'mydbr_password';
go

use mydbr;
go
create user mydbr for login mydbr;
alter role db_owner add member mydbr;
go

-- read access to the data that the reports use
use mydata;
go
create user mydbr for login mydbr;
alter role db_datareader add member mydbr;
go

The user must own the reporting database (the db_owner role) so that it can create and run the objects of myDBR. Repeat the last block for every database that your reports read. A report that updates data also needs the matching db_datawriter role or insert, update and delete rights.

Create the myDBR objects

The creation script is in the install/db_creation directory of the myDBR package. Run it with sqlcmd:

C:\> sqlcmd -S localhost -U mydbr -P mydbr_password -d mydbr -b -i mydbr_create_mssql.sql

The script creates the tables and the stored procedures of myDBR. The -b option stops it at the first error. Make sure that the user has enough rights to create the objects.

Connection settings

define( "DB_VENDOR", 'sqlsrv' );
define( "DB_HOST", 'localhost' );
define( "DB_PORT", 1433 );
define( "DB_NAME", 'mydbr' );
define( "DB_USER", 'mydbr' );
define( "DB_PASSWORD", '********' );
define( "SETUP_DONE", true );

By default myDBR trusts the certificate of the server (TrustServerCertificate). To require a certificate that the web server trusts, change the connection options in user/defaults.php:

$mydbr_defaults['db_connection']['sqlsrv_connect_info']['TrustServerCertificate'] = false;