search

microsoft / durabletask-mssql

Microsoft SQL storage provider for Durable Functions and the Durable Task Framework
MIT License
87 stars 32 forks source link

Connection String question #196

Closed SeaDude closed 1 year ago

SeaDude commented 1 year ago

I'd like to use durabletask-mssql for a python Durable Azure Function. Been following the well-written docs here but I'm stuck on one piece.

I've created an Azure SQL Server and I'd like to run the Durable Azure Function with the local.settings.json set to...

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "UseDevelopmentStorage=true",
    "FUNCTIONS_WORKER_RUNTIME": "python",
    "SQLDB_Connection": "Server=tcp:<my-azure-sql-server>.database.windows.net,1433;Initial Catalog=<my-azure-sql-db>;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Managed Identity;"
  }
}

...and the host.json set to:

{
  "version": "2.0",
  "logging": {
    "applicationInsights": {
      "samplingSettings": {
        "isEnabled": false,
        "excludedTypes": "Request"
      }
    }
  },
  "extensionBundle": {
    "id": "Microsoft.Azure.Functions.ExtensionBundle",
    "version": "[4.*, 5.0.0)"
  },
  "extensions": {
    "durableTask": {
      "storageProvider": {
        "type": "mssql",
        "connectionStringName": "SQLDB_Connection",
        "taskEventLockTimeout": "00:02:00",
        "createDatabaseIfNotExists": true,
        "schemaName": "dt"
      }
    }
  }
}

...so that the database is automatically created.

But I don't know what to use for the SQLDB_Connection value. I don't see Connection Strings in the Portal for an Azure SQL Server. Only if a DB is added do I see a ConnectionString blade.

Here is a sample:

Server=tcp:<my-azure-sql-server>.database.windows.net,1433;
Initial Catalog=<my-azure-sql-db>;
Encrypt=True;
TrustServerCertificate=False;
Connection Timeout=30;
Authentication=Active Directory Managed Identity;

Questions:

  1. What is the Connection String to use in order to have the DurableDB setup automatically?
  2. How do I define the SKU to use for DurableDB?

Thank you!

SeaDude commented 1 year ago

Anyone have thoughts on this one?

SeaDude commented 1 year ago

I'd like to use this tool to track Azure Durable Function state.

cgillum commented 1 year ago

Hi @SeaDude. I haven't tried this exact scenario when using Azure Managed Identities, but the intent is that you specify the name of the database that you want to be created in your connection string. When the app starts up, it will first connect to the master database and will then create the target database (from the connection string) if it doesn't yet exist. Have you tried this yet, and what was the outcome?

SeaDude commented 1 year ago

Thank you for the suggestion. The question I have is, what is the Connection String to use? I'm using Azure SQL and there doesn't seem to be a default ConnString to use to access the server/master database.

cgillum commented 1 year ago

Does this help? https://learn.microsoft.com/en-us/azure/azure-sql/database/connect-query-content-reference-guide?view=azuresql#get-adonet-connection-information-optional---sql-database-only. Just replace the server name with your server name and the database name with master?

SeaDude commented 1 year ago

@cgillum

  1. I added SQLDB_Connection: "Server=tcp:<my-server>.database.windows.net,1433;Initial Catalog=sqldb-durable-function;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Managed Identity;" connection string to local.settings.json.
  2. I assigned the Contributor RBAC role to the Function App's System-Assigned Managed Identity (SAMI)
  3. Then published the local Function to the Azure Function App

I'm not sure where to go from there. There was nothing in the func publish logs stating that the database was being built. Remote build succeeded! is what I received and the Function is present in Azure.

Do you know how I should debug the auto-db creation?

cgillum commented 1 year ago

Database creation should be happening when the app starts up for the first time, not during deployment. Is your app starting up successfully? If not, are there any errors in the logs that might indicate whether a problem occurred?

SeaDude commented 1 year ago

Hello @cgillum,

I the Function has been deployed to the Function App. I triggered the app, and now I get an exception loop...

Here is the App Setting that is deployed:

Exception that is pulled from Azure Function Live Metrics:

Recurring exception (every few minutes):

Time    9:25:31 PM
Exception type  Microsoft.Azure.WebJobs.Host.Listeners.FunctionListenerException
Exception message   The listener for function 'Functions.orchestrator' was unable to start. <--- CREATE DATABASE permission denied in database 'master'.
Category    Host.Startup
FormattedMessage    The listener for function 'Functions.orchestrator' was unable to start.
HostInstanceId  5bf59404-e91a-4b23-a719-2d27016ee832
LogLevel    Error
ProcessId   24
prop__{OriginalFormat}  The listener for function 'Functions.orchestrator' was unable to start.
Microsoft.Azure.WebJobs.Host.Listeners.FunctionListenerException: The listener for function 'Functions.orchestrator' was unable to start.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): CREATE DATABASE permission denied in database 'master'.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location ---
   at DurableTask.SqlServer.SqlUtils.WithRetry[T](Func`1 func, SprocExecutionContext context, LogHelper traceHelper, String instanceId, Int32 maxRetries) in /_/src/DurableTask.SqlServer/SqlUtils.cs:line 497
   at DurableTask.SqlServer.SqlUtils.WithRetry[T](Func`1 func, SprocExecutionContext context, LogHelper traceHelper, String instanceId, Int32 maxRetries) in /_/src/DurableTask.SqlServer/SqlUtils.cs:line 504
   at DurableTask.SqlServer.SqlUtils.ExecuteSprocAndTraceAsync[T](DbCommand command, LogHelper traceHelper, String instanceId, Func`2 executor) in /_/src/DurableTask.SqlServer/SqlUtils.cs:line 439
   at DurableTask.SqlServer.SqlDbManager.CreateDatabaseAsync(String databaseName, SqlConnection connection) in /_/src/DurableTask.SqlServer/SqlDbManager.cs:line 208
   at DurableTask.SqlServer.SqlDbManager.EnsureDatabaseExistsAsync() in /_/src/DurableTask.SqlServer/SqlDbManager.cs:line 187
   at DurableTask.SqlServer.SqlDbManager.AcquireDatabaseLockAsync(Boolean createDatabaseIfNotExists) in /_/src/DurableTask.SqlServer/SqlDbManager.cs:line 137
   at DurableTask.SqlServer.SqlDbManager.CreateOrUpgradeSchemaAsync(Boolean recreateIfExists) in /_/src/DurableTask.SqlServer/SqlDbManager.cs:line 33
   at Microsoft.Azure.WebJobs.Extensions.DurableTask.DurableTaskExtension.StartTaskHubWorkerIfNotStartedAsync() in D:\a\_work\1\s\src\WebJobs.Extensions.DurableTask\DurableTaskExtension.cs:line 1429
   at Microsoft.Azure.WebJobs.Host.Listeners.FunctionListener.StartAsync(CancellationToken cancellationToken, Boolean allowRetry) in D:\a\_work\1\s\src\Microsoft.Azure.WebJobs.Host\Listeners\FunctionListener.cs:line 68
ClientConnectionId:f837b601-820e-4cb4-b6f4-b6ee649f261f
Error Number:262,State:1,Class:14
ClientConnectionId before routing:89436671-2a21-4d2e-aeff-72557ef2ab00
Routing Destination:cbe7cd08f62a.tr11103.westus2-a.worker.database.windows.net,11000
   --- End of inner exception stack trace ---

I've already created the Managed Identity USER in the master database...

CREATE USER [nameofapplication] FROM EXTERNAL PROVIDER;

...but each time I try to add the USER to the db_owner role, I get the error shown, even though I'm logged in as the SQL Admin. I also tried logging into the server as the SQL AAD Admin. Same error:

ALTER ROLE db_owner ADD MEMBER [nameofapplication];

Msg 15151, Level 16, State 1, Line 1
Cannot alter the role 'db_owner', because it does not exist or you do not have permission.
cgillum commented 1 year ago

Hi @SeaDude - thanks for providing all this information. Based on the error messages you're seeing, the issue seems to be related to permissions. Does anything in the following documentation provide hints about what might be wrong? https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-transact-sql?view=azuresqldb-current&preserve-view=true&tabs=sqlpool#permissions-1. This part seems relevant:

To create a database, the user login must be one of the following principals:

  • The server-level principal login
  • The Azure AD administrator for the local Azure SQL Server
  • A login that is a member of the dbmanager database role
SeaDude commented 1 year ago

Thanks for the continued engagement @cgillum. I was able to successfully execute ALTER ROLE dbmanager ADD MEMBER [my-managed-identity]. Looks like the database is now being created!! image

SeaDude commented 1 year ago

I think this part of the docs need to be updated (maybe?) to read dbmanager instead of db_owner.

image

Thanks again for the assistance!

Nice!

image

SeaDude commented 1 year ago

Don't forget to change the db Compute + Storage! The auto-deploy creates a General Purpose instance by default. Changed to the Basic instance for development.