Share via

Prevent a thick-client app's Windows user from accessing the local SQL Express DB outside the app

Mansoor R 0 Reputation points
2026-06-04T13:03:09.61+00:00

Setup: A Windows desktop app runs on non-admin workstations. It uses a local SQL Server Express instance on the same machine over shared memory. The app connects to SQL using the logged-in Windows user's own identity (the app process runs as that interactive user). SQL is in Windows Auth mode and sa is disabled. The app has its own login screen for access control. The database holds sensitive data.

Goal: The app needs full read and write to the database. But the same Windows user running the app should not be able to access the database directly outside the app. That is, they should not be able to open SSMS, sqlcmd and connect using their own identity, and they should not be able to read or write the tables directly to bypass the app login screen.

The difficulty: The app process and the user are the same Windows principal. So the user inherently has whatever SQL access the app has, and any stored credential or connection string seems extractable by the user who owns the process.

Question: Is it possible to give the app database access while preventing the same interactive Windows user from connecting directly with other tools? If so, what is the recommended approach for a local SQL Express setup like this?

SQL Server Database Engine
0 comments

Answer recommended by moderator

Mansoor R 0 Reputation points
2026-06-08T12:32:44.74+00:00

Thanks for the responses in this thread. Based on the inputs above and some additional research on my end, I've arrived at the approaches outlined below.

For additional business context: the thick-client application uses MSSQL Express as the embedded ISV database (i.e. shipped along with the thick client app) and runs locally on a standalone machine. Depending on the client's deployment environment, this may be set up in an air-gapped network or run in kiosk mode.

Approach-1: 3-tier Architecture

Introduce a middle layer - for example, a web server running on localhost; and grant SQL permissions to the web service only, with the web service itself protected by the application credentials. This is the only fully reliable solution to the existing problem, but it increases the overall complexity of the setup.

Approach-2: Using Application Roles (recommended)

Grant BUILTIN\Users connect permission only (the default public role already provides this), and create an application role secured with a per-machine generated password (and stored encrypted using DPAPI machine key). Permissions on the application database are then granted to this application role only. In this approach, the password stored by the application (even when encrypted) can be decrypted by a skilled, motivated user, because Windows does not support application-level security—that is, a password that can be read by the application but not by the Windows user.

This approach carries a residual risk

Was this answer helpful?

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bruce (SqlWork.com) 84,086 Reputation points
    2026-06-04T16:17:38.57+00:00

    windows does not currently support application based security

    you could use sql standard security and an application unique login. while there is no 100% secure way to store the password, you can make it difficult to access. the password should be unique per box, so if a user cracks their computer, its not usable on another computer.

    a more common approach is hosting the database behind a web server. the web api can limit the user to only supported requests. even if hosted on the same box, the web server can use a service account to access the database, so the user account does not access.

    note: iOS, MacOs and non rooted Android support application containers where application data can be protected from user access, so a local database like SQLite can be protected. Maybe this will come to windows someday.

    Was this answer helpful?

    1 person found this answer helpful.

  2. AI answer

    2026-06-04T13:03:32.3266667+00:00

    No. With SQL Server (including Express) running on a machine that the user controls, it is not possible to give a thick-client application full database access while technically preventing that same interactive Windows user from accessing the database directly with other tools.

    Key points from the platform behavior:

    1. Local admins and instance owners can always access SQL Server
      • When SQL Server is installed on a computer that others administer, the owner/administrator of that computer can always access the SQL Server instance as a member of the sysadmin fixed server role and can grant themselves permissions, manage backups, decrypt/move data files, etc. This is a fundamental trust and control issue, not something that can be fully solved with SQL configuration alone.
      • Protecting the intellectual property or data in such a scenario is primarily a legal/licensing issue, not a technical one.
    2. Windows Authentication ties app and user together
      • Using Windows Authentication (Integrated Security/Trusted_Connection) means the app and the user share the same Windows identity. Any permissions granted so the app can read/write data are, by definition, granted to that Windows principal. The user can then use that same identity in SSMS, sqlcmd, or other tools.
    3. User instances / LocalDB do not change the trust model
      • SQL Server Express user instances and LocalDB run under the user’s own Windows security context and are designed so that the owning user is effectively sysadmin on that instance. They are explicitly intended to give a least-privilege Windows user full control over their own local SQL instance, not to hide data from that user.
      • Databases in LocalDB or user instances are protected only by file system ACLs. If the user can access the database files, they can open them with their own LocalDB instance.
    4. Encryption and obfuscation cannot fully hide schema/data from the local admin/user
      • Even if stored procedures or data are encrypted, a user who can attach a debugger to the SQL Server process can retrieve decrypted procedures and data from memory at runtime.
      • Transparent Data Encryption and backup encryption protect at-rest data against offline access, but SQL Server administrators and machine admins can still reverse these protections.

    Implications for the described scenario:

    • Because the app runs under the interactive user’s Windows account and uses Windows Authentication to connect to a local SQL Express instance, any database permissions required by the app are inherently available to that user when using other tools.
    • There is no supported SQL Server configuration that allows “this Windows principal can access the database only when running process X, but not when running process Y” on the same machine under that user’s control.

    Recommended approaches (conceptual, not technical enforcement on that same box):

    • Move the database off the user’s machine to a server controlled by an administrator or service provider. Then:
      • Use Windows Authentication or other secure authentication, but only grant database permissions to service accounts or centrally managed identities, not to the end user’s Windows account.
      • The client app can authenticate to the service (or to the DB via a service account), while the end user cannot directly connect to the database server.
    • If the database must remain local, rely on non-technical controls:
      • License agreements and policies that prohibit tampering or direct access.
      • Auditing and monitoring where feasible.

    But purely technically, on a local SQL Express instance where the app and the user share the same Windows identity, SQL Server cannot enforce “app-only” access while blocking that same user from direct tools.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

    1 person found this answer helpful.
  3. Erland Sommarskog 134.6K Reputation points MVP Volunteer Moderator
    2026-06-04T18:54:50.8033333+00:00

    It's a dead end. Full stop.

    If you want a solution where the user only can connect to the database through the application, you need to have three tiers. That is, the application talks to a middle layer that talks to the database. Application, middle layer and database are all on different machines. The middle layer uses its own login, and the actual users do not have access to SQL Server.

    There are some possible variations. In the system I work with the application server is IIS and it runs on the same machine as SQL Server, and it does not use its own login, but it impersonates the users. However, we have revoked CONNECT permission on the TCP endpoint, so users can only log on to SQL Server on the machine itself through Shared Memory. And they can only get on the machine through the web server.

    Yet a variation is to put the application on Remote Desktop or Citrix, and all users would be able to do on those machine is to run that application. The application could use a network login, or the network is segmented so that users cannot reach the SQL Server machine.

    If you want all locally, the best you can do is security by obscurity. That is, as Bruce says, the application logs in with a specific login. But since the information about that login and its password is on the user's machine, there is no way to hide it for a cunning user who understands where to look.

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.