Hi,
Should it be possible to authenticate with the Octopus database when it is hosted on Azure and by using Azure Active Directory authentication?
I have read similar topics which suggest it may not be. If this is the case, are we compelled to use explicit credentials to connect to the database?
Thanks for getting in touch!
We added support for AAD authentication for Azure SQL in 2021.1.
Full details on the change made can be found here: Add Azure Managed Service Identity support for Azure SQL authentication ¡ Issue #6738 ¡ OctopusDeploy/Issues ¡ GitHub
Regards,
Paul
I now have connections succeeding with Azure AAD after tweaking the connection string options.
Thank you @paul.calvert
I am mistaken, actually, I am still unable to get it working.
I have followed the online articles, both those published within Octopus documentation and those found through web searches.
Are you receiving a specific error when trying to start the service?
The connection string should look something like this.
Setting database connection string to: server=localhost;initial catalog=Octopus;authentication="Active Directory Managed Identity"
Then the Octopus service would need to be configured to run as a User that has the necessary permissions set by AAD to access the database.
I have been trying connection strings with various different values for the Authentication parameter.
I havent tried âActive Directory Managed Identityâ as yet and will do so today at the next earliest opportunity.
Iâm not convinced the errors logged at this stage would be much help since they seem to suggest connections are being attempted using a specific login:
FATAL SQL Error 18456 - Login failed for user âValidLoginâ.
Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user âValidLoginâ.
at Microsoft.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod(Exception e)
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 Nevermore.Transient.RetryPolicy.<>c__DisplayClass26_0.<ExecuteAction>b__0() at Nevermore.Transient.RetryPolicy.ExecuteAction[TResult](Func1 func)
at Nevermore.Transient.RetryPolicy.ExecuteAction(Action action)
at Nevermore.Transient.DbConnectionExtensions.OpenWithRetry(DbConnection connection, RetryPolicy retryPolicy)
at Nevermore.Transient.DbConnectionExtensions.OpenWithRetry(DbConnection connection)
at Nevermore.Advanced.ReadTransaction.Open()
at Nevermore.Advanced.ReadTransaction.Open(IsolationLevel isolationLevel)
at Nevermore.RelationalStore.BeginWriteTransaction(IsolationLevel isolationLevel, RetriableOperation retriableOperation, String name)
at Nevermore.RelationalStore.BeginTransaction(IsolationLevel isolationLevel, RetriableOperation retriableOperation, String name)
at Octopus.Core.RelationalStorage.RawRelationalStore.BeginTransaction(IsolationLevel isolationLevel, RetriableOperation retriableOperation, String name) in ./source/Octopus.Core/RelationalStorage/RawRelationalStore.cs:line 50
at Octopus.Core.Initialization.BuiltIn.EnsureDatabaseUsesCaseInsensitiveCollationInitializer.Initialize(IRawRelationalStore store) in ./source/Octopus.Core/Initialization/BuiltIn/EnsureDatabaseUsesCaseInsensitiveCollationInitializer.cs:line 13
at Octopus.Core.Initialization.StoreInitializer.Initialize() in ./source/Octopus.Core/Initialization/StoreInitializer.cs:line 20
at Octopus.Server.OctopusServerEngine.Start() in ./source/Octopus.Server/OctopusServerEngine.cs:line 81
at Octopus.Server.Commands.RunCommand.Start() in ./source/Octopus.Server/Commands/RunCommand.cs:line 78
at Octopus.Shared.Startup.AbstractCommand.Start(String commandLineArguments, ICommandRuntime commandRuntime, OptionSet commonOptions)
at Octopus.Shared.Startup.OctopusProgram.Start(ICommandRuntime commandRuntime)
at Octopus.Shared.Startup.WindowsServiceHost.<>c__DisplayClass2_0.b__0()
at Octopus.Shared.Startup.WindowsServiceAdapter.RunService()
ClientConnectionId:4b93abfe-f947-4fb0-815a-6e9ecf0a9edb
Error Number:18456,State:1,Class:14
ClientConnectionId before routing:99848968-144f-468c-bb1d-8c3e9cf0a6d1
Routing Destination:ServerName.worker.database.windows.net,11055
On occasions I have been able to connect from the server via PowerShell using the example from here:
But I was unable to get Octopus authenticating using the same connection string.
After reading the Octopus documentation, I am unsure of whether an Octopus user must exist for the database connection. It seems inappropriate, but still unclear to me in the âOctopus user accounts are still requiredâ paragraph of:
I believe permissions on the Azure database server and database to be correct.
I do have some concerns that potentially outgoing ports might be blocking AAD, but this is difficult to diagnose due to the firewall being managed by a third party.
If you are able to provide details of any known ports, I will ask our security partners to add the appropriate exceptions.
I am also seeing the following warnings every time the server is started:
WARN - FAIL: Files required to successfully run were not found.
To get your Octopus Server back up and running, please re-run the installer for this version of Octopus Server.
WARN If this issue persists, try disabling your anti-virus and re-installing - we sometimes find overzealous anti-virus software can lock or quarantine necessary files.
WARN If neither of these options works, please contact support@octopus.com with a copy of your Octopus Server log files so we can help you get back up and running.
WARN Missing files detected in Octopus directory: (many many many dlls listed)
The listing of the missing files detected is really very long and makes reading the log file somewhat tedious. It would also be good if I was able to remedy these warnings.
Regarding the file missing warnings when the service starts.
What version of Octopus are you currently running?
We have an issue in 2021.3.8275 where the manifest file that Octopus uses to compare the list of the installed files against incorrectly includes the build path (e.g. C:/BuildAgent/work/abb2fbfce959a439/build/outputs/win-x64/server/api-ms-win-core-console-l1-1-0.dll) causing the service start check to think that all of the files are missing.
Ah, right.
Yes, we are running that version 
I have just changed that Authentication parameter and believe the server is now running! 
Perhaps you might like to add that detail to the documentation.
Thank you @paul.calvert
Great to hear that it is working now.
I agree about the documentation. Iâve reached out to our team to see if we can get something added detailing that this is possible and a sample connection string.
This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.