Laravel 7 sanctum Database Connection Problem

I am making project for API by using Sanctum laravel package. It needs to connect 2 database servers. One is MySQL to save some data including login user. Another one is SQL Server to get data for API route. There is no problem in login for Sanctum in MySQL database server that I have tested login well and got back token. Also retrieving data from SQL Server is working well when I retrieve data without using Laravel Sanctum.

I need to login check from MySQL database server and retrieve data from SQL Server database server. The problem I am facing now is how and where to define MySQL database server need for Sanctum auth middleware.

The connections are as below:

DB_MYSQL_CONNECTION=mysql
DB_MYSQL_HOST=127.0.0.1
DB_MYSQL_PORT=3306
DB_MYSQL_DATABASE=api
DB_MYSQL_USERNAME=root
DB_MYSQL_PASSWORD=

DB_SQLSRV_CONNECTION=sqlsrv
DB_SQLSRV_HOST=127.0.0.1
DB_SQLSRV_PORT=1433
DB_SQLSRV_DATABASE=StoreData
DB_SQLSRV_USERNAME=sa
DB_SQLSRV_PASSWORD=

For that route

Route::middleware(['auth:sanctum'])->group(function () {  
    Route::get('/items', 'APIController@getAllItems');
});

I run it with auth token after logging success, I got the error as below:

IlluminateDatabaseQueryException: SQLSTATE[42S02]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name ‘personal_access_tokens’. (SQL: select top 1 * from [personal_access_tokens] where [token] = bd1de1df8bc2ac289dbd89f7d640a4c65ebc1b709b565ee500315ee710f12b62) in file …vendorlaravelframeworksrcIlluminateDatabaseConnection.php on line 671

personal_access_tokens and users tables are in the MySQL database. Can I customize which db connection is for Sanctum Auth? and how?

Answer

well you need some customization for personal access token. create a normal model with name of SanctumPersonalAccessClient(what ever you want its in this example) and in AuthServiceProvider.php write this

namespace AppProviders;

use LaravelSanctumSanctum;
use AppSanctumPersonalAccessClient as PersonalAccessClient;

class AuthServiceProvider extends ServiceProvider
{
    /**
     * The policy mappings for the application.
     *
     * @var array
     */
    protected $policies = [
        //
    ];

    /**
     * Register any authentication / authorization services.
     *
     * @return void
     */
    public function boot()
    {
        $this->registerPolicies();
        Sanctum::usePersonalAccessClientModel(PersonalAccessClient::class);
    }
}

then in your model that just created just set connection and table name like this:

namespace App;
use LaravelSanctumPersonalAccessClient;

class SanctumPersonalAccessClient extends PersonalAccessClient
{
    protected $connection = 'my_custom_connection';
    protected $table = 'personal_access_tokens';
}

helpful link but with passport: https://github.com/laravel/passport/issues/247#issuecomment-424095961