SQL Server

Type: state.sqlserver

Status: stable

Reference: https://docs.dapr.io/reference/components-reference/supported-state-stores/setup-sqlserver/

Example

apiVersion: cra.diagrid.io/v1beta1
kind: Component
metadata:
  name: <name>
spec:
  type: state.sqlserver
  version: v1
  metadata:
    # The connection string used to connect. If the connection string contains the database, it must already exist. Otherwise, if the database is omitted, a default database named "Dapr" is created.
    - name: connectionString
      value: "Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;Password=myPassword;"
    # Interval, in seconds, to clean up rows with an expired TTL. Default: 3600 (i.e. 1 hour). Setting this to values `<=0` disables the periodic cleanup. (Optional)
    #- name: cleanupInterval
    #  value: "3600"
    # Use this state store as the Workflows Store, it will enable the Workflows API. Defaults to `false`. (Optional)
    #- name: enableWorkflow
    #  value: "false"
    # List of indexed properties, as a string containing a JSON document. (Optional)
    #- name: indexedProperties
    #  value: "'[{column: transactionid, property: id, type: int}, {column: customerid, property: customer, type: nvarchar(100)}]'"
    # The max length of key. Ignored if "keyType" is not `string`. (Optional)
    #- name: keyLength
    #  value: "200"
    # Prefix added to keys in the state store. (Optional)
    #- name: keyPrefix
    #  value: "appid"
    # The type of key used (Optional)
    #- name: keyType
    #  value: "string"
    # Name of the table Dapr uses to store metadata properties. (Optional)
    #- name: metadataTableName
    #  value: "dapr_metadata"
    # By setting outboxDiscardWhenMissingState to true, Dapr discards the transaction if it cannot find the state in the database and does not retry. This setting can be useful if the state store data has been deleted for any reason before Dapr was able to deliver the message and you would like Dapr to drop the items from the pub/sub and stop retrying to fetch the state (Optional)
    #- name: outboxDiscardWhenMissingState
    #  value: "false"
    # For outbox. Sets the name of the pub/sub component to deliver the notifications when publishing state changes (Optional)
    #- name: outboxPublishPubsub
    #  value: ""
    # For outbox. Sets the topic that receives the state changes on the pub/sub configured with "outboxPublishPubsub". The message body will be a state transaction item for an insert or update operation (Optional)
    #- name: outboxPublishTopic
    #  value: ""
    # For outbox. Sets the pub/sub component used by Dapr to coordinate the state and pub/sub transactions. If not set, the pub/sub component configured with "outboxPublishPubsub" is used. This is useful if you want to separate the pub/sub component used to send the notification state changes from the one used to coordinate the transaction (Optional)
    #- name: outboxPubsub
    #  value: "outboxPublishPubsub"
    # The schema to use. (Optional)
    #- name: schemaName
    #  value: "dbo"
    # The name of the table to use. Alpha-numeric with underscores. (Optional)
    #- name: tableName
    #  value: "state"

Authentication profiles

Available authentication profiles:

Connection string
Azure AD: Client credentials
Azure AD: Client certificate

Connection string

Authenticates using a connection string

`connectionString`

Required - The connection string used to connect. If the connection string contains the database, it must already exist. Otherwise, if the database is omitted, a default database named "Dapr" is created.

Example value: Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;Password=myPassword;

Azure AD: Client credentials

Authenticate using Azure AD with client credentials, also known as "service principals".

`azureClientId`

Required - Client ID (application ID)

Example value: c7dd251f-811f-4ba2-a905-acd4d3f8f08b

`azureClientSecret`

Required - Client secret (application password)

Example value: Ecy3XG7zVZK3/vl/a2NSB+a1zXLa8RnMum/IgD0E

`azureTenantId`

Required - ID of the Azure AD tenant

Example value: cd4b2887-304c-47e1-b4d5-65447fdd542a

`connectionString`

Required - The connection string or URL of the Azure SQL database, without credentials. If the connection string contains the database, it must already exist. Otherwise, if the database is omitted, a default database named "Dapr" is created.

Example value: sqlserver://myServerName.database.windows.net:1433?database=myDataBase

`useAzureAD` (bool)

Required - Must be set to true to enable the component to retrieve access tokens from Azure AD. This authentication method only works with Azure SQL databases.

Default value: true

Example value: true

`azureEnvironment`

Optional name for the Azure environment if using a different Azure cloud

Default value: AzurePublicCloud

Example value: AzurePublicCloud

Allowed values:

AzurePublicCloud
AzureChinaCloud
AzureUSGovernmentCloud

Azure AD: Client certificate

Authenticate using Azure AD with a client certificate. "azureCertificate" is required.

`azureClientId`

Required - Client ID (application ID)

Example value: c7dd251f-811f-4ba2-a905-acd4d3f8f08b

`azureTenantId`

Required - ID of the Azure AD tenant

Example value: cd4b2887-304c-47e1-b4d5-65447fdd542a

`connectionString`

Example value: sqlserver://myServerName.database.windows.net:1433?database=myDataBase

`useAzureAD` (bool)

Required - Must be set to true to enable the component to retrieve access tokens from Azure AD. This authentication method only works with Azure SQL databases.

Default value: true

Example value: true

`azureCertificate`

Certificate and private key (in either a PEM file containing both the certificate and key, or in PFX/PKCS#12 format)

Example value:

-----BEGIN PRIVATE KEY-----\n MIIEvgI... \n -----END PRIVATE KEY-----
\n -----BEGIN CERTIFICATE----- \n MIICoTC... \n -----END CERTIFICATE----- \n

`azureCertificatePassword`

Password for the certificate if encrypted.

Example value: password

`azureEnvironment`

Optional name for the Azure environment if using a different Azure cloud

Default value: AzurePublicCloud

Example value: AzurePublicCloud

Allowed values:

AzurePublicCloud
AzureChinaCloud
AzureUSGovernmentCloud

Metadata

`cleanupInterval` (number)

Interval, in seconds, to clean up rows with an expired TTL. Default: 3600 (i.e. 1 hour). Setting this to values <=0 disables the periodic cleanup.

Default value: 3600

Example value: 1800, -1

`enableWorkflow` (bool)

Use this state store as the Workflows Store, it will enable the Workflows API. Defaults to false.

Example value: false

`indexedProperties`

List of indexed properties, as a string containing a JSON document.

Example value: '[{column: transactionid, property: id, type: int}, {column: customerid, property: customer, type: nvarchar(100)}]'

`keyLength` (number)

The max length of key. Ignored if "keyType" is not string.

Default value: 200

Example value: 200

`keyPrefix` (string)

Prefix added to keys in the state store.

Default value: appid

Example value: appid

Allowed values:

appid
name
none

Documentation: https://docs.dapr.io/developing-applications/building-blocks/state-management/howto-share-state/

`keyType`

The type of key used

Default value: string

Example value: string

Allowed values:

string
uuid
integer

`metadataTableName`

Name of the table Dapr uses to store metadata properties.

Default value: dapr_metadata

Example value: dapr_metadata

`outboxDiscardWhenMissingState` (bool)

By setting outboxDiscardWhenMissingState to true, Dapr discards the transaction if it cannot find the state in the database and does not retry. This setting can be useful if the state store data has been deleted for any reason before Dapr was able to deliver the message and you would like Dapr to drop the items from the pub/sub and stop retrying to fetch the state

Default value: false

`outboxPublishPubsub` (string)

For outbox. Sets the name of the pub/sub component to deliver the notifications when publishing state changes

`outboxPublishTopic` (string)

For outbox. Sets the topic that receives the state changes on the pub/sub configured with "outboxPublishPubsub". The message body will be a state transaction item for an insert or update operation

`outboxPubsub` (string)

For outbox. Sets the pub/sub component used by Dapr to coordinate the state and pub/sub transactions. If not set, the pub/sub component configured with "outboxPublishPubsub" is used. This is useful if you want to separate the pub/sub component used to send the notification state changes from the one used to coordinate the transaction

Default value: outboxPublishPubsub

`schemaName`

The schema to use.

Default value: dbo

Example value: dapr

`tableName`

The name of the table to use. Alpha-numeric with underscores.

Default value: state

Example value: table_name

SQL Server

Example​

Authentication profiles​

Connection string​

connectionString​

Azure AD: Client credentials​

azureClientId​

azureClientSecret​

azureTenantId​

connectionString​

useAzureAD (bool)​

azureEnvironment​

Azure AD: Client certificate​

azureClientId​

azureTenantId​

connectionString​

useAzureAD (bool)​

azureCertificate​

azureCertificatePassword​

azureEnvironment​

Metadata​

cleanupInterval (number)​

enableWorkflow (bool)​

indexedProperties​

keyLength (number)​

keyPrefix (string)​

keyType​

metadataTableName​

outboxDiscardWhenMissingState (bool)​

outboxPublishPubsub (string)​

outboxPublishTopic (string)​

outboxPubsub (string)​

schemaName​

tableName​

Example

Authentication profiles

Connection string

`connectionString`

Azure AD: Client credentials

`azureClientId`

`azureClientSecret`

`azureTenantId`

`connectionString`

`useAzureAD` (bool)

`azureEnvironment`

Azure AD: Client certificate

`azureClientId`

`azureTenantId`

`connectionString`

`useAzureAD` (bool)

`azureCertificate`

`azureCertificatePassword`

`azureEnvironment`

Metadata

`cleanupInterval` (number)

`enableWorkflow` (bool)

`indexedProperties`

`keyLength` (number)

`keyPrefix` (string)

`keyType`

`metadataTableName`

`outboxDiscardWhenMissingState` (bool)

`outboxPublishPubsub` (string)

`outboxPublishTopic` (string)

`outboxPubsub` (string)

`schemaName`

`tableName`