如何将Android Studio连接至AWS RDS托管的SQL Server数据库？

Hey there! I get where you're coming from—trying to find a direct SDK for Android to talk to AWS RDS MSSQL can feel like chasing a ghost, and hitting roadblocks with PHP APIs that only support MySQL doesn't help. Let's walk through the practical, secure ways to make this work.

First: Why Direct Android-to-RDS MSSQL Isn't Recommended #

Before diving into solutions, it's important to note that direct database connections from Android are a bad idea:

• You'd have to hardcode database credentials in your app, which can be easily extracted by reverse-engineering.
• AWS RDS security groups are typically locked down to specific IPs (like your existing .NET app's server), so your Android app's dynamic IPs would get blocked.
• It exposes your database to potential attacks from the public internet.

The standard approach here is to build a middle-tier API that acts as a bridge between your Android app and the RDS database.

Solution 1: Reuse Your .NET Stack (Most Efficient) #

Since you already have a .NET app using the RDS MSSQL database, adding a .NET Web API layer is the most straightforward option—you'll leverage existing knowledge and database connectivity code.

Step 1: Add a Web API Controller to Your .NET App #

Here's a quick example of a controller that fetches data from your MSSQL database (using SqlConnection or Entity Framework, whichever you're already using):

using Microsoft.AspNetCore.Mvc;
using System.Data.SqlClient;

[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
    // Use your existing RDS connection string (store it in appsettings.json, not hardcoded!)
    private readonly string _connectionString = "Server=your-rds-endpoint,1433;Database=your-db-name;User Id=your-db-user;Password=your-db-password;";

    [HttpGet]
    public IActionResult GetProducts()
    {
        List<Product> products = new List<Product>();
        using (SqlConnection conn = new SqlConnection(_connectionString))
        {
            conn.Open();
            string query = "SELECT Id, Name, Price FROM Products";
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        products.Add(new Product
                        {
                            Id = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            Price = reader.GetDecimal(2)
                        });
                    }
                }
            }
        }
        return Ok(products);
    }

    // Add POST/PUT/DELETE methods as needed
}

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

Step 2: Configure AWS RDS Security Group #

Ensure your .NET app's server IP is allowed in the RDS security group's inbound rules (it should already be there since your existing app connects).

Step 3: Call the API from Android #

Use libraries like Retrofit or OkHttp to send HTTP requests to your .NET API. Here's a quick Retrofit example:

// Define the API interface
interface ProductApi {
    @GET("api/products")
    suspend fun getProducts(): List<Product>
}

// Initialize Retrofit
val retrofit = Retrofit.Builder()
    .baseUrl("https://your-dotnet-api-domain.com/")
    .addConverterFactory(GsonConverterFactory.create())
    .build()

val productApi = retrofit.create(ProductApi::class.java)

// Call the API from a coroutine
lifecycleScope.launch {
    try {
        val products = productApi.getProducts()
        // Update UI with products
    } catch (e: Exception) {
        // Handle error
    }
}

data class Product(val id: Int, val name: String, val price: Double)

Solution 2: Use PHP with MSSQL Support (If You Prefer PHP) #

You mentioned your PHP API only worked for MySQL—but PHP does support MSSQL! You just need to use the sqlsrv extension (for Windows) or pdo_sqlsrv (cross-platform). Here's a quick example PHP endpoint:

<?php
header("Content-Type: application/json");

// RDS MSSQL connection details
$serverName = "your-rds-endpoint,1433";
$connectionOptions = array(
    "Database" => "your-db-name",
    "Uid" => "your-db-user",
    "PWD" => "your-db-password"
);

// Establish connection
$conn = sqlsrv_connect($serverName, $connectionOptions);
if ($conn === false) {
    die(json_encode(["error" => sqlsrv_errors()]));
}

// Fetch data
$sql = "SELECT Id, Name, Price FROM Products";
$stmt = sqlsrv_query($conn, $sql);
$products = array();

while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
    // Convert decimal price to float for JSON compatibility
    $row["Price"] = (float)$row["Price"];
    $products[] = $row;
}

echo json_encode($products);

sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
?>

Make sure you install the sqlsrv extension on your PHP server first (follow AWS docs for your EC2/Lambda setup if you're using AWS hosting).

Critical Security Tips #

• Never hardcode database credentials in your Android app—keep them safely on your API server.
• Add authentication to your API (e.g., JWT tokens) so only authorized users can access your database data.
• Restrict your RDS security group to only allow incoming connections from your API server's IP (don't open it to 0.0.0.0/0).

内容的提问来源于stack exchange，提问作者Bharath R