CODEWITHSUNDEEP
databasesecuritysql-server-2005
Abhay Bhave
Abhay Bhave

Reputation: 45

Prevent windows admin user from reading a SQL Server database

I am creating a product software that many people will install on their machines themselves.

It is a VB.Net - SQL Server application.

I certainly don't want a user to have some SQL Server GUI tool like SQL Server Management Studio installed and then fiddling with the database values / definitions directly.

But when my customer will install the application (and the database - and SQL Server instance if there isn't any) with his own (most probably system admin) account, that account is obviously going to be authorized to access, read and modify the database.

Is there any way to prevent it?

We tried to keep .mdf file with app, but that can be attached manually.

We tried to use .sdf file, which allows it's own password to be created, and we loved the idea initially, but that is ok for single project .net solution. For a .net solution with multiple projects (e.g. one for user info and authentication, one for staff payroll etc.), it takes 1 copy of that file for each project, rendering that idea useless.

Upvotes: 1

Views: 1704

Answers (2)

STLDev
STLDev

Reputation: 6164

I do not believe that there is anyway to prevent an database user from viewing or otherwise altering a database installed on a SQL Server instance to which they have administrative access. However, it is possible to make it very difficult (but not impossible) to view or modify stored procedures. Microsoft uses this approach with their GP accounting software.

To create stored procedures that cannot be reverse engineered easily, create them with the ENCRYPTION option. See http://msdn.microsoft.com/en-us/library/ms187926.aspx for more detailed info.

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300549

The standard way to manage application/database permissions is to create database schemas and roles and assign permissions to those roles, such as ReportViewer (with data reader only access), SensitiveDataViewer (for salaries etc) and remove all default access on your database access.

Then you create logins using domain groups (preferably) or users, and add those to roles. If your are on a domain and use domain groups, you can then manage access through Active Directory user membership of groups.

BUT, you can't stop a sys admin looking at/changing data. If you manage access through roles you are shifting the responsibility of security to the system administrator(s), and that's a good thing.

You could encrypt your entire database, but a sys admin could still circumvent this if they really wanted to.

Database Engine Permission Basics

Manage Access and Roles with Transact-SQL (T-SQL) Commands

Understanding the Difference between Owners and Schemas in SQL Server

Schema, Owner for objects in MS SQL

Upvotes: 3

Related Questions