Moodle 2.8.2 w/ sqlsrv - Attempting to use an NT account name with SQL Server Authentication.

Moodle 2.8.2 w/ sqlsrv - Attempting to use an NT account name with SQL Server Authentication.

by Nils Finnsson -
Number of replies: 4

Good morning,

We recently rolled out a new installation. Environment details are as follows.

OS: Windows Server 2012 R2

HTTP Daemon: IIS 8.5

Moodle: v2.8.2

Database: MSSQL 2008 R2

PHP: 5.6.6

PHP DB connector: Unofficial Microsoft SQL Server Driver for PHP (sqlsrv) 3.0.2.2R from http://robsphp.blogspot.be/2012/06/unofficial-microsoft-sql-server-driver.html I'm using this version as it supports php 5.6.x.


There were no problems getting this installation up and running after creating a database and an SQL user with the db_owner role for it. Our other Moodle installation running 2.4.4 uses FreeTDS along with NT Authentication for the database. I've yet to find a compiled dll of FreeTDS for PHP 5.6.x and decided to go use the sqlsrv driver. An AD account was created and granted privileges to the database. The user is working as confirmed by launching the SQL Management Studio as thatuser, connecting to the database with Windows authentication and successfully accessing the new database.

When I configure moodle (config.php) to use a that new account, Moodle doesn't connect. The following error shows up in the SQL Server logs. 

Error: 18456, Severity: 14, State: 6.

Login failed for user '<our domain>\<database user>'. Reason: Attempting to use an NT account name with SQL Server Authentication. [CLIENT: x.x.x.x]


Error 18456 is a Login failure

State 6 is Attempt to use a Windows login name with SQL Authentication


Here is what I have setup in config.php

$CFG->dbtype    = 'sqlsrv';
$CFG->dblibrary = 'native';
$CFG->dbhost    = '<sql hostname>\<instance name>';
$CFG->dbname    = '<the database name>';
$CFG->dbuser    = '<our domain>\<database user>';
$CFG->dbpass    = '<some magical password>';
$CFG->prefix    = 'mdl_';
$CFG->dboptions = array (
  'dbpersist' => 0,
  'dbport' => '',
  'dbsocket' => '',
);

php.ini is setup with the following extension

extension=php_sqlsrv_56_nts.dll

..and phpinfo() reports that the extension is loaded.

So, I can successfully use SQL Server authentication, but unable to with Windows authentication. Does anyone have a suggestion on what to check next?

Thank you,

-nils

Average of ratings: -
In reply to Nils Finnsson

Re: Moodle 2.8.2 w/ sqlsrv - Attempting to use an NT account name with SQL Server Authentication.

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

I don't know anything about Windows stuff... but I can't help thinking I've seen this question asked several times before. Might be worth a search.

Taking it further, I could be wrong, but I have a feeling that the answer is "that's right, Windows authentication is not supported" but I wouldn't trust me on that one. 

Average of ratings: -
In reply to Howard Miller

Re: Moodle 2.8.2 w/ sqlsrv - Attempting to use an NT account name with SQL Server Authentication.

by Nils Finnsson -
Thanks for the reply Howard. I think I'm in a territory where most people decide to go with local SQL authentication instead of making the required changes to use NT auth.

This post has a bunch of interesting information - https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d8bb596c-9f30-41fb-b136-2abc2a60ac6b/connection-to-sql-server-2008-login-failed-for-user  Essentially it's the same problem, just pared down to the PHP required to make an SQL connection. From what I'm reading, the application pool needs to run in the context of the user I want to connect to the database as. If that's correct, I'd simply remove the $CFG->dbuser and $CFG->dbpass options from config.php. However I'm not sure that will work.

http://php.net/manual/en/function.sqlsrv-connect.php has the following comments...

// Since UID and PWD are not specified in the $connectionInfo array,
// The connection will be attempted using Windows Authentication.

But lib\dml\sqlsrv_native_moodle_database.php has the following connection string..

        $this->sqlsrv = sqlsrv_connect($this->dbhost, array
         (
          'UID' => $this->dbuser,
          'PWD' => $this->dbpass,
          'Database' => $this->dbname,
          'CharacterSet' => 'UTF-8',
          'MultipleActiveResultSets' => true,
          'ConnectionPooling' => !empty($this->dboptions['dbpersist']),
          'ReturnDatesAsStrings' => true,
         ));

So it's passing UID and PWD, even if they're empty. I'll have to do some more testing over the next few days. And unfortunately, I might fall into the group that ends up using SQL authentication anyways. Hopefully I can at least get this sorted for other people that want to do the same thing.

-nils

Average of ratings: -
In reply to Nils Finnsson

Re: Moodle 2.8.2 w/ sqlsrv - Attempting to use an NT account name with SQL Server Authentication.

by Rui Santos -

Hi Nils Finnsson,

Did you manage to get it working with Windows Authentication?

I'm trying to do the same, but with no success.

Thanks.

Average of ratings: -
In reply to Rui Santos

Re: Moodle 2.8.2 w/ sqlsrv - Attempting to use an NT account name with SQL Server Authentication.

by Nils Finnsson -
No. mixed

After a couple more days experimenting and testing, I couldn't get it working properly. Other priorities made me decide to stick with SQL auth and move onto other projects.
Average of ratings: -