Skip to content

SQL Server Connection Strings in API Maker

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

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

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

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/

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/

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/

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/

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

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

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

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

Oracle Cloud Infrastructure (OCI) - SQL Server on Oracle Linux or Windows - Autonomous database options - High-performance computing capabilities - 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

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

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/

Vultr - Cloud compute instances for SQL Server - High-frequency compute options - Multiple global locations - 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

Specialized Database Hosting

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

Rackspace Technology - Managed SQL Server on multiple clouds - Database administration services - 24x7x365 expert support - 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/