OctopusServer.config - connection string, database user change?

Legacy Operations
1

We plan to migrate the current Octopus database from normal SQL Server instance to Azure SQL database, however currently Windows integrated authentication is not supported by Azure SQL database and our current setup is using domain service account in the connection string.

We are still running version 3.13.3

I am not sure what is the best way to quickly & seamlessly migrate our current Octopus to use Azure SQL database? Is switching the Octopus user to use SQL authentication is possible scenario?

I thought simply changing the connection string to use new SQL Authentication user will work after restoring our existing database into new Azure SQL database, but that does not seem to be the case!?
Here is what I tried:

  1. Put Octopus in Maintenance mode.
  2. Restored Octopus current database backup into the new Azure SQL database
  3. Created new db_owner SQL Server user in newly restored database to use in the connection string in OctopusServer.config
  4. Stopped Octopus service using Octopus.Server service --stop
  5. Updated the connection string to use SQL Authentication with the newly created db user in step 3 above
  6. Started Octopus service using Octopus.Server service --start
    All looks good started the service without errors, however the UI does not load at all! Looked at the Octopus log nothing obvious no errors!
    Before rolling back the config to old connection string I tried: Octopus.Server show-configuration and I got:
    Error: SQL Error 18456 - Login failed for user '<my-sql-user-id>'.
    I did manage to login with the same credentials via the SSMS so I don’t think it’s the credentials

Rolled back the connection string to old database all working normally!

I see Azure SQL database is supported, however not sure changing the authentication scheme in the connection string from Windows integrated to SQL is possible…?

Also I have to mention the above test was very quick - I have not spent much time simply, because the Maintenance window was very short and I had to put Octopus back online. I am just trying to get an approved way to achieve this database change!

3

Hi,
Thanks for getting in touch, I’m sorry to hear you encountered issues while performing an SQL Database migration into Azure.

One possible reason why this is failing here could be a coallation mis-match in SQL Server. I’m interested to know if you have ensured they are the same across the new AzureSQL and the old Windows Server SQL installation. By default, Octopus uses the Latin1_General_CI_AS collation but for more information, please feel free to check out our documentation on the collation of the Octopus database

One other possible reason this might be failing could be how the SQL Database connection string is being modified here. We always recommend to modify these settings by using the Octopus.Server.Exe command line tool.

Based on your version of Octoups, you would need to modify the connection string using this command:

Octopus.Server.exe configure --storageConnectionString="VALUE"

Please feel free to check out or documentation on moving the Octopus Database for your version of Octopus.

I look forward to hearing if this has been helpful! If you’re seeing any further issues please feel free to keep in touch.

Kind regards,
Lawrence.

4

Hi Lawrence,

The database collation was different indeed - thanks for the tip!

I am going to attempt migration again later off working hours and shall let you know.

Meanwhile I’ve been wondering about the “VALUE” syntax of the command to set the connection string above… Would this be the correct syntax for standard security (not trusted connection):

Octopus.Server.exe configure --storageConnectionString="Server=dbServerHost;Database=myDatabase;User Id=myUsername;Password=myPassword;"

Thanks,
Emil

5

Hi Emil,
Thanks for keeping in touch! When I attempted to reproduce this in my own environment, I was able to copy the connection string directly from the Azure portal by navigating to the overview section of the SQL Database resource in Azure.

In the essentials section at the top of the page, under Connection strings you should be able to click the link: Show database connection strings to retrieve it directly from the Azure portal.

You will just need to fill in your SQL username and password in the connection string.

I look forward to hearing if this was helpful.

Kind regards,
Lawrence.

6

Hi Lawrence,

this info was helpful indeed :slight_smile:

I can confirm the only trouble that I had was to do with the different collation in the current Octopus database and the restored database in SQL Azure.
Once the collation was corrected, it did work right away just by pointing to the newly migrated Octopus database in Azure via the connection string in OctopusServer.config

Many Thanks for all your help!
Emil

7

Hi,
Thanks for keeping in touch and letting us know how you resolved this one!

Happy deployments!

Kind regards,
Lawrence.