Unable to use Azure Active Directory (AAD) in Azure SQL

rrosiek · 31 August 2022 23:48

I’m attempting to migrate our Azure SQL Octopus DB to a new Azure SQL instance using only Azure AD authentication.

After reading through this information, it seemed possible. However whether I’m using Active Directory Password or Active Directory Managed Identity, I seem to get the same error. The command line also says it’s SQL Server Authentication which isn’t correct. The connection is updated correctly in config file, so that part works, but the Octopus server does not start.

This command outputs the following with the stack trace:

Octopus.Server.exe database --connectionString="..."
Setting database connection string to: "server=tcp:db.database.windows.net,1433;initial catalog=octopus;persist security info=False;user id=oct@x.onmicrosoft.com;password=********;multipleactiveresultsets=False;encrypt=True;trustservercertificate=False;authentication=\"Active Directory Password\"" 

========== SQL Database Connection Details ======== 

"SQL Server: tcp:db.database.windows.net,1433 

SQL Database: octopus 

Authentication Type: SQL Server Credentials 

User: oct@x.onmicrosoft.com 

" 

=================================================== 

Object reference not set to an instance of an object. 

System.NullReferenceException: Object reference not set to an instance of an object. 

   at Microsoft.Data.SqlClient.TdsParser.SendFedAuthToken(SqlFedAuthToken fedAuthToken) 

   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OnFedAuthInfo(SqlFedAuthInfo fedAuthInfo) 

   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream 

, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) 

   at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, B 

ulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) 

   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) 

   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Secur 

eString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) 

   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Secur 

eString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credenti 

al, TimeoutTimer timeout) 

   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connec 

tionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstanc 

e) 

   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString con 

nectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boole 

an redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTra 

nsientFaultHandling, String accessToken, DbConnectionPool pool) 

   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey po 

olKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptio 

ns) 

   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningO 

bject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) 

   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptio 

ns, DbConnectionInternal oldConnection) 

   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions user 

Options, DbConnectionInternal oldConnection) 

   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObj 

ectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& 

connection) 

   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 re 

try, DbConnectionOptions userOptions, DbConnectionInternal& connection) 

   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSour 

ce`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) 

   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnect 

ionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) 

   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory 

connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) 

   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 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](Func`1 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 retriableOperati 

on, String name) 

   at Nevermore.RelationalStore.BeginTransaction(IsolationLevel isolationLevel, RetriableOperation retriableOperation, S 

tring name) 

   at Octopus.Core.RelationalStorage.RawRelationalStore.BeginTransaction(IsolationLevel isolationLevel, RetriableOperati 

on retriableOperation, String name) in ./source/Octopus.Core/RelationalStorage/RawRelationalStore.cs:line 48 

   at Octopus.Core.Initialization.BuiltIn.EnsureDatabaseUsesCaseInsensitiveCollationInitializer.Initialize(IRawRelationa 

lStore store) in ./source/Octopus.Core/Initialization/BuiltIn/EnsureDatabaseUsesCaseInsensitiveCollationInitializer.cs:l 

ine 12 

   at Octopus.Core.Initialization.StoreInitializer.Initialize() in ./source/Octopus.Core/Initialization/StoreInitializer 

.cs:line 19 

   at Octopus.Server.Commands.DatabaseCommand.Start() in ./source/Octopus.Server/Commands/DatabaseCommand.cs:line 68 

   at Octopus.Shared.Startup.AbstractCommand.Start(String[] commandLineArguments, ICommandRuntime commandRuntime, Option 

Set commonOptions) in ./source/Octopus.Shared/Startup/AbstractCommand.cs:line 100 

   at Octopus.Shared.Startup.OctopusProgram.Start(ICommandRuntime commandRuntime) in ./source/Octopus.Shared/Startup/Oct 

opusProgram.cs:line 500 

   at Octopus.Shared.Startup.ConsoleHost.Run(Action`1 start, Action shutdown) in ./source/Octopus.Shared/Startup/Console 

Host.cs:line 34 

   at Octopus.Shared.Startup.OctopusProgram.RunHost(ICommandHost host) in ./source/Octopus.Shared/Startup/OctopusProgram 

.cs:line 217 

   at Octopus.Shared.Startup.OctopusProgram.Run() in ./source/Octopus.Shared/Startup/OctopusProgram.cs:line 168 

"------------------------------------------------------------------------------- 

Terminating process with exit code 100 

Full error details are available in the log files at: 

E:\Octopus\Logs 

If you need help, please send these log files to https://octopus.com/support 

------------------------------------------------------------------------------- 
"

I checked the logs in E:\Octopus\Logs, but it doesn’t offer too much:

2022-08-31 19:46:43.7855 12728 1 INFO ==== CheckServicesCommand ====
2022-08-31 19:46:43.7855 12728 1 INFO CommandLine: C:\Program Files\Octopus Deploy\Octopus\Octopus.Server.dll checkservices --instances OctopusServer

Is there something I need to do different with the connection string?

Daniel_Fischer · 1 September 2022 03:44

Hi Ryan,

Thanks for getting in touch! I have some ideas to better narrow down the possible causes of this issue. From what I can see in the error, it appears that your Octopus server isn’t receiving the auth token from AAD when attempting to authenticate with the SQL Database.

There are a handful of reasons this could be occurring and from the information available. Below I have some troubleshooting steps you can follow which should help identify what’s going wrong.

The error could be interpreted as the SQL server failing to retrieve the Auth token from the AD FS. I have a couple of ways you could test this.

The first thing that comes to mind relates to the configuration of your AD FS and SQL Database server. Would you be able to check in Azure to confirm that a route has been configured between these servers?
Remove Octopus from the equation and see what happens. Octopus has a lot of moving parts and sometimes it causes unexpected issues. The best way to begin narrowing down the cause of your potential auth issue is to by removing Octopus as a variable and attempting to connect to your SQL DB via script or console.
- Using the same account that runs the Octopus Server’s service, attempt to connect to the SQL database with the connection string used by Octopus.
- The following Microsoft docs has different code samples for testing the connection to your SQL server.

Example:

// Use your own server, database, user ID, and password.
string ConnectionString = @"Server=demo.database.windows.net; Authentication=Active Directory Password; Database=testdb; User Id=user@domain.com; Password=***";

using (SqlConnection conn = new SqlConnection(ConnectionString)) {
    conn.Open();
}

The above two options are the first things that come to mind and probably the best place to start your troubleshooting. However, while looking into this error I found some different possible causes. Without a detailed knowledge of your setup and environment, we can’t easily rule these options out, so I’ll post them below with some thoughts so if the above steps one and two don’t help, you’ll have some further leads to investigate.

Connection string authentication methods. You mention that both Active Directory Password and Active Directory Managed Identity both returned the same error. Do you see the same result when you use Active Directory Integrated?
Can you confirm that the SQL server configured with TLS 1.2?
Does the user account have MFA configured?
Similar issue I found from December 2021. It suggests using an older version (1.1.4) of Microsoft.Data.SQLClient as a possible fix.

Again, my information about your environment is limited, so I’m hoping there are enough ideas above to help get the ball rolling at least. If none of the above options help you get this working, I think the next step would be to grab a copy of your log files and possibly schedule a screenshare to investigate in person.

If you have any further thoughts or questions here, please don’t hesitate to let me know.

Best regards,
Daniel

rrosiek · 1 September 2022 12:04

Thanks Daniel, I’ve only scanned through your response and see a lot of great information to help me track this down, so I just wanted to acknowledge that because it may take me a little time to step through everything and narrow down the issue.

I do have another connection to the same server, from the same server, using a user configured in the same way that is working, however that is with TeamCity (JDBC) which is obviously a different animal. With that functioning I think I just expected the .NET connection to work, which is naive given the complexity of these connections.

I’ll dig into this a bit more with what you provided and post back; thanks again!

rrosiek · 8 September 2022 11:44

Thanks you again for the feedback, fortunately I was able to figure this out. It ended up being the account used needed a new password set…sigh. Apparently when you create a user in Azure as an admin, and even set the password manually, the new user needs to login and set their own password. There’s probably a better way to go about that, but that was my auth problem and just wasn’t getting an obvious error returned.

For others, this is the command I used to set the connection. It would also work with the managed service identity.

Octopus.Server.exe database --connectionString='Server=tcp:db1.database.windows.net,1433;Initial Catalog=octopus;Persist Security Info=False;User ID=mssql.octopus@domain.com;Password=passwd;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication="Active Directory Password"'

clare.martin · 8 September 2022 11:48

Hey @rrosiek,

Great news you solved the issue, Daniel will be pleased, I will let him know.
Thank you for posting up the resolution too as this will help other users having this issue.

If there is anything you need in future please don’t hesitate to reach out.

Kind Regards,

Clare