Field notes · sql-server

Read-only discovery in a vendor database you cannot touch

How I read a vendor database I was never given a login for: a SELECT-only login, a DPAPI-encrypted credential, and the PowerShell helper that runs the queries.

·9 min read

Sooner or later someone asks you for a number that lives inside a vendor’s application, and the application cannot produce it. The canned reports do not have the column you need, the export drops the rows you care about, and the vendor quotes you a custom-report fee with a two-week lead time. The data is right there in a SQL Server database on a server you administer. You just have no way in, because the vendor app authenticates with its own account and never handed you one.

That was my problem. Here is how I solved it with a read-only login I created for myself, without begging the vendor, without borrowing the application’s own over-powered account, and without ever writing a password into a file.

Key takeaways

  • Your Windows account almost certainly cannot log into a vendor’s SQL instance. The app uses its own embedded account, so you need to create your own.
  • Create a dedicated login with exactly one role, db_datareader, which grants SELECT and nothing else. Least privilege makes read-only discovery safe.
  • Store that login as a DPAPI-encrypted credential, never plaintext, and pass it as a SqlCredential object so the password never enters a connection string.
  • Encrypted columns like SSNs come back as ciphertext even with read access. That is the application protecting PII, and you had no business decrypting it anyway.

Why can’t I just use my Windows login?

Because the vendor app does not use Windows authentication, and it never created a login for you. Line-of-business apps ship with their own SQL account. It is often a single shared one the installer set up, and that account is what the app connects with. Your domain account was never added to the instance, so it fails at the door.

The first time I tried, I got a flat Login failed for user. Not a permissions problem inside the database. A rejection at authentication. My account simply did not exist as far as that SQL Server was concerned, even though I patch the box it runs on. Administering the server and holding a database login are two different things. The vendor’s installer only arranged the second one, and only for itself.

So the real question is not “how do I get in with what I have,” it is “what is the smallest new credential I can create that lets me read and nothing more.”

What read-only login should I create?

One with a single role: db_datareader. That role, which Microsoft documents as a fixed database-level role, grants SELECT on every table and view in the database and nothing else. No INSERT. No UPDATE. No DELETE, no schema changes, no touching server settings. For read-only discovery it is exactly the right size. It is also small enough that you can hand the reasoning to an auditor in one sentence.

I weighed three ways in before settling here, and the tradeoffs are worth seeing side by side:

Approach Cost Risk
Ask the vendor for a login Slow; sometimes a support fee and a lead time None, but you may wait weeks for a simple read
Reuse the app’s own SQL login Free and instant High: that account can write everything, so one bad query corrupts supported data
Create a dedicated read-only login Three SQL statements, once Low: SELECT only, cannot alter or delete anything

I deliberately did not reuse the application’s own login. That account usually owns the schema and can write everything. Query through it and one typo, an UPDATE where you meant a SELECT, can corrupt production data the vendor supports. A separate read-only login cannot make that mistake. The permission to write was never granted in the first place.

-- Run as a sysadmin, once, against the vendor database.
-- A dedicated read-only login: SELECT everywhere, nothing else.
CREATE LOGIN [report_readonly] WITH PASSWORD = N'<generated-strong-password>';

USE [VendorAppDb];
CREATE USER [report_readonly] FOR LOGIN [report_readonly];
ALTER ROLE [db_datareader] ADD MEMBER [report_readonly];

Generate the password with a real random generator, use it once to store the credential (next section), and then forget it. You never type it again.

How do I store the password without writing it in a file?

Encrypt it with DPAPI. The Windows Data Protection API ties the encrypted blob to your Windows user account on that one machine. A credential you export this way is unreadable by any other account, unreadable on any other machine, and safe to leave on disk. PowerShell’s Export-Clixml uses DPAPI automatically when you export a PSCredential.

# Run once, as the account that will run the queries.
# You are prompted for the password; it goes straight into the encrypted file.
$cred = Get-Credential -UserName 'report_readonly' -Message 'Read-only SQL login'
$cred | Export-Clixml -Path "$env:LOCALAPPDATA\report_readonly.cred.xml"

The password is never on a command line, never in a script, never in your shell history. It lives only inside a file that only your account on only that machine can decrypt. If someone copies the file to another box, or another user opens it, they get an unusable blob. That is the whole point of DPAPI: the secret is bound to a context, not just hidden.

Two rules I hold to. Keep that .cred.xml out of any git repository and out of any syncing folder like OneDrive or a file share. DPAPI protection does not travel with the file, so a synced copy is just a locked box sitting in the wrong building. And scope the credential to the single account that needs it, never a shared one.

How do I run the queries?

Import the credential, then pass it as a SqlCredential so the password stays an encrypted object end to end. Never build a connection string with the password in it, because that string ends up in memory, in logs, and in error messages. A small helper function is worth writing once and reusing for every ad-hoc question.

function Invoke-ReadOnlyQuery {
    param(
        [Parameter(Mandatory)] [string] $Server,
        [Parameter(Mandatory)] [string] $Database,
        [Parameter(Mandatory)] [string] $Sql
    )
    $cred = Import-Clixml "$env:LOCALAPPDATA\report_readonly.cred.xml"
    $cred.Password.MakeReadOnly()
    $sqlCred = [System.Data.SqlClient.SqlCredential]::new(
        $cred.UserName, $cred.Password)

    $conn = [System.Data.SqlClient.SqlConnection]::new(
        "Server=$Server;Database=$Database;Encrypt=True;TrustServerCertificate=True",
        $sqlCred)   # credential is separate from the connection string, by design
    $conn.Open()
    try {
        $cmd = $conn.CreateCommand()
        $cmd.CommandText = $Sql
        $adapter = [System.Data.SqlClient.SqlDataAdapter]::new($cmd)
        $table = [System.Data.DataTable]::new()
        [void]$adapter.Fill($table)
        return $table
    } finally {
        $conn.Close()
    }
}

From there, answering a question is one line, and piping to Export-Csv hands the requester exactly the slice they asked for. The internal reporting tools I build, including the small Flask apps I run on IIS, read from these vendor databases the same way: a least-privilege login, a protected credential, SELECT only.

What broke, and what surprised me

The Social Security numbers were gibberish. The first time I selected an employee table, the SSN column came back as a base64 string that decoded to nothing readable. I briefly thought the data was corrupt. It was not. The application encrypts that column at rest with its own key. Even a valid read returns ciphertext, and my read-only login has no way to decrypt it. That is correct behavior on every level. I had no legitimate reason to see plaintext SSNs to answer a headcount question, and the app made sure I could not. Treat an encrypted column as a wall the vendor built on purpose, and route any real need for that data through the vendor’s supported, audited path.

Least privilege also bit me later, which is the honest tradeoff. When a different day brought a performance question, I reached for the same helper to read the server’s dynamic management views and hit a permission error on every one. The DMVs need VIEW SERVER STATE, which db_datareader does not include. That is the role doing its job. Performance triage is a different task with a different, more privileged need. It belongs in a sysadmin session in SQL Server Management Studio, not through a read-only reporting credential. The lesson is not to widen the reporting login. It is to keep the two jobs apart.

Was the least-privilege overhead worth it?

Yes, and it is less overhead than it sounds. The whole setup is three SQL statements and two PowerShell lines, done once. What it buys is a credential you can explain in a sentence, a login that physically cannot write to a system you do not own, and a password that never touched a file in plaintext. When the question is “how did IT get this data and could they have broken anything,” the answer is short and it is good.

The pattern generalizes past any one vendor. Any time you need to read a system you administer but were never granted application access to, the safe shape is the same: a dedicated login scoped to exactly the read you need, a context-bound encrypted secret, and a thin tool that uses them. Give yourself the least you can get away with, and the risk of exploring someone else’s schema drops to almost nothing. The same discipline shows up in how I treat HR data as the source of truth for provisioning: read from the system of record, do not write back to what you do not own.

FAQ

Can I query a vendor’s SQL database directly?

Usually yes, if it runs on a SQL Server you administer, but not with your Windows account. The vendor app authenticates with its own embedded login and did not create one for you, so you create a dedicated read-only login yourself. Scope it to the db_datareader role so it can SELECT and nothing else, and confirm your support contract does not forbid direct database reads before you rely on it.

What is db_datareader and why use it?

db_datareader is a built-in SQL Server database role that grants SELECT on all tables and views and grants no write or administrative permission. It is the right size for read-only reporting and discovery, because a login that holds only that role cannot alter or delete production data even by accident.

How do I store a SQL password securely in PowerShell?

Use DPAPI by exporting a PSCredential with Export-Clixml, which encrypts the password and binds it to your Windows account on that one machine. Import it with Import-Clixml and pass it as a SqlCredential object so the password never appears in a connection string, a script, or your shell history. Keep the exported file out of git and out of any syncing folder.

Why are some columns encrypted even when I can read the table?

Because the application encrypts sensitive fields, such as SSNs, at the column level with its own key. A read-only login can retrieve the row but only sees ciphertext, which is the app protecting PII exactly as it should. Do not try to work around it. If you have a legitimate need for that data, get it through the vendor’s supported, audited export instead.