# SQL Server Connection Strings

> The SQL Server connection strings API Maker takes - the keys it reads, encryption and certificates, named instances, Azure SQL, examples, how to add one to an instance, and the common errors.

Source: https://docs.apimaker.dev/v4/docs/Database-connection-string/sqlserver-connection-strings.html

## The format

```text
Server=host,port;Database=name;User Id=user;Password=password;Encrypt=True;TrustServerCertificate=False;
```

| Key | What it is |
|---|---|
| `Server` (or `Data Source`) | `host,port`, or `host\instance` for a named instance. `1433` when no port is given. |
| `Database` (or `Initial Catalog`) | Optional : the database a connection starts in. |
| `User Id`, `Password` | A login of SQL Server. API Maker signs in with a user and a password : Windows sign-in is not used. |
| `Encrypt` | `True` (the default) : the whole connection is encrypted. `False` : only the sign-in is. |
| `TrustServerCertificate` | `True` : the certificate of the server is taken without a check, as with the self-signed certificate of a new SQL Server. `False` (the default) : it is checked. |
| `Connection Timeout` | Seconds to connect, `15` by default. |
| `ApplicationIntent=ReadOnly` | The connection only reads : an Always On group sends it to a readable secondary. |

## Examples

```text
# 1. SQL Server on the same server, with its self-signed certificate
Server=127.0.0.1,1433;User Id=sa;Password=yourStrong(!)Password;TrustServerCertificate=True;

# 2. A database to start in
Server=10.0.0.5,1433;Database=inventory;User Id=app;Password=password;TrustServerCertificate=True;

# 3. A named instance
Server=10.0.0.5\SQLEXPRESS;Database=inventory;User Id=app;Password=password;TrustServerCertificate=True;

# 4. Azure SQL, its certificate checked
Server=myserver.database.windows.net,1433;Database=inventory;User Id=app;Password=password;Encrypt=True;

# 5. A readable secondary of an Always On group
Server=listener.example.com,1433;Database=inventory;User Id=app;Password=password;ApplicationIntent=ReadOnly;
```

## Add it to API Maker

An instance never holds the connection string itself : it names a key of your [default secret](https://docs.apimaker.dev/v4/docs/secrets/secrets.html), where the string stays encrypted, out of Git.

1. **Put the string in the secret** **Security → Secret Management** → `Default`. Add it under `common.connectionString`, with a name of your choice, and save.
2. **Create the instance** **APIs & Logic → Instance API (DB API)** → add an instance : pick the database type, give it a name, and pick the key of the secret under **Connection**.
3. **Test it** **Test Instance** connects with the string. An instance which can not reach its database is not saved, and the form says why.

**Connection String Sandbox** is optional : a second key, for code which runs in a sandbox and reaches the database by another address than API Maker does.

## Common errors

| What you see | What to check |
|---|---|
| `Login failed for user 'app'` | The login and the password, and that the server accepts SQL Server sign-in (mixed mode). |
| A certificate error | A server with a self-signed certificate needs `TrustServerCertificate=True`, or a certificate API Maker can check. |
| A timeout | TCP/IP is enabled in the configuration of SQL Server, the firewall lets API Maker in, the port is right. |
| A named instance can not be found | The SQL Server Browser service runs and UDP port `1434` is open, or give the port in place of the name : `Server=10.0.0.5,1433`. |

## Related

- [Secret Management](https://docs.apimaker.dev/v4/docs/secrets/secrets.html) · [Execute plain query](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-execute-plain-query-api.html) · [Connection strings of SQL Server](https://learn.microsoft.com/sql/connect/ado-net/connection-string-syntax)