小型咨询公司SQL Server ERP对接外部公网网站技术求助

Great question—protecting sensitive internal data while building a public-facing portal is critical, especially when bridging legacy systems with newer tech. Here’s a structured, secure approach tailored to your setup:

All solutions should center on keeping your internal SQL Server completely isolated from direct public access and only exposing the absolute minimum data needed for the client portal.

1. Middle API Layer (Recommended First Choice) #

Instead of letting your new ASP.NET/PHP website connect directly to your ERP’s SQL Server, build a lightweight, secure middle layer (like an ASP.NET Web API or PHP-based REST API) that acts as a gatekeeper. This is the most flexible and secure option:

• Filter & Transform Data: The API will pull only non-sensitive fields (e.g., project ID, status, sanitized client name) from your ERP database, mapping internal table structures to safe Data Transfer Objects (DTOs) that hide your schema.
• Strict Access Control: Restrict API access to only your public website’s server IPs, and use API keys + HTTPS to authenticate requests. For client-specific data, add user authentication (e.g., client login) to ensure users only see their own projects.
• Decouple Systems: This layer separates your legacy ERP from the public web, so you can update either system without breaking the other.

Example ASP.NET Web API Action #

[HttpGet("client/projects/{clientId}")]
[Authorize(Policy = "PublicPortalAccess")]
public IActionResult GetClientProjectStatuses(int clientId)
{
    // Use a restricted SQL account to query your ERP database
    var clientProjects = _erpDbContext.Projects
        .Where(p => p.ClientID == clientId && p.IsPubliclyVisible)
        .Select(p => new PublicProjectStatusDto
        {
            ProjectId = p.ProjectID,
            ProjectName = p.ProjectName,
            CurrentStatus = p.Status,
            LastUpdated = p.LastModifiedDate
            // Exclude sensitive fields like financial data, internal notes
        })
        .ToList();

    if (!clientProjects.Any()) return NotFound("No projects found for your account.");
    
    return Ok(clientProjects);
}

2. Database-Level Hardening (Fallback Option) #

If you can’t implement a middle API right away, lock down your SQL Server to minimize risk:

• Create Restricted Database Users: Make a read-only SQL login that only has access to custom views (not raw tables). These views will include only the non-sensitive data your public website needs.
• Filter Data at the Source: Add conditions to your views (e.g., WHERE IsPubliclyVisible = 1) to automatically exclude projects that shouldn’t be shown externally.
• Limit Permissions: Revoke all access to system tables, stored procedures, and sensitive tables for this restricted user.

Example SQL for Views & Permissions #

-- Create a view with only safe, public-facing data
CREATE VIEW vw_PublicProjectStatus AS
SELECT 
    ProjectID,
    ProjectName,
    Status,
    LastModifiedDate
FROM Projects
WHERE IsPubliclyVisible = 1;

-- Create a restricted login/user
CREATE LOGIN PublicPortalUser WITH PASSWORD = 'YourStrongPassword123!';
CREATE USER PublicPortalUser FOR LOGIN PublicPortalUser;

-- Grant only read access to the view
GRANT SELECT ON vw_PublicProjectStatus TO PublicPortalUser;
-- Revoke all other permissions
DENY SELECT ON ALL TABLES IN SCHEMA dbo TO PublicPortalUser;

3. Network Isolation #

• Keep your internal SQL Server in a private subnet (no public IP address). If using cloud infrastructure, use VPC peering or internal networking to let your API/website server connect to the SQL Server without exposing it to the internet.
• Enable SQL Server’s IP whitelisting to only allow connections from your API or website server’s IP addresses.
• Force SSL/TLS encryption for all SQL Server connections to prevent data interception during transit.

4. Extra Security Checks #

• Data Sanitization: Even non-sensitive fields (like client names) can be partially redacted (e.g., Acme C*** ) if needed to reduce exposure.
• Audit Logs: Enable SQL Server’s auditing features to track all access from the restricted user, so you can spot unusual activity.
• Avoid Hardcoding: Never store database credentials in your website’s code. Use environment variables or secure configuration managers instead.

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