Moving to new server. Does not accept master key

grant_drage · 11 January 2023 18:19

Hello,
I am migrating my Octopus Deploy server and SQL database to a new virtual machine. Its currently running in Azure on Server 2012 R2, I am trying to get this running on Server 2019 with SQL Server 2019 Enterprise.
I have the original server still up and running and available. Followed the steps in the documentation, restored the SQL DB, downloaded and installed the same version of Octopus, drive letters match, same HOME directory, same domain accounts with permissions.
When running through the Octopus Server Wizard it sees the local database instance on the new server, and prompts for the master key which I am copying from the original server. Unfortunately I just keep getting the error “Please enter a valid master key for the existing database”.

I have tried new backups, I have checked the master key using the powershell show-master-key, copied over the folders from the original data folder, ran the wizard as administrator, as the service account which will run the service.

Any further suggestions?

Thanks in advance.
Grant

patrick.smergut · 11 January 2023 18:58

Hi Grant,

Thanks for reaching out, and I’m sorry to hear you’re having issues applying the master key to your new/relocated instance.

When you copied over the folders from the original home folder, was there by chance an OctopusServer.config (e.g. C:\Octopus\OctopusServer.config) file that in that folder? If that’s the case, you may need to remove that from the new server prior to running through the Octopus Wizard, as it holds configuration details for the old instance. We’ve seen this causing similar behavior in the past, so hopefully that’s the issue here.

Let me know what you find, and if that doesn’t help we’ll continue troubleshooting.

Best,
Patrick

grant_drage · 11 January 2023 21:32

Hi Patrick,

Thanks for the suggestion.

The first few times I tried to enter the master key was before I copied over the data folder files. However, I did have the drive letters reversed on the new server. So one disk for the octopus instance and one for databases, but the drive letters were the opposite way round on the new server. I have since made it like for like, but maybe now I have copied the config file over.

I will check and get back to you.

Kind regards
Grant

grant_drage · 12 January 2023 16:08

Hi,

Unfortunately there isn’t an OctopusServer.config file on the new server (actually didnt find one on the old server either). In terms of what I have copied over, these are the Artifacts, Packages and TaskLogs folders from the Data directory.

I have ensured the account running the setup process has admin rights on the server and is able to connect to the Octopus database in SQL. I have also tried to run the setup as the account that will run the Octopus service, again with rights to the server and database, but neither of these worked.

I have checked the display language / keyboard layouts so they match. I have tried copy and paste, and typing the master key manually. This is on a fresh Windows Server 2019 image (comes with WMF 5.1 pre installed, and the Octopus version has .NET runtime bundled anyway).

Can you think of anything else worth trying? I considered changing the master key on the original server and taking another backup over to the new server.

Kind regards
Grant

Scribestar Limited is a company registered in England and Wales. Registered number: 06935972. Registered office: Suite 606, Central Point, 45 Beech Street, London, EC2Y 8AD. This message is private and confidential. If you have received this message in error please remove it from your system. You must not disclose, copy or distribute the contents of this email or any attachments to any other person nor use its contents or the content in any attachments in any way or you may be acting unlawfully.

patrick.smergut · 12 January 2023 18:09

Hey Grant,

Thanks for trying that and reporting back. I’m sorry to hear that didn’t help resolve your issue.

Sorry to ask a basic question, but when you restored the Octopus database backup to the 2019 SQL server, did you use the same service account as with the old database? I ask because we’ve seen that sometimes the account under the restored database needs to be removed and re-added with owner permissions, if it was restored along with the database.

If that doesn’t help, I was wondering if you’re able to create a new Octopus instance via the Octopus Server Wizard? If that works, then you should be able to stop that instance and reconfigure it to point to your database and use the master key via the following Octopus.Server.exe command:

You’ll need the connection string which will look something like the following but with your account details, if it’s not integrated security:

Data Source=(local);Initial Catalog=OctopusDeploy-OctopusServer;Integrated Security=True

This along with the master key should allow you to point that instance to the newly restored database via something like:

C:\Program Files\Octopus Deploy\Octopus\Octopus.Server.exe database --instance="MyInstance" --connectionString "<DB_CONN_STRING>" --masterKey "<MASTER_KEY>"

Where MyInstance is the name of your Octopus server instance and the master key should be exactly as you’re seeing it in the Octopus Manager.

Let me know if that gets you unblocked! If not, we may need to jump on a call to see if we can further troubleshoot this with you.

Best regards,
Patrick

grant_drage · 13 January 2023 22:00

I originally tried using the service account that was included in the restore process. I deleted that login user, and added it back from active directory and reapplied the permissions but that did not work.

I deleted the Octopus database and tried to create a new instance but was getting some errors relating to file paths when creating the new database files. I created the necessary folders and that allowed the wizard to go further but it still failed to completely create the new instance.

I decided to delete the VM and start again from scratch. This is in azure, previsouly I used an image which had SQL pre-installed so this time I created a blank Server 2019 machine and manually installed SQL Server 2019 Enterprise. Restored the database, recreated the service accounts and re-applied the permissions, installed the same octopus version, and again still could not enter the master key.
I then deleted the Octopus database again, tried to create a new instance, and got this error…

Now using your new license. Happy deployments!
Process F:\OctopusDeploy\Octopus.Server.exe in C:\Users.…\Downloads exited with code 0
Error: SQL Error -2146893019 - A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
The certificate chain was issued by an authority that is not trusted.
Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
—> System.ComponentModel.Win32Exception (0x80090325): The certificate chain was issued by an authority that is not trusted.
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParserStateObject.SNIWritePacket(PacketHandle packet, UInt32& sniError, Boolean canAccumulate, Boolean callerHasConnectionLock) at Microsoft.Data.SqlClient.TdsParserStateObject.WriteSni(Boolean canAccumulate) at Microsoft.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode, Boolean canAccumulate) at Microsoft.Data.SqlClient.TdsParser.TdsLogin(SqlLogin rec, FeatureExtension requestedFeatures, SessionData recoverySessionData, FederatedAuthenticationFeatureExtensionData fedAuthFeatureExtensionData) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.Login(ServerInfo server, TimeoutTimer timeout, String newPassword, SecureString newSecurePassword) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool) at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry, SqlConnectionOverrides overrides)
at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
at Microsoft.Data.SqlClient.SqlConnection.Open()
at Octopus.Manager.Server.OctopusConfiguration.SetupWizard.SqlServerExpressConfigurer.TryParseLocalSqlServerServiceName(SqlConnectionStringBuilder connectionString, String& sqlServerServiceName) in ./source/Octopus.Manager.Server/OctopusConfiguration/SetupWizard/SqlServerExpressConfigurer.cs:line 276
at Octopus.Manager.Server.OctopusConfiguration.SetupWizard.InitialSetupWizardModel.GenerateScript()+MoveNext() in ./source/Octopus.Manager.Server/OctopusConfiguration/SetupWizard/InitialSetupWizardModel.cs:line 118
at Octopus.Shared.Util.CommandLineRunner.Execute(IEnumerable1 commandLineInvocations, Action1 debug, Action1 info, Action1 error, Action2 exception) at Octopus.Shared.Util.CommandLineRunner.Execute(IEnumerable1 commandLineInvocations, ILog log)
at Octopus.Manager.Server.Shared.CommonTabs.InstallTab.b__25_0(Object ) in ./source/Octopus.Manager.Server/Shared/CommonTabs/InstallTab.cs:line 79
ClientConnectionId:fdf98699-81d5-4170-a16a-623f6ef65a7d
Error Number:-2146893019,State:0,Class:20
Executable directory is F:\OctopusDeploy
Executable name or full path: F:\OctopusDeploy\Octopus.Server.exe
No user context provided. Running as current user.
Starting F:\OctopusDeploy\Octopus.Server.exe in working directory ‘C:\Users.…\Downloads’ using ‘OEM United States’ encoding running as ‘DOMAIN_ADMIN’ with the same environment variables as the launching process
Deregistered OctopusServer from the database.
Deleted instance: OctopusServer
Process F:\OctopusDeploy\Octopus.Server.exe in C:\Users.…\Downloads exited with code 0

My acccount is a sysadmin in SQL Server and Windows Server Administrator.
Its strange i can restore the database from backup and it seems to connect to it when setting up the instance as its asking for the master key thats in the restored database, however the same user account cannot create a new database or intance.

Happy to have a call about this whenever possible.
Thanks
Grant

patrick.smergut · 13 January 2023 22:18

Hey Grant,

Thanks for getting back to me and sorry to hear you’re still hitting errors when getting the new instance up.

I’m not quite sure what version of Octopus Server you’re on, but it sounds like you might be running into a known issue we’ve logged here: https://github.com/OctopusDeploy/Issues/issues/7501

If that looks like it might be the same issue, you could try the workaround mentioned there to see if it unblocks you:

Change Encrypt to False in the advanced settings on the Database connection window in the installer.
Alternatively, set TrustServerCertificate to True.

Let me know if that gets you one step further, otherwise we can get a call scheduled.

Best regards,
Patrick

grant_drage · 13 January 2023 23:32

I had done some reading and saw something about the TrustServerCertificate setting but I wasn’t able to find it. Can you tell me where it is or how to change it?

The server has a server and client authentication certificate issued from our internal Certificate Authority server which is in the trusted root of the host.

patrick.smergut · 13 January 2023 23:49

Hi Grant,

Sorry, I should have been more specific that these are configuration options when creating the database connection in the Octopus Wizard.

When you’re in the ‘Database’ section of the Octopus Wizard, there’s an ‘Advanced…’ menu at the bottom of the window to further configure the connection:

After clicking that you should see the options referred to in the workaround. It looks like our bug fix was to set the Trust Server Certficate field to True by default, so you might try that first and see if it gets you unblocked:

One of my colleagues found a bit more information regarding this in another GitHub issue, so if you’d like to know more about this configuration you can find it here: https://github.com/OctopusDeploy/Issues/issues/6899

Let me know how it goes!

Best,
Patrick

grant_drage · 18 January 2023 09:17

Thats great, thanks for the info. This sorted out the problem, it accepted the master key and the instance restored as expected.

Really apprciate your help.

system · 18 February 2023 09:17

This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.