Api Key Exposure in Phoenix with Mssql

When building Phoenix applications that connect to Microsoft SQL Server (Mssql), developers often embed connection strings containing API keys or database credentials directly in configuration or source code. If these Phoenix endpoints expose introspection or debug routes, or if the Mssql instance is reachable from untrusted networks, an attacker can obtain the API key and pivot to the backend data store. This combination increases the risk of key leakage through insecure endpoints, insufficient input validation, and weak access controls.

During a middleBrick scan, the tool checks for exposed API keys in responses and insecure authentication mechanisms. For example, a Phoenix controller that returns system configuration or debug info might inadvertently include a connection string with an embedded API key. If that endpoint lacks proper authentication or is subject to BOLA/IDOR, the key can be extracted and used to access the Mssql database. Additionally, if the Mssql server accepts connections without enforcing TLS encryption, the API key and other credentials can be intercepted during transit.

middleBrick’s 12 security checks run in parallel to identify such risks. The Authentication check verifies whether endpoints that expose configuration require valid credentials. The Data Exposure and Encryption checks confirm whether sensitive data, including API keys in database connection strings, are transmitted or stored without encryption. The Input Validation check tests for injection vectors that could allow an attacker to extract configuration details containing keys. The SSRF check ensures that the application does not inadvertently allow an attacker to direct requests to the Mssql metadata endpoints or internal services where keys might be logged or exposed.

An unauthenticated LLM endpoint in a Phoenix service that returns model metadata could also expose API keys if the system prompt or configuration is improperly handled. middleBrick’s LLM/AI Security checks specifically test for system prompt leakage and output scanning for credentials, helping to detect when API keys appear in responses that should remain internal. These findings are mapped to frameworks such as OWASP API Top 10 and PCI-DSS, emphasizing the need to protect secrets at both the application and data layers.

Using the middleBrick CLI, you can scan a Phoenix endpoint that interacts with Mssql to see whether any findings highlight key exposure risks. The report includes prioritized findings with severity levels and remediation guidance. For continuous protection, the Pro plan enables scheduled scans and alerts, while the GitHub Action can fail builds if a scan detects exposed credentials, preventing insecure configurations from reaching production.

To mitigate Api Key Exposure when using Phoenix with Mssql, store connection strings securely and avoid returning them through any endpoint. Use environment variables or a secrets manager, and ensure that all database connections enforce encryption. Below are concrete Mssql-focused remediation steps and code examples tailored for Phoenix applications.

First, configure your Mssql connection to require encryption and avoid embedding API keys in connection strings that might be logged. Use integrated security where possible, and restrict permissions to the minimum required. In your Phoenix configuration, reference the connection string from the system environment rather than hardcoding it.

# config/config.exs
import Config

database_url = System.get_env("PHOENIX_MSSQL_URL") || "server=localhost;database=appdb;TrustServerCertificate=false;"

config :my_app, MyApp.Repo,
  url: database_url,
  pool_size: 10,
  ssl: true

Ensure that the connection string includes TrustServerCertificate=false and ssl=true to enforce encryption. Next, create a Mssql user with limited permissions and avoid using the db_owner role for application connections. The following Mssql script demonstrates how to create a minimal-privilege login and map it to a database user.

-- Mssql: Create a login with restricted permissions
CREATE LOGIN phoenix_app_login WITH PASSWORD = 'Str0ngP@ssw0rd!';
GO

-- Mssql: Create a user in the target database
CREATE USER phoenix_app_user FOR LOGIN phoenix_app_login;
GO

-- Mssql: Grant only necessary permissions (e.g., SELECT, EXECUTE)
GRANT SELECT ON SCHEMA::dbo TO phoenix_app_user;
GRANT EXECUTE ON SCHEMA::dbo TO phoenix_app_user;
GO

-- Revoke public permissions that may expose metadata
REVOKE CONNECT ANY DATABASE TO phoenix_app_user;
DENY VIEW DEFINITION TO phoenix_app_user;

In your Phoenix code, avoid dynamic SQL that concatenates user input directly into queries, as this can lead to injection that exposes configuration or keys. Use parameterized queries or Ecto’s query DSL to keep SQL safe. The following example shows a safe Ecto query that does not expose internal details even if an error occurs.

# lib/my_app/repo.ex
defmodule MyApp.Repo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.Postgres
end

# lib/my_app/user.ex
defmodule MyApp.User do
  use Ecto.Schema
  schema "users" do
    field :name, :string
    field :email, :string
    timestamps()
  end

  def get_user_by_email(email) do
    from(u in __MODULE__, where: u.email == ^email, select: [:id, :name, :email])
    |> Repo.one()
  end
end

Finally, audit your Phoenix logs and Mssql error messages to ensure they do not contain stack traces or connection strings that include API keys. Use middleware to filter sensitive data from responses and configure your Phoenix endpoint to reject requests that attempt to probe for debug or configuration endpoints. middleBrick findings can guide you in identifying endpoints that should not expose any backend details and help you validate that encryption and access controls are correctly enforced.