# SQL Server Connection Strings in API Maker

> Format SQL Server connection strings easily:- include server, credentials, trusted connection, and escape special chars like @.

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

## Introduction

When connecting SQL Server to API Maker, the connection string defines how your API Maker instance communicates with your SQL Server database. Whether you're using Azure SQL Database, SQL Server Express, Amazon RDS for SQL Server, or a self-hosted SQL Server instance, the correct connection string is essential for secure and reliable data access.

In this comprehensive guide, we'll explain connection string formats, parameters, authentication methods, cloud provider examples, troubleshooting tips, and best practices to help you integrate SQL Server with API Maker effectively.

---

## SQL Server Connection String Format

Below is a visual breakdown of the SQL Server connection string format:

```
Server=[server_name];Database=[database_name];User Id=[username];Password=[password];[options]
└────┬────┘ └───────┬────────┘ └──────┬────────┘ └───────┬─────────┘ └───┬───┘
     │              │                  │                  │                │
   Server         Database          User ID           Password         Options
(Host/Instance)    Name            (optional)        (optional)      (key=value)
```

---

##  Connection String Parts Explained

### Server/Host
- **Single Instance**: `Server=myServerName` or `Server=192.168.1.100`
- **Named Instance**: `Server=myServer\SQLEXPRESS`
- **With Port**: `Server=myServer,1433` or `Server=tcp:myServer.database.windows.net,1433`

### Database
- Specifies the target database name
- Format: `Database=myDataBase` or `Initial Catalog=myDataBase`

### Authentication
- **SQL Authentication**: `User Id=myUser;Password=myPass`
- **Windows Authentication**: `Trusted_Connection=True` or `Integrated Security=SSPI`
- **Azure AD**: `Authentication=Active Directory Integrated`

### Options
- Additional parameters separated by semicolons
- Example: `Encrypt=True;TrustServerCertificate=False;Connection Timeout=30`

---

## SQL Server Connection String Examples

### Basic Local Connection
```
Server=localhost;Database=myDB;Trusted_Connection=True;
```

### Local with SQL Authentication
```
Server=localhost;Database=myDB;User Id=sa;Password=myPassword;
```

### Local SQL Express Instance
```
Server=.\SQLEXPRESS;Database=myDB;Trusted_Connection=True;
```

### Remote Server with Port
```
Server=192.168.1.100,1433;Database=myDB;User Id=dbuser;Password=myPassword;
```

### Azure SQL Database
```
Server=tcp:myserver.database.windows.net,1433;Initial Catalog=myDB;Persist Security Info=False;User ID=myUser;Password=myPassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
```

### Amazon RDS SQL Server
```
Server=mydb.cxxxxxxxxx.us-east-1.rds.amazonaws.com,1433;Database=myDB;User Id=admin;Password=myPassword;Encrypt=True;
```

### Encrypted Connection with Certificate Trust
```
Server=myserver.com;Database=myDB;User Id=myUser;Password=myPassword;Encrypt=True;TrustServerCertificate=True;
```

### Connection with Application Intent
```
Server=myserver;Database=myDB;User Id=myUser;Password=myPassword;ApplicationIntent=ReadOnly;
```

### Multi-Subnet Failover
```
Server=tcp:ag-listener.domain.com,1433;Database=myDB;User Id=myUser;Password=myPassword;MultiSubnetFailover=True;
```

### Connection Pooling Configuration
```
Server=myserver;Database=myDB;User Id=myUser;Password=myPassword;Pooling=true;Min Pool Size=5;Max Pool Size=100;Connection Lifetime=0;
```

---

## Connection String Parts Breakdown

| Part                               | Description                                 | Example                                           |
|------------------------------------|---------------------------------------------|---------------------------------------------------|
| Server/Data Source                 | SQL Server instance name, IP, or FQDN       | `Server=myserver`, `Data Source=192.168.1.1,1433` |
| Database/Initial Catalog           | Target database name                        | `Database=myDB`, `Initial Catalog=myDB`           |
| User Id/User ID                    | Username for SQL Authentication             | `User Id=sa`, `User ID=dbuser`                    |
| Password                           | Password for SQL Authentication             | `Password=myPassword`                             |
| Trusted_Connection                 | Enable Windows Authentication               | `Trusted_Connection=True`                         |
| Integrated Security                | Alternative to Trusted_Connection           | `Integrated Security=SSPI`                        |
| Encrypt                            | Enable SSL/TLS encryption                   | `Encrypt=True`                                    |
| TrustServerCertificate             | Trust server certificate without validation | `TrustServerCertificate=False`                    |
| Connection Timeout/Connect Timeout | Connection establishment timeout (seconds)  | `Connection Timeout=30`                           |
| Command Timeout                    | Command execution timeout (seconds)         | `Command Timeout=120`                             |

---

## Connection String Options Breakdown and Explanation

| Parameter                    | Description                                    | Example Value                    |
|------------------------------|------------------------------------------------|----------------------------------|
| **Encrypt**                  | Enables SSL/TLS encryption for data in transit | `Encrypt=True`                   |
| **TrustServerCertificate**   | Bypasses certificate validation (dev only)     | `TrustServerCertificate=False`   |
| **Connection Timeout**       | Max time to wait for connection (default: 15s) | `Connection Timeout=30`          |
| **Command Timeout**          | Max time for command execution (default: 30s)  | `Command Timeout=120`            |
| **MultipleActiveResultSets** | Enables MARS for concurrent result sets        | `MultipleActiveResultSets=False` |
| **Pooling**                  | Enables connection pooling (default: true)     | `Pooling=true`                   |
| **Min Pool Size**            | Minimum connections in pool                    | `Min Pool Size=5`                |
| **Max Pool Size**            | Maximum connections in pool (default: 100)     | `Max Pool Size=200`              |
| **Connection Lifetime**      | Max lifetime of pooled connection (seconds)    | `Connection Lifetime=0`          |
| **ApplicationIntent**        | Read-only or read-write intent                 | `ApplicationIntent=ReadOnly`     |
| **MultiSubnetFailover**      | Support for Always On availability groups      | `MultiSubnetFailover=True`       |
| **Workstation ID**           | Client workstation identifier                  | `Workstation ID=MyApp`           |
| **Application Name**         | Application name for monitoring                | `Application Name=MyApplication` |
| **Packet Size**              | Network packet size in bytes                   | `Packet Size=4096`               |
| **Persist Security Info**    | Keep security info in connection string        | `Persist Security Info=False`    |

---

## Secure Connection Best Practices

- **Always use encrypted connections** in production with `Encrypt=True`
- **Avoid certificate trust bypass** - set `TrustServerCertificate=False` in production
- **Use Windows Authentication** when possible instead of SQL Authentication
- **Store credentials securely** using API Maker's Secrets Management
- **Implement least privilege** - grant only necessary database permissions
- **Regular credential rotation** and monitoring of database access
- **Enable connection pooling** for better performance and resource management

---

## Connecting SQL Server in API Maker

1. **Navigate to API Maker Dashboard** → **Database Configuration**
2. **Select SQL Server** as your database type
3. **Enter your connection string** in the designated field
4. **Test the connection** to verify credentials and network accessibility
5. **Configure schema introspection** settings if needed

Once connected, you can:
- **Auto-generate REST APIs** from your SQL Server tables and views
- **Use schema-based endpoints** like `/api/schema/tablename` for optimized queries
- **Leverage Deep Populate** to join SQL Server data with other connected databases
- **Enable real-time streaming** and caching for high-performance applications

---

##  Troubleshooting Scenarios

When connecting to SQL Server (local, cloud, or managed services), you may encounter various common errors. Below are categorized troubleshooting scenarios with solutions:

###  Authentication & Authorization Errors

| Error Code | Error Message                                       | Common Causes                                    | Suggested Solutions                                                              |
|------------|-----------------------------------------------------|--------------------------------------------------|----------------------------------------------------------------------------------|
| **18456**  | Login failed for user 'username'                    | Invalid credentials, disabled SQL Auth, firewall | Verify username/password, enable SQL Server Authentication, check firewall rules |
| **18452**  | Login failed. The login is from an untrusted domain | Windows Auth on unsupported domain               | Use SQL Authentication or configure domain trust                                 |
| **40607**  | Login failed due to client IP address               | Azure SQL firewall restrictions                  | Add client IP to Azure SQL firewall rules                                        |
| **40615**  | Login failed - server firewall rules                | Azure SQL server-level firewall                  | Configure server-level firewall rules in Azure portal                            |
| **18470**  | Login failed for user - account disabled            | SQL Server login disabled                        | Re-enable the login account using SQL Server Management Studio                   |

**Reference**: [SQL Server Authentication Troubleshooting](https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error)

###  Network & Connectivity Errors

| Error Code | Error Message                                   | Common Causes                                       | Suggested Solutions                                                  |
|------------|-------------------------------------------------|-----------------------------------------------------|----------------------------------------------------------------------|
| **53**     | Named Pipes Provider, could not open connection | SQL Server not running, incorrect instance name     | Verify SQL Server service is running, check instance name format     |
| **2**      | System cannot find the file specified           | Incorrect server/instance name                      | Verify server name and instance configuration                        |
| **10060**  | Connection timeout expired                      | Network latency, firewall blocking, server overload | Increase Connection Timeout, check network connectivity and firewall |
| **26**     | Error Locating Server/Instance Specified        | SQL Browser not running, instance not found         | Start SQL Server Browser service, verify instance name               |
| **10061**  | Connection actively refused                     | SQL Server not accepting connections                | Enable remote connections, check SQL Server network configuration    |

**Reference**: [SQL Server Network Connectivity Troubleshooting](https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/network-related-or-instance-specific-error-occurred-while-establishing-connection)

###  SSL/TLS & Security Errors

| Error Code      | Error Message                                                            | Common Causes                            | Suggested Solutions                                                       |
|-----------------|--------------------------------------------------------------------------|------------------------------------------|---------------------------------------------------------------------------|
| **19**          | SSL Provider: The certificate chain was issued by an untrusted authority | Invalid or self-signed certificate       | Install trusted certificate or set TrustServerCertificate=True (dev only) |
| **20**          | SSL Provider: The client certificate is not valid                        | Client certificate authentication failed | Verify client certificate installation and validity                       |
| **-2146893022** | SSL Provider: The target principal name is incorrect                     | Certificate name mismatch                | Ensure certificate matches server FQDN or use IP address                  |

**Reference**: [Enable Encrypted Connections to SQL Server](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine)

###  Performance & Resource Errors

| Error Code | Error Message                                         | Common Causes                        | Suggested Solutions                                        |
|------------|-------------------------------------------------------|--------------------------------------|------------------------------------------------------------|
| **1222**   | Lock request time out period exceeded                 | Long-running transactions, deadlocks | Optimize queries, implement proper transaction handling    |
| **8645**   | A timeout occurred while waiting for memory resources | Memory pressure                      | Increase available memory or optimize memory usage         |
| **701**    | There is insufficient system memory                   | Out of memory condition              | Scale up server resources or optimize memory configuration |

###  Database & Configuration Errors

| Error Code | Error Message                               | Common Causes                         | Suggested Solutions                            |
|------------|---------------------------------------------|---------------------------------------|------------------------------------------------|
| **4060**   | Cannot open database requested by the login | Database doesn't exist, access denied | Verify database name, grant access permissions |
| **15128**  | The specified database name is not valid    | Invalid database name characters      | Use valid database naming conventions          |
| **208**    | Invalid object name                         | Table/view doesn't exist              | Verify object exists and user has permissions  |

###  Cloud Provider Specific Errors

#### Azure SQL Database
| Error Code | Description                                        | Solution                                           |
|------------|----------------------------------------------------|----------------------------------------------------|
| **40544**  | Database has reached its size quota                | Scale up service tier or clean up data             |
| **40549**  | Session terminated due to long-running transaction | Optimize transaction scope and duration            |
| **40613**  | Database currently unavailable                     | Wait for service recovery or contact Azure support |

#### Amazon RDS SQL Server
| Error Code | Description                       | Solution                                       |
|------------|-----------------------------------|------------------------------------------------|
| **N/A**    | Connection failed to RDS instance | Check security groups, VPC configuration       |
| **N/A**    | Parameter group modifications     | Reboot RDS instance to apply parameter changes |

---

## FAQ

**Q: Can I use SQL Server Express with API Maker?**  
A: Yes, SQL Server Express works perfectly for development and small to medium-sized applications. Use the connection format: `Server=.\SQLEXPRESS;Database=myDB;Trusted_Connection=True;`

**Q: What's the default port for SQL Server?**  
A: The default TCP port is `1433`. Named instances use dynamic ports unless configured otherwise.

**Q: How do I connect to SQL Server in a Docker container?**  
A: Use `Server=localhost,1433;Database=myDB;User Id=sa;Password=YourPassword;TrustServerCertificate=True;`

**Q: Does API Maker support Always On Availability Groups?**  
A: Yes, use the availability group listener in your connection string with `MultiSubnetFailover=True`.

**Q: Can I connect to multiple SQL Server databases simultaneously?**  
A: Yes, API Maker supports multiple database connections. Configure each database separately in the dashboard.

**Q: What authentication methods does API Maker support for SQL Server?**  
A: API Maker supports SQL Authentication, Windows Authentication (for on-premises), and Azure AD authentication.

---

## List of SQL Server Cloud Service Providers

### Microsoft Azure SQL Services

**Azure SQL Database**
- Fully managed SQL Server database service
- Built-in high availability, automated backups, intelligent performance
- Serverless and hyperscale options available
- [https://azure.microsoft.com/en-us/products/azure-sql/database/](https://azure.microsoft.com/en-us/products/azure-sql/database/)

**Azure SQL Managed Instance**
- Near 100% compatibility with SQL Server on-premises
- VNet deployment with private IP addressing
- Cross-database queries and SQL Agent support
- [https://azure.microsoft.com/en-us/products/azure-sql/managed-instance/](https://azure.microsoft.com/en-us/products/azure-sql/managed-instance/)

### Amazon Web Services (AWS)

**Amazon RDS for SQL Server**
- Managed SQL Server with automated patching and backups
- Multi-AZ deployments for high availability
- Read replicas and automated failover
- [https://aws.amazon.com/rds/sqlserver/](https://aws.amazon.com/rds/sqlserver/)

**Amazon EC2 with SQL Server**
- Self-managed SQL Server on virtual machines
- Full control over configuration and customization
- License-included and BYOL options
- [https://aws.amazon.com/microsoft/sql-server/](https://aws.amazon.com/microsoft/sql-server/)

### Google Cloud Platform (GCP)

**Google Cloud SQL for SQL Server**
- Fully managed SQL Server database service
- Automatic replication, backup, and failover
- Integration with Google Cloud services
- [https://cloud.google.com/sql/docs/sqlserver](https://cloud.google.com/sql/docs/sqlserver)

**Google Compute Engine with SQL Server**
- Self-managed SQL Server on virtual machines
- Custom machine types and persistent disks
- Windows Server and SQL Server licensing options
- [https://cloud.google.com/compute/docs/instances](https://cloud.google.com/compute/docs/instances)

### API Maker Cloud

**API Maker Managed SQL Server**
- One-click SQL Server installation with API Maker integration
- Dedicated VPS hosting with flexible scaling
- Cost-effective alternative to major cloud providers
- Pre-configured for optimal API Maker performance
- [https://cloud.apimaker.dev](https://cloud.apimaker.dev)

### Enterprise & Specialized Providers

**IBM Cloud Databases for SQL Server**
- Enterprise-grade managed SQL Server service
- High availability and disaster recovery
- Integration with IBM Watson and AI services
- [https://www.ibm.com/cloud/databases](https://www.ibm.com/cloud/databases)

**Oracle Cloud Infrastructure (OCI)**
- SQL Server on Oracle Linux or Windows
- Autonomous database options
- High-performance computing capabilities
- [https://www.oracle.com/in/cloud/](https://www.oracle.com/in/cloud/)

**Alibaba Cloud RDS for SQL Server**
- Managed SQL Server in Asia-Pacific regions
- Multi-zone deployment and read replicas
- Integration with Alibaba Cloud ecosystem
- [https://www.alibabacloud.com/en/product/apsaradb-for-rds-sql-server?_p_lc=1](https://www.alibabacloud.com/en/product/apsaradb-for-rds-sql-server?_p_lc=1)

### Infrastructure-as-a-Service (IaaS) Providers

**DigitalOcean Droplets**
- Self-managed SQL Server installation on VPS
- Simple pricing and developer-friendly interface
- Global data center locations
- [https://www.digitalocean.com/products/droplets](https://www.digitalocean.com/products/droplets)

**Linode (Akamai)**
- Virtual machines for SQL Server installation
- High-performance computing with SSD storage
- 24/7 support and global presence
- [https://www.linode.com/products/essential-compute/](https://www.linode.com/products/essential-compute/)

**Vultr**
- Cloud compute instances for SQL Server
- High-frequency compute options
- Multiple global locations
- [https://www.vultr.com/products/cloud-compute/](https://www.vultr.com/products/cloud-compute/)

**Hetzner Cloud**
- European-based cloud infrastructure
- Cost-effective VPS with excellent performance
- ARM64 and x86 instance types
- [https://www.hetzner.com/cloud](https://www.hetzner.com/cloud)

### Specialized Database Hosting

**ScaleGrid**
- Fully managed SQL Server hosting
- Multi-cloud deployment (AWS, Azure, GCP)
- Database monitoring and optimization tools
- [https://scalegrid.io/sqlserver/](https://scalegrid.io/sql-server/)

**Rackspace Technology**
- Managed SQL Server on multiple clouds
- Database administration services
- 24x7x365 expert support
- [https://www.rackspace.com/data/databases](https://www.rackspace.com/data/databases)

**OVHcloud**
- European cloud provider with SQL Server support
- Public and private cloud options
- GDPR-compliant infrastructure
- [https://www.ovhcloud.com/](https://www.ovhcloud.com/)

---

## Related Resources

- [Microsoft SQL Server Documentation](https://learn.microsoft.com/en-us/sql/sql-server/)
- [SQL Server Connection String Reference](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/connection-string-syntax)
- [Azure SQL Database Documentation](https://learn.microsoft.com/en-us/azure/azure-sql/)
- [Amazon RDS for SQL Server User Guide](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html)
- [Google Cloud SQL for SQL Server Documentation](https://cloud.google.com/sql/docs/sqlserver)
- [API Maker Documentation Hub](https://docs.apimaker.dev/)
- [SQL Server Performance Tuning Guide](https://learn.microsoft.com/en-us/sql/relational-databases/performance/performance-monitoring-and-tuning-tools)
- [SQL Server Security Best Practices](https://learn.microsoft.com/en-us/sql/relational-databases/security/security-center-for-sql-server-database-engine-and-azure-sql-database)