use MSSQL driver custom

[giuseppecapasso]

Description

Hi, this is my situation.
I'm trying to use MSSQL for which I wrote a custom driver as indicated in this PR: #627
I can correctly create a tenant db, create the user and assign him permissions in this way:

public function created(Created $event, array $config, Connection $connection): bool
    {
        $connection = $connection->system($event->website);

        $createUser = config('tenancy.db.auto-create-tenant-database-user');

        if ($createUser) {
            return
                $this->createDatabase($connection, $config) &&
                $this->createLogin($connection, $config) &&
                $this->createUser($connection, $config) &&
                $this->grantPrivileges($connection, $config);
        } else {
            return $this->createDatabase($connection, $config);
        }
    }

    protected function createLogin(IlluminateConnection $connection, array $config)
    {
        return $connection->statement("CREATE LOGIN \"{$config['username']}\" WITH PASSWORD=N'{$config['password']}', DEFAULT_DATABASE=\"{$config['database']}\", DEFAULT_LANGUAGE=[Italiano], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF");
        return true;
    }

    protected function createUser(IlluminateConnection $connection, array $config)
    {
        if (!$this->userExists($connection, $config['username'])) {
            return $connection->statement("CREATE USER \"{$config['username']}\" FOR LOGIN \"{$config['username']}\"");
        }

        return true;
    }

    protected function createDatabase(IlluminateConnection $connection, array $config)
    {
        return $connection->statement("CREATE DATABASE \"{$config['database']}\"");
    }

    protected function grantPrivileges(IlluminateConnection $connection, array $config)
    {
        return $connection->statement("USE \"{$config['database']}\"; ALTER ROLE [db_owner] ADD MEMBER \"{$config['username']}\"");
    }

    protected function userExists($connection, string $username): bool
    {
        return $connection->table('sys.server_principals')
                ->where('name', $username)
                ->count() > 0;
    }

at this point, when I try to register my tenant with:

private function registerTenant($name)
    {
        $website = new Website;
        $website->uuid = Str::random(10);
        // $website->managed_by_database_connection = 'system';
        app(WebsiteRepository::class)->create($website);

        $hostname = new Hostname;
        $baseUrl = env('APP_URL_BASE');
        $hostname->fqdn = "{$name}.{$baseUrl}";
        $hostname = app(HostnameRepository::class)->create($hostname);
        app(HostnameRepository::class)->attach($hostname, $website);

        return $hostname;
    }

I get error on the instruction of creation of a website, I have the impression that it is connected to the db tenant and not to the db system.

where am i wrong?

---

Information

• hyn/multi-tenant version: 5.5
• laravel version: 6.2
• database driver and version: ODBC Driver 17 for SQL Server
• webserver software and version: apache 2.4.35
• php version: 7.3.10

[ArlonAntonius]

Hi there,

Did you already figure out the problem or is everything working fine now? :)

[giuseppecapasso]

Hi, I could not solve the problem.

[ReArmedHalo]

Out of curiosity, @giuseppecapasso, why are you using your own class? Doesn't Laravel have MS SQL support out of the box? https://laravel.com/docs/7.x/database#introduction

I'm guessing you are doing something different during the provisioning of the database but I'm not versed enough in Tenancy to understand this yet.

I'm just researching for any potential problems using MS SQL, specifically Azure SQL Databases in serverless mode ideally, for an upcoming project. My background is MariaDB so MS SQL is a bit of new beast for me.