Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-50307

External DB auth login using MSSQL has not worked since 2.8

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide

      Important!

      Please complete the testing for all the branches. We must triple ensure this does not break any stable. TIA!

      Prerequisites

      • MSSQL server
      • ODBC (comes with MSSQL server)
      • Your moodle instance can be using whatever DB you usually use

      If you choose to complete this test on a unix machine you will need to use the FreeTDS drivers. It is not recommended as you will need a way to create the ODBC data source anyway. (http://www.unixodbc.org/)

      I highly recommend using a windows VM.

      Part 1: MSSQL ODBC

      1. In MSSQL create a new table with at least four columns: Username and password, two other ordinary fields (e.g. firstname, email), and one custom user profile field
        1. Do not use username, password, firstname etc. Add something to the end such as ZZ and use some uppercase and lower case letters. This ensures the backend won't work if it tries to just use the moodle names.
      2. Insert one row
      3. In windows, open "Data Sources (ODBC)"
      4. Create a new data source pointing to that MSSQL server with the appropriate default database
      5. Install the mssql_odbc driver
      6. Set up your external db
        1. Site administration ► Plugins ► Authentication ► Manage authentication
        2. Enable "External database"
        3. Instead of the database host, write the name of your odbc data source
        4. Select mssql_odbc
        5. Leave DB name empty (the database chosen will be the default database you chose in the ODBC program)
        6. Type the name of your table
        7. Fill in the database username and password
        8. Write the names of your username and password fields
        9. Write the names of your other two fields
      7. Try to log in with the username and password you inserted into the mssql table
      8. You should successfully log in and be taken to the edit profile screen
      9. Make sure all the fields are correctly pre-filled
      10. Add more users to your external db table if you need to so that you have some users which have not been created yet
      11. Run php auth/db/cli/sync_users.php
      12. Ensure all users in the db are created with correct details

      Part 2: Another database

      1. Create a new table in MySQL or postgres or something else with at least four columns: Username and password, two other ordinary fields (e.g. firstname, email), and one custom user profile field
        1. Again do not use username, password, firstname etc. Add something to the end of each such as ZZ and use some uppercase and lower case letters.
      2. Modify your external db auth
        1. Site administration ► Plugins ► Authentication ► External database
        2. Type the address of your database host (e.g. localhost)
        3. Select the database type, type the name of the database and the name of the table
        4. Fill in the database username and password
        5. Write the names of your username and password fields
        6. Write the names of your other two fields
      3. Try to log in with the username and password you inserted into the mssql table
      4. You should successfully log in and be taken to the edit profile screen
      5. Make sure all the fields are correctly pre-filled
      6. Add more users to your external db table if you need to so that you have some users which have not been created yet
      7. Run php auth/db/cli/sync_users.php
      8. Ensure all users in the db are created with correct details
      Show
      Important! Please complete the testing for all the branches. We must triple ensure this does not break any stable. TIA! Prerequisites MSSQL server ODBC (comes with MSSQL server) Your moodle instance can be using whatever DB you usually use If you choose to complete this test on a unix machine you will need to use the FreeTDS drivers. It is not recommended as you will need a way to create the ODBC data source anyway. ( http://www.unixodbc.org/ ) I highly recommend using a windows VM. Part 1: MSSQL ODBC In MSSQL create a new table with at least four columns: Username and password, two other ordinary fields (e.g. firstname, email), and one custom user profile field Do not use username, password, firstname etc. Add something to the end such as ZZ and use some uppercase and lower case letters. This ensures the backend won't work if it tries to just use the moodle names. Insert one row In windows, open "Data Sources (ODBC)" Create a new data source pointing to that MSSQL server with the appropriate default database Install the mssql_odbc driver Set up your external db Site administration ► Plugins ► Authentication ► Manage authentication Enable "External database" Instead of the database host, write the name of your odbc data source Select mssql_odbc Leave DB name empty (the database chosen will be the default database you chose in the ODBC program) Type the name of your table Fill in the database username and password Write the names of your username and password fields Write the names of your other two fields Try to log in with the username and password you inserted into the mssql table You should successfully log in and be taken to the edit profile screen Make sure all the fields are correctly pre-filled Add more users to your external db table if you need to so that you have some users which have not been created yet Run php auth/db/cli/sync_users.php Ensure all users in the db are created with correct details Part 2: Another database Create a new table in MySQL or postgres or something else with at least four columns: Username and password, two other ordinary fields (e.g. firstname, email), and one custom user profile field Again do not use username, password, firstname etc. Add something to the end of each such as ZZ and use some uppercase and lower case letters. Modify your external db auth Site administration ► Plugins ► Authentication ► External database Type the address of your database host (e.g. localhost) Select the database type, type the name of the database and the name of the table Fill in the database username and password Write the names of your username and password fields Write the names of your other two fields Try to log in with the username and password you inserted into the mssql table You should successfully log in and be taken to the edit profile screen Make sure all the fields are correctly pre-filled Add more users to your external db table if you need to so that you have some users which have not been created yet Run php auth/db/cli/sync_users.php Ensure all users in the db are created with correct details
    • Affected Branches:
      MOODLE_28_STABLE, MOODLE_29_STABLE, MOODLE_30_STABLE
    • Fixed Branches:
      MOODLE_28_STABLE, MOODLE_29_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      MDL-50307-master-3
    • Sprint:
      Team Beards Sprint 9

      Description

      This appears to be a regression of MDL-20365.

      The password auth_db recieves from the DB during it's user_login function (auth/db/auth.php) is completely blank.

      Notice: Undefined index: userpass in C:\xampp\htdocs\integration\auth\db\auth.php on line 123

      The code works before, but not after this commit https://github.com/moodle/moodle/commit/f97b63bfd16dbb0547f54258f6d3936124d2da45

      Originally I thought it only effected ODBC. According to MDL-50440 it doesn't work with ado_mssql. Looking at the problem code, it's safe to assume ADODB is the problem here. It probably effects only ODBC or straight MSSQL

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  14/Sep/15