> For the complete documentation index, see [llms.txt](https://docs.appstrategy.com/apprules-r-documentation/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.appstrategy.com/apprules-r-documentation/platform/platform-features/system-settings/data-sources/connection-settings/databases/sqlserver.md).

# Microsoft SQL Server

Connects to Microsoft SQL Server (on-premises or hosted on a virtual machine) using the Microsoft SQL client (`Microsoft.Data.SqlClient`). SQL is sent to the server as written, so the full Transact-SQL dialect is available, including stored procedures.

#### Connection Properties

The connection settings are the standard Microsoft SQL client properties. The most commonly used ones are listed below; the other properties shown in the property grid behave as described in the Microsoft `SqlConnectionStringBuilder` documentation.

A new data source starts with **Integrated Security** = `true`, **Multiple Active Result Sets** = `true` and **Trust Server Certificate** = `true`.

**Server**

| Property              | Default     | Notes                                                                                      |
| --------------------- | ----------- | ------------------------------------------------------------------------------------------ |
| Data Source           | —           | Required. Server name, e.g. `SQLSRV01`, `SQLSRV01\INSTANCE` or `sqlsrv01.contoso.com,1433` |
| Initial Catalog       | —           | Database name                                                                              |
| Connect Timeout       | `15`        | Seconds to wait for the connection to open                                                 |
| Command Timeout       | `30`        | Seconds to wait for a statement to complete                                                |
| Application Intent    | `ReadWrite` | `ReadOnly` routes the connection to a readable secondary of an availability group          |
| Multi Subnet Failover | `false`     | Set to `true` when connecting to an availability group listener                            |
| Application Name      | —           | Name shown for the connection in server monitoring tools                                   |

**Authentication**

| Property            | Default | Notes                                                                                           |
| ------------------- | ------- | ----------------------------------------------------------------------------------------------- |
| Integrated Security | `true`  | Windows authentication, using the account that runs appRules. Set to `false` to use a SQL login |
| User ID             | —       | SQL login name                                                                                  |
| Password            | —       | Password of the SQL login                                                                       |

Typical combinations:

| To connect with | Set                                            |
| --------------- | ---------------------------------------------- |
| Windows account | Integrated Security `true` (default)           |
| SQL login       | Integrated Security `false`, User ID, Password |

With Windows authentication, the connection is made as the Windows account running the appRules service, not as the user signed in to the portal. That account needs a login on the SQL Server.

For Azure SQL Database and Microsoft Entra authentication, use the **Azure SQL** data source.

**Encryption**

| Property                 | Default | Notes                                                                                                                                  |
| ------------------------ | ------- | -------------------------------------------------------------------------------------------------------------------------------------- |
| Encrypt                  | `true`  | Encrypt the connection                                                                                                                 |
| Trust Server Certificate | `true`  | `true`: the server certificate is not validated, so servers with a self-signed certificate are accepted. Set to `false` to validate it |
| Host Name In Certificate | —       | Expected host name in the server certificate, when it differs from Data Source                                                         |

**Pooling and Resilience**

| Property                    | Default | Notes                                                    |
| --------------------------- | ------- | -------------------------------------------------------- |
| Pooling                     | `true`  | Reuse database connections between requests              |
| Min Pool Size               | `0`     | Connections kept open in the pool                        |
| Max Pool Size               | `100`   | Maximum connections in the pool                          |
| Multiple Active Result Sets | `true`  | Allows several open result sets on one connection (MARS) |
| Connect Retry Count         | `1`     | Reconnection attempts after a dropped idle connection    |
| Connect Retry Interval      | `10`    | Seconds between reconnection attempts                    |

#### Supported SQL

**Tables and columns**

* Tables and views are addressed as `Table` or `Schema.Table`. Tables in the `dbo` schema are listed without the schema prefix.
* Names containing spaces or special characters, names starting with a digit and reserved words are written in square brackets: `[Order Details]`, `[User]`.
* Primary keys and foreign keys are read from the database.

**SELECT**

Queries are executed by the server as written. Everything Transact-SQL supports is available: joins, sub-queries, `GROUP BY`, aggregate and window functions, `UNION`, common table expressions (`WITH`), `TOP`, `OFFSET … FETCH`, JSON functions, and so on.

```sql
SELECT * FROM Customers WHERE Country = @country ORDER BY CompanyName
SELECT c.CompanyName, COUNT(*) AS Orders
FROM Sales.Orders o JOIN Customers c ON c.CustomerID = o.CustomerID
GROUP BY c.CompanyName
SELECT TOP 50 * FROM Sales.Orders ORDER BY OrderDate DESC
```

**INSERT, UPDATE, DELETE**

```sql
INSERT INTO Customers (CustomerID, CompanyName, Country) VALUES (@id, @name, @country)
UPDATE Customers SET Country = 'IE' WHERE CustomerID = @id
DELETE FROM Customers WHERE CustomerID = @id
```

* Statements are sent unchanged. DDL (`CREATE`, `ALTER`, `DROP`, `TRUNCATE`), `MERGE` and multi-statement batches can also be executed.
* Saving records through a workflow target requires the table to have a primary key (used to locate the row to update or delete).

**Stored procedures**

Stored procedures are supported, with input, output, input/output and return-value parameters. Result sets returned by a procedure are available as records.

**Parameters**

Named parameters are written `@name`.

```sql
SELECT * FROM Sales.Orders WHERE CustomerID = @customer AND OrderDate >= @from
```

**Transactions**

Transactions are supported and are handled by the server.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://docs.appstrategy.com/apprules-r-documentation/platform/platform-features/system-settings/data-sources/connection-settings/databases/sqlserver.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
