search

dotnet / SqlClient

Microsoft.Data.SqlClient provides database connectivity to SQL Server for .NET applications.
MIT License
844 stars 282 forks source link

Running EF core migration bundles on linux with Active Directory Default as connection string. #1565

Open MichielCornilleKenze opened 2 years ago

MichielCornilleKenze commented 2 years ago

Executing migration bundle built using Azure Pipelines 

  pool:
     vmImage: ubuntu-latest

Does not work. I expected the migration to be executed as the Service Connections MSI (which has sql permissions)

Instead I get the error:

SharedTokenCacheCredential authentication failed: Persistence check failed. Data was written but it could not be read. Possible cause: on Linux, LibSecret is installed but D-Bus isn't running because it cannot be started over SSH.

Appsettings:

{
    // This file is published together with the EF Core Migration Bundles in DevOps
  "ConnectionStrings": {
    "CONNECTION_STRING": "Server=tcp:XXXX.database.windows.net,1433;Initial Catalog=sqldb-xxxx;Authentication=Active Directory Default"
  }
}

Azure pipelines task to build the bundle

  - task: DotNetCoreCLI@2
            displayName: Build EF Core Migration Bundle
            inputs:
              command: custom
              custom: ef
              arguments: migrations bundle (irrelevant arguments omitted)
          - task: CopyFiles@2
            displayName: Copy EF Core Migration Bundle AppSettings
            inputs:
              Contents: 'src/Migrator/appsettings.json'
              TargetFolder: MigrationBundle
              flattenFolders: true

Azure pipelines task to run the ef bundle output:

              - bash: |
                    chmod +x MigrationBundle
                    ./MigrationBundle --verbose
                  displayName: Deploy Database sqldb-$(projectName)             
                  workingDirectory: $(Agent.BuildDirectory)/drop/MigrationBundle
                  failOnStderr: true

The error is chinese to me, and I don't have control over what is running on the linux hosted agent.

Is this scenario supported or should we use windows agents if we want the service connections MSI?

Minor update: I read https://github.com/Azure/azure-sdk-for-net/issues/17052 but I have no Idea how to apply this workaround to EF Core.

Update: added stack trace from pipeline logs

Microsoft.Data.SqlClient.SqlException (0x80131904): SharedTokenCacheCredential authentication failed: Persistence check failed. Data was written but it could not be read. Possible cause: on Linux, LibSecret is installed but D-Bus isn't running because it cannot be started over SSH.
 ---> Azure.Identity.AuthenticationFailedException: SharedTokenCacheCredential authentication failed: Persistence check failed. Data was written but it could not be read. Possible cause: on Linux, LibSecret is installed but D-Bus isn't running because it cannot be started over SSH.
 ---> Microsoft.Identity.Client.Extensions.Msal.MsalCachePersistenceException: Persistence check failed. Data was written but it could not be read. Possible cause: on Linux, LibSecret is installed but D-Bus isn't running because it cannot be started over SSH.
   at Microsoft.Identity.Client.Extensions.Msal.MsalCacheStorage.VerifyPersistence()
   at Microsoft.Identity.Client.Extensions.Msal.MsalCacheHelper.VerifyPersistence()
   at Azure.Identity.MsalClientBase`1.GetClientAsync(Boolean async, CancellationToken cancellationToken)
   at Azure.Identity.MsalClientBase`1.GetClientAsync(Boolean async, CancellationToken cancellationToken)
   at Azure.Identity.MsalPublicClient.GetAccountsAsync(Boolean async, CancellationToken cancellationToken)
   at Azure.Identity.SharedTokenCacheCredential.GetAccountAsync(Boolean async, CancellationToken cancellationToken)
   at Azure.Identity.SharedTokenCacheCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken)
   --- End of inner exception stack trace ---
   at Azure.Identity.CredentialDiagnosticScope.FailWrapAndThrow(Exception ex)
   at Azure.Identity.SharedTokenCacheCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken)
   at Azure.Identity.SharedTokenCacheCredential.GetTokenAsync(TokenRequestContext requestContext, CancellationToken cancellationToken)
   at Azure.Identity.DefaultAzureCredential.GetTokenFromSourcesAsync(TokenCredential[] sources, TokenRequestContext requestContext, Boolean async, CancellationToken cancellationToken)
   at Azure.Identity.DefaultAzureCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken)
   at Azure.Identity.CredentialDiagnosticScope.FailWrapAndThrow(Exception ex)
   at Azure.Identity.DefaultAzureCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken)
   at Azure.Identity.DefaultAzureCredential.GetTokenAsync(TokenRequestContext requestContext, CancellationToken cancellationToken)
   at Microsoft.Data.SqlClient.ActiveDirectoryAuthenticationProvider.AcquireTokenAsync(SqlAuthenticationParameters parameters)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.<>c__DisplayClass146_1.<<GetFedAuthToken>b__1>d.MoveNext()
--- End of stack trace from previous location ---
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.GetFedAuthToken(SqlFedAuthInfo fedAuthInfo)
   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, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory 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.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.OpenDbConnection(Boolean errorsExpected)
roji commented 2 years ago

Transferred to SqlClient as this doesn't have anything to do with EF Core per se. Looking at the error message, you may want to make sure D-Bus is running.

MichielCornilleKenze commented 2 years ago

@roji I am unsure if that move makes sense, as there is no problem with SQL Client. I'm afraid it might die out here.

I don't have control over whether or not "D-bus" is running. It is the azure hosted build agent that wouldn't have it enabled. My problem is with EF migration bundles generating a bundle that does not work when run on Azure DevOps hosted agents.

So if it needs to be moved I guess it could be moved to some azure-pipelines github...

roji commented 2 years ago

It's true that this isn't necessarily a SqlClient issue either - but it's definitely not an EF one either. At the end of the day it's probably more or "how do I set up D-Bus on the Azure Ubuntu image". Someone from the SqlClient team may have had experience with this, otherwise a stackoverflow question may be more appropriate.

Kaur-Parminder commented 2 years ago

@MichielCornilleKenze I will try to test M.D.S on ubuntu-latest. Meanwhile can you try other ubuntu version except latest or try to launch D-bus. Here is relevant discussion on ubuntu forum https://askubuntu.com/questions/135573/gconf-error-no-d-bus-daemon-running-how-to-reinstall-or-fix.

MichielCornilleKenze commented 2 years ago

I currently worked around this by using "Active Directory Default" , a windows-latest hosted agent, Azure CLI task and letting DefaultAzureCredential use AzureCLI Authentication.

karo-solutions commented 2 years ago

I don't know a lot about Azure pipelines, but I ran into the same issue when executing dotnet-ef database update from a GitLab docker runner.

Steps to reproduce: 

docker run -it mcr.microsoft.com/dotnet/sdk:6.0 /bin/bash
apt update && apt install -y libsecret-1-0
dotnet tool install -g dotnet-ef --version 6.0.5
[copy or mount a project to container for testing purposes]
~/.dotnet/tools/dotnet-ef database update

Results in the same issue:

[...]
SharedTokenCacheCredential authentication failed: Persistence check failed. Data was written but it could not be read. Possible cause: on Linux, LibSecret is installed but D-Bus isn't running because it cannot be started over SSH.

My solution was to set the environment variables for Azure.Identity: https://docs.microsoft.com/en-us/dotnet/api/azure.identity.environmentcredential?view=azure-dotnet

I'm using an App Registration to deploy, so I executed the following commands:

export AZURE_TENANT_ID=XXX
export AZURE_CLIENT_ID=XXX
export AZURE_CLIENT_SECRET=XXX
~/.dotnet/tools/dotnet-ef database update

and it worked. :+1:

I initially thought I can use az to authenticate, which was wrong. Installing the Azure CLI as described here and executing the following resulted in the same issue. az login --service-principal --username XXX --password XXX --tenant XXX

Hope this helps somebody.

SrChronus commented 2 years ago

Is there any progress on this?

nilshjalmarson commented 12 months ago

Hi

not sure if this one is alive but I'm getting the same error on my Mac after build ing a bundle with dotnet ef migrations bundle -r osx-arm64.

Microsoft.Data.SqlClient.SqlException (0x80131904): The operation was canceled. ---> System.Threading.Tasks.TaskCanceledException: The operation was canceled. at System.Net.Http.HttpClient.HandleFailure(Exception e, Boolean telemetryStarted, HttpResponseMessage response, CancellationTokenSource cts, CancellationToken cancellationToken, CancellationTokenSource pendingRequestsCts) at System.Net.Http.HttpClient.gCore|83_0(HttpRequestMessage request, HttpCompletionOption completionOption, CancellationTokenSource cts, Boolean disposeCts, CancellationTokenSource pendingRequestsCts, CancellationToken originalCancellationToken) at Microsoft.Identity.Client.Http.HttpManager.ExecuteAsync(Uri endpoint, IDictionary2 headers, HttpContent body, HttpMethod method, ILoggerAdapter logger, CancellationToken cancellationToken) at Microsoft.Identity.Client.Http.HttpManager.ExecuteWithRetryAsync(Uri endpoint, IDictionary2 headers, HttpContent body, HttpMethod method, ILoggerAdapter logger, Boolean doNotThrow, Boolean retry, CancellationToken cancellationToken) at Microsoft.Identity.Client.Http.HttpManager.SendGetAsync(Uri endpoint, IDictionary2 headers, ILoggerAdapter logger, Boolean retry, CancellationToken cancellationToken) at Microsoft.Identity.Client.OAuth2.OAuth2Client.ExecuteRequestAsync[T](Uri endPoint, HttpMethod method, RequestContext requestContext, Boolean expectErrorsOn200OK, Boolean addCommonHeaders, Func2 onBeforePostRequestData) at Microsoft.Identity.Client.OAuth2.OAuth2Client.DiscoverAadInstanceAsync(Uri endPoint, RequestContext requestContext) at Microsoft.Identity.Client.Instance.Discovery.NetworkMetadataProvider.SendInstanceDiscoveryRequestAsync(Uri authority, RequestContext requestContext) at Microsoft.Identity.Client.Instance.Discovery.NetworkMetadataProvider.FetchAllDiscoveryMetadataAsync(Uri authority, RequestContext requestContext) at Microsoft.Identity.Client.Instance.Discovery.NetworkMetadataProvider.GetMetadataAsync(Uri authority, RequestContext requestContext) at Microsoft.Identity.Client.Instance.Discovery.InstanceDiscoveryManager.FetchNetworkMetadataOrFallbackAsync(RequestContext requestContext, Uri authorityUri) at Microsoft.Identity.Client.Instance.Discovery.InstanceDiscoveryManager.GetMetadataEntryAsync(AuthorityInfo authorityInfo, RequestContext requestContext, Boolean forceValidation) at Microsoft.Identity.Client.AuthorityInfo.AuthorityInfoHelper.IsAuthorityAliasedAsync(RequestContext requestContext, AuthorityInfo requestAuthorityInfo) at Microsoft.Identity.Client.AuthorityInfo.AuthorityInfoHelper.ValidateSameHostAsync(AuthorityInfo requestAuthorityInfo, RequestContext requestContext) at Microsoft.Identity.Client.AuthorityInfo.AuthorityInfoHelper.CreateAuthorityForRequestAsync(RequestContext requestContext, AuthorityInfo requestAuthorityInfo, IAccount account) at Microsoft.Identity.Client.Instance.Authority.CreateAuthorityForRequestAsync(RequestContext requestContext, AuthorityInfo requestAuthorityInfo, IAccount account) at Microsoft.Identity.Client.ClientApplicationBase.CreateRequestParametersAsync(AcquireTokenCommonParameters commonParameters, RequestContext requestContext, ITokenCacheInternal cache) at Microsoft.Identity.Client.ApiConfig.Executors.ClientApplicationBaseExecutor.ExecuteAsync(AcquireTokenCommonParameters commonParameters, AcquireTokenByRefreshTokenParameters refreshTokenParameters, CancellationToken cancellationToken) at Azure.Identity.AbstractAcquireTokenParameterBuilderExtensions.ExecuteAsync[T](AbstractAcquireTokenParameterBuilder`1 builder, Boolean async, CancellationToken cancellationToken) at Azure.Identity.MsalPublicClient.AcquireTokenByRefreshTokenCoreAsync(String[] scopes, String claims, String refreshToken, AzureCloudInstance azureCloudInstance, String tenant, Boolean async, CancellationToken cancellationToken) at Azure.Identity.MsalPublicClient.AcquireTokenByRefreshTokenAsync(String[] scopes, String claims, String refreshToken, AzureCloudInstance azureCloudInstance, String tenant, Boolean async, CancellationToken cancellationToken) at Azure.Identity.VisualStudioCodeCredential.GetTokenImplAsync(TokenRequestContext requestContext, Boolean async, CancellationToken cancellationToken) at Azure.Identity.CredentialDiagnosticScope.FailWrapAndThrow(Exception ex, String additionalMessage) at Azure.Identity.VisualStudioCodeCredential.GetTokenImplAsync(TokenRequestContext requestContext, Boolean async, CancellationToken cancellationToken) at Azure.Identity.VisualStudioCodeCredential.GetTokenAsync(TokenRequestContext requestContext, CancellationToken cancellationToken) at Azure.Identity.DefaultAzureCredential.GetTokenFromSourcesAsync(TokenCredential[] sources, TokenRequestContext requestContext, Boolean async, CancellationToken cancellationToken) at Azure.Identity.DefaultAzureCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken) at Azure.Identity.CredentialDiagnosticScope.FailWrapAndThrow(Exception ex, String additionalMessage) at Azure.Identity.DefaultAzureCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken) at Azure.Identity.DefaultAzureCredential.GetTokenAsync(TokenRequestContext requestContext, CancellationToken cancellationToken) at Microsoft.Data.SqlClient.ActiveDirectoryAuthenticationProvider.AcquireTokenAsync(SqlAuthenticationParameters parameters) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.<>c__DisplayClass147_1.<b1>d.MoveNext() --- End of stack trace from previous location --- at Microsoft.Data.SqlClient.SqlInternalConnectionTds.GetFedAuthToken(SqlFedAuthInfo fedAuthInfo) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.GetFedAuthToken(SqlFedAuthInfo fedAuthInfo) 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, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) 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.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.OpenDbConnection(Boolean errorsExpected) at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(Boolean errorsExpected) at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.<>c__DisplayClass18_0.<Exists>b__0(DateTime giveUp) at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.<>c__DisplayClass12_02.b0(DbContext _, TState s) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func2 operation, Func2 verifySucceeded) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists(Boolean retryOnNotExists) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists() at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists() at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration) at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType) at Microsoft.EntityFrameworkCore.Migrations.Design.MigrationsBundle.ExecuteInternal(String[] args) at Microsoft.EntityFrameworkCore.Migrations.Design.MigrationsBundle.<>c__DisplayClass6_0.b0(String[] args) at Microsoft.DotNet.Cli.CommandLine.CommandLineApplication.Execute(String[] args) at Microsoft.EntityFrameworkCore.Migrations.Design.MigrationsBundle.Execute(String context, Assembly assembly, Assembly startupAssembly, String[] args)

ErikEJ commented 12 months ago

@nilshjalmarson increase the command and connect timeouts