# 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: v2
  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;"
    # Use this state store for actors. Defaults to `false`. (Optional)
    #- name: actorStateStore
    #  value: "false"
    # Maximum number of keys included in each internal SQL query issued by BulkGet. When the number of requested keys exceeds this value, BulkGet issues multiple chunked queries sequentially (not in parallel) and merges the results. This avoids SQL Server's 2100-parameter limit and reduces connection-pool pressure. Values `<=0` default to 1000; values above 2000 are clamped to 2000. (Optional)
    #- name: bulkGetChunkSize
    #  value: "1000"
    # 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"
    # List of indexed properties, as a string containing a JSON document. This will apply only to String data (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`

**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`

#### `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

### `actorStateStore` (bool)

Use this state store for actors. Defaults to `false`.

Example value: `false`

### `bulkGetChunkSize` (number)

Maximum number of keys included in each internal SQL query issued by BulkGet. When the number of requested keys exceeds this value, BulkGet issues multiple chunked queries sequentially (not in parallel) and merges the results. This avoids SQL Server's 2100-parameter limit and reduces connection-pool pressure. Values `<=0` default to 1000; values above 2000 are clamped to 2000.

Default value: `1000`

Example value: `500`

### `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`

### `indexedProperties`

List of indexed properties, as a string containing a JSON document. This will apply only to String data

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`