Cloning a homestead installation and box

[tom-sinclair]

I would like to copy a homestead installation from one machine to another including VM, however this is not working as I would like, so I am asking for some help with this, can I even do this?

I copied the Homestead installation, and then ran vagrant up; vagrant halt, then copied the .vmdk file over the homestead one, and then ran vagrant up, and received this error:

There was an error while executing `VBoxManage`, a CLI used by Vagrant
for controlling VirtualBox. The command and stderr is shown below.

Command: ["startvm", "3f9ed7c7-813c-48f4-9647-992e717be0cc", "--type", "headless"]

Stderr: VBoxManage: error: UUID {6f0b8c47-7b56-407d-bc18-76726de9de0d} of the medium '/Users/tomsinclair/VirtualBox VMs/homestead-7/ubuntu-16.04-amd64-disk001.vmdk' does not match the value {cd7d1ece-1919-4b6b-b7e7-4333067ad22e} stored in the media registry ('/Users/tomsinclair/Library/VirtualBox/VirtualBox.xml')
VBoxManage: error: Details: code NS_ERROR_FAILURE (0x80004005), component MediumWrap, interface IMedium

I didn't expect that to work tbh, but without a great deal of knowledge on VirtualBox/Vagrant I figured this was the simplest approach, and worth a shot, any help is greatly appreciated. Ultimately I need MSSQL server on the homestead and with the latest issues, I cannot install it on my VM, but my colleague has a working environment.

[svpernova09]

You shouldn't copy the files, rather you should install Homestead on the second machine like you did the first, then copy / adjust the Homestead.yaml settings accordingly, and then vagrant up.

Alternatively you may want to explore the Per Project Installation method

[tom-sinclair]

Which settings in the Homestead.yaml file install MSSQL?

[svpernova09]

Homestead doesn't support MSSQL, there was a brief period where we did, but after dumping a lot of time into following their docs and guides and unable to get a working version we had no choice but to remove it. If you have it working I would be interested in seeing how you managed it so I could implement it. At the moment it's not worth the time for me to spend more time with it without a proof of concept or at least a partial PR.

[tom-sinclair]

Before my failed update, I had it working with MSSQL 2016, I do have a guide on installation but the links to the Microsoft repositories have been changed to 2017 which is no longer working for me, hence the attempt at copying my colleagues working setup, 2016 definitely did though, I was using an old version of Homestead however (v4.0.3 I think), I don't know if this would have any impact on any implementation of MSSQL. We switched away from the ODBC drivers to FreeTDS as we were having other issues, which worked for us.

[svpernova09]

What I would try doing in your case would be on your machine (where everything is working) run vagrant package --base your-homestead-machine-name, that should output a *.box file that they can run vagrant box add my-box my-box.box and then in their Homestead.yamladdbox: my-box. Thenvagrant up`

[tom-sinclair]

Thanks I'll give that a go 👍

[tom-sinclair]

Seems to be working but I'm getting an error with Authentication:

==> homestead-7: Waiting for machine to boot. This may take a few minutes...
    homestead-7: SSH address: 127.0.0.1:2222
    homestead-7: SSH username: vagrant
    homestead-7: SSH auth method: private key
    homestead-7: Warning: Authentication failure. Retrying...

And the last line keeps repeating endlessly, is there something I'm missing? Sorry I'm aware this is turning into a support ticket.

EDIT: Following this thread I added:

config.ssh.username = 'vagrant'
config.ssh.password = 'vagrant'

into homestead.rb and it worked.

[svpernova09]

If it works, I'd run with it, my guess would be there is something different about the SSH keys on the 2nd machine.

Glad you got it working.

[tom-sinclair]

I now have a working version of MSSQL on homestead, I can post the instructions I followed if you're interested?

[svpernova09]

@tom-sinclair sure

[tom-sinclair]

You can follow the installation instructions here, there is an incompatibility with openSSL I had to downgrade openSSL to get this to work, I used apititude (from this thread)

1. Install aptitute sudo apt install aptitude
2. sudo aptitude install mssql-server
The following NEW packages will be installed: ... The following packages have unmet dependencies: mssql-server : Depends: openssl (<= 1.1.0) but 1.1.0f-2~ubuntu16.04.1+deb.sury.org+1 is installed. The following actions will resolve these dependencies:

Keep the following packages at their current version:

        1)  mssql-server [Not Installed]                      
Accept this solution? [Y/n/q/?] n

3. type n and press enter
The following actions will resolve these dependencies:

Downgrade the following packages:

        1)  openssl [1.1.0f-2~ubuntu16.04.1+deb.sury.org+1 (now) -> 1.0.2g-1ubuntu4.6 (xenial-security)]
Accept this solution? [Y/n/q/?] Y The following packages will be DOWNGRADED: openssl

4. type Y and press enter, and mssql-server will be installed

I am currently using freeTDS to connect to the database as I had problems with ODBC, so I installed the PHP sybase extension and the freedtds libraries

sudo apt-get update
sudo apt-get install freetds-common freetds-bin php-sybase

Then you have to set a config for the database host you're using or you can set it globally

/etc/freetds.conf

[myhostname]
    host = myhostname.com
    port = 1433
    tds version = 7.1
    client charset = UTF-8

I've been using 7.1 as it been the best of all so far.

Microsoft also recommend installing the drivers, I did, but even without registering it inside the php.ini file my connection to mssql was working.

Optional: I work with a team of SQL developers so there were a couple of things I had to do in addition to get our database working.

Change the collation

sudo systemctl stop mssql-server
sudo /opt/mssql/bin/mssql-conf set-collation

Enter Latin1_General_CI_AS when prompted, then start MSSQL again

sudo systemctl start mssql-server

Install full-text search

sudo apt-get install -y mssql-server-fts
sudo systemctl restart mssql-server

Hope this helps. It states it a few times in the links above but you need at least 3Gb of memory assigned to install mssql, I have mine currently set to 4Gb and 2cpus, I can be quite slow, and with elasticsearch running too the VM can have a tendency to fall over but's probably due to the intensive nature of everything running on a small VM.

Also, not sure how you feel about downgrading openSSL.

I'm running Homestead v7.1.2 and Vagrant 2.0.3. Let me know if you need anything else.

[craneabove]

@tom-sinclair Hey there, were you connecting a Laravel application to MS SQL on homestead? I've been working at this for better part of 2 weeks and spinning me tires.

[tom-sinclair]

Yeah Laravel 5.5 if it helps. Did you try following the steps in my comment above?

[craneabove]

@tom-sinclair yeah to no avail for some reason. I'll try starting fresh again and see what happens.

[craneabove]

@tom-sinclair So I was successful in installing mssql-server and freetds! I also configured the database host in the freetds config file. However, my connection on the laravel end is unsuccessful. May I ask what you configured in config/database.php and .env files to enable laravel to talk to MS SQL?

[tom-sinclair]

So my database config looks like this:

'sqlsrv' => [
            'driver'   => 'sqlsrv',
            'host'     => env('DB_HOST'),
            'port'     => env('DB_PORT'),
            'database' => env('DB_DATABASE'),
            'username' => env('DB_USERNAME'),
            'password' => env('DB_PASSWORD'),
            'charset'  => 'utf8',
            'prefix'   => 'vw_',
            'schema'   => 'cvSys',
            'options'  => [
                'encrypt'                                  => true,
                'MultipleActiveResultSets'                 => '0',
                // This setting converts unique identifiers from binary to strings
                // Used for GUIDs
                PDO::DBLIB_ATTR_STRINGIFY_UNIQUEIDENTIFIER => true,
                // you might not need this, we have a long running process on one of our jobs
                PDO::ATTR_TIMEOUT                          => 300,
            ],
        ],

In addition, our database tables live within a schema, hence the schema attribute in the config above, and I had to override one method of the grammar to account for this:

// SqlGrammar.php
/**
     * Wrap a table in keyword identifiers.
     *
     * @param  \Illuminate\Database\Query\Expression|string $table
     *
     * @return string
     */
    public function wrapTable($table)
    {
        $table = $this->wrapTableValuedFunction(parent::wrapTable($table));

        $schema = config('database.connections.sqlsrv.schema', 'dbo');
        if (str_contains($table, 'laravel_reserved')) {
            if (str_contains($table, 'as')) {
                $position = strpos($table, ' as');

                $originalTable = substr($table, 0, $position);

                $aliasString = substr($table, $position);

                return implode('.', [$schema, $originalTable]) . $aliasString;
            }

            return $table;
        }

        return implode('.', [$schema, $table]);
    }

And then in the SqlConnection you need to use that custom grammar:

    /**
     * Get the default query grammar instance.
     *
     * @return \Illuminate\Database\Grammar
     */
    protected function getDefaultQueryGrammar()
    {
        return $this->withTablePrefix(new SqlServerGrammar);
    }

Then inside the register method of AppServiceProvider I added this:

Connection::resolverFor('sqlsrv', function ($connection, $database, $prefix, $config) {
            return new \App\Database\SqlServerConnection($connection, $database, $prefix, $config);
        });

You can of course create a new service provider for this to save you AppServiceProvider getting too clogged up.

[tom-sinclair]

If you are getting an Adaptive Server Error, it could be any one of a million reasons for failure, but in my experience it was one of three errors: 1) the permissions of the database user are not set correctly, 2) the host name in your database config doesn't match the host value in the FreeTDS config, 3) the VM or DB ran out of memory - make sure your VM has minimum memory of 4Gb, although this should have been set otherwise you wouldn't have been able to install MSSQL in the first place.

[craneabove]

@tom-sinclair Thank you so much good sir! This helped a ton!

[craneabove]

@tom-sinclair Hi there Tom, sorry again for this, but I was wondering if you could shed any light on the following error I've been running into. I believe that my application is connecting to MS SQL Server I have hosted on AWS, but I receive this error when trying to run a query.

SQLSTATE[HY000] unrecognized msgno (severity 11) (SQL: select top 1 * from [schools] where [id] = 23456)

[tom-sinclair]

I've never had that error message. Which version of FreeTDS are you using? Try changing it around to find the one that best works for you, it might be that changing it will fix your error, I am currently using 7.1, and I think 8.0 resolves back to that version, but I would test which version works best for you, 7.1, 7.2, 7.3 or 8.0, in this blog in the comments, someone mentions switching the version to 8.0 to remove your specific error, but it is dated 2012.

[craneabove]

@tom-sinclair Thanks, that solved it! Just a simple change to 7.2 and we're all set!

Thanks again for all your help!