CraftCMS Connect Plug-in use with MSSQL Server

[snagelia]

Hi, I am trying to use connect plug to connect to Microsoft SQL Server DB which resides on a different server than my web server. After the setup when I am using open() to open the connection and then using the isActive method and it is returning 0. Looks like it is not connected to the DB as my SQL query is not returing the expected result as well. Please guide me what I am doing wrong. Any help is higly appreciated. Below is my .env and config file setting.

.env file

putenv("REMOTE_DB_DRIVER=sqlsrv"); putenv("REMOTE_DB_SERVER=xxxx"); putenv("REMOTE_DB_USER=xxxx"); putenv("REMOTE_DB_PASSWORD=xxxx"); putenv("REMOTE_DB_DATABASE=xxxx"); putenv("REMOTE_DB_PORT=1433");

config file (general.php)

//sql server db 'remote' => [ 'driver' => getenv('REMOTE_DB_DRIVER'), 'server' => getenv('REMOTE_DB_SERVER'), 'user' => getenv('REMOTE_DB_USER'), 'password' => getenv('REMOTE_DB_PASSWORD'), 'database' => getenv('REMOTE_DB_DATABASE'), 'port' => getenv('REMOTE_DB_PORT') ],

then in twig template:

{% set db = craft.connect.open('remote') %} {% if isActive %} hello active {% else %} not active {% endif %}

[khalwat]

Your .env file shouldn't have putenv() in it? It should just list the key/value pairs.

I'd verify that the environment variables are actually set.

[snagelia]

Thanks for your response. I forgot to mention that my set up little different than the default .env file. I am using .env.php and have already working set up for MYSQL DB by using putenv() as below. Now I am trying to add a secondary new SQL Server DB connection that is needed for one particular part of the website.

working .env.php file setting for MYSQL

putenv("DB_DRIVER=mysql"); putenv("DB_SERVER=localhost"); putenv("DB_USER=craft"); putenv("DB_PASSWORD=xxxx"); putenv("DB_DATABASE=craft3dev"); putenv("DB_SCHEMA=public"); putenv("DB_TABLEPREFIX=breas"); putenv("DB_PORT=3306");

As you pointed out, I checked whether the environment variables are getting set by having this command in template:

{{craft.app.config.general.remote.driver}}

and it returns: sqlsrv

What could be wrong?

[khalwat]

I think you should try using actual environment variables in a .env file, because I'm not sure if everything is being bootstrapped in the right order.

Also, you should be using a config/connect.php file as described here: https://nystudio107.com/docs/connect/#configuring-connect

It should not be in your general.php

[snagelia]

Ok, so I created a new .env file just for this set up and put the variables as below:

#Connect to SQL Server DB in Pathology5
# The database driver that will be used (mssql or sqlsrv)
REMOTE_DB_DRIVER="sqlsrv"

# The database server name or IP address 
REMOTE_DB_SERVER="xxxxx"

# The database username to connect with
REMOTE_DB_USER="username"

# The database password to connect with
REMOTE_DB_PASSWORD="xxxx"

# The name of the database to select
REMOTE_DB_DATABASE="pathcatalog"

# The port to connect to the database with.
REMOTE_DB_PORT="1433"

Then I created a new config\connect.php and put the below code:

<?php
/**
 * SQL Server Database Configuration
 *
 */

return [
    'connections' => [
        'remote' => [
            'driver' => getenv('REMOTE_DB_DRIVER'),
            'server' => getenv('REMOTE_DB_SERVER'),
            'user' => getenv('REMOTE_DB_USER'),
            'password' => getenv('REMOTE_DB_PASSWORD'),
            'database' => getenv('REMOTE_DB_DATABASE'),
            'port' => getenv('REMOTE_DB_PORT')
        ],
    ],
];

In template file

{% set db = craft.connect.open('remote') %}

Now when I go to the browser and load the page, getting 'Internal Server Error'

[snagelia]

Ok, 'Internal Server Error' resolved. Was missing 'tablePrefix' variable as didn't define one. Once did it it is gone. But it is still not connecting to SQL Server DB as isActive is returning '0'

[khalwat]

I'm not sure what to tell you, other than it is most likely:

1) A configuration error of some kind

2) The database isn't accessible via the credentials you are providing

3) Some kind of environment setup issue

See here for more:

https://www.google.com/search?q=yii2+microsoft+sql&rlz=1C5CHFA_enUS980US984&oq=yii2+microsoft+sql&aqs=chrome..69i57j33i160l3.5372j0j7&sourceid=chrome&ie=UTF-8