2

The Problem

Good Morning! I work on an application team that supports a few applications which utilize SQL Server for data storage. Recently, our Database Support team decided that SQL Authentication was no longer permissible (for security and logging reasons) and so my team was forced to convert all connections to Windows Authentication including several dedicated Service IDs that our applications had been utilizing for data retrieval.

First, let me say there most certainly are advantages to moving to Windows Authentication, I am not trying to dispute that. But this change has raised a huge problem for us... by switching our Service IDs to Windows Authentication we have now opened up our back-end databases to every internal business user with front-end application access.

MS Access is pushed out to every user desktop and a few superusers even have access to SSMS. At this point we are relying entirely on user ignorance to prevent internal users from accessing the back-end database directly. And given that certain roles have elevated DML rights, this presents a possibility for some nasty data consequences.

This new enterprise standard has left my team stuck between a rock and a hard place at this point so we looking for any database, account or architecture solution that would allow us to restrict user access to front-end only.

Questions

  • Has anyone else run into this problem? Is there an architectural solution we are missing that would allow us to eliminate SQL Authentication without exposing our databases?
  • Does anyone know of a way to restrict access to a SQL Server database to only certain connection methods? I'm wondering if there is a way to designate a specific ID (or role) as only allowing a connection through a front end (and eliminate ODBC connections entirely).
  • Does anyone have any clever workarounds?

-------------EDIT---------------

A couple people brought up a good point about role access so I wanted to clarify our former and current solution... Previously, all role access was managed on the front-end and data retrieval was handled entirely by private system SQL Authenticated IDs to which end users had no visibility.

When we were forced to eliminate these SQL Auth IDs, we created a similar role-based setup on the back-end database as existed on the front end. Active Directory Groups were created to house different groups of users and these groups were assigned specific role privileges in the database. So currently access is limited by role as much as feasible.

The problem is that even the lowest privileged roles have INSERT, UPDATE and DELETE access to some tables (access which is normally controlled through code). So while we were able to mitigate risk somewhat by utilizing database roles, we still have areas where a user can bypass front end protections by logging directly into the database.

Improve this question
2
  • 1
    There should be no difference between SQL and Windows Authentication unless you had no security to begin with and relied on hiding the SQL account's password from end users. Create the appropriate roles, give table/sproc access to the appropriate roles and assign users to the roles, whether these are Windows or SQL users Commented Jun 9, 2015 at 15:41
  • You are correct, from a database perspective under our SQL Auth solution, we had no users connecting to the database either directly or through the application. User roles were managed through the front end only and any data requests would be fed to a private system SQL ID which would handle all database calls. We have since moved our application level roles also to the database layer but this allows the end user to bypass data entry logic if they directly access the database. Commented Jun 9, 2015 at 16:25

1 Answer 1

Reset to default
1

EDIT: Question clarification makes this answer obsolete, but leaving it for reference since some comments discuss it.

Assuming you mean that you have to (based on your architecture) allow access to the DB to each windows user account, one options is to use database roles.

You disable public access to your database, then define a set of database roles, depending on your use cases. Each role is granted permissions such that members of that role are able to manipulate the data they need and or work with the objects they need. Users are then mapped into the roles they require. When connecting to your database, the user will be granted permissions according to the roles they are members of.

For example, we have a role in one of our databases named MyAppUser (our name is actually related to the app which uses the db), which is designed for end users to read and insert data only. These can be created simply as follows:

CREATE ROLE [MyAppUser]

The role is granted just the permissions it to the relevant schemas or tables (assume all our "public" tables are in dbo schema for now).

GRANT SELECT ON SCHEMA::[dbo] TO [MyAppUser] 
GRANT INSERT ON SCHEMA::[dbo] TO [MyAppUser]
GRANT DELETE ON SCHEMA::[dbo] TO [MyAppUser]

Each user who should have this public read-write access is then mapped into the relevant role.

ALTER ROLE [MyAppUser] ADD MEMBER [UserName]

This separates users and roles / permissions within your database and allows you to have a single point of entry to control who has access to what in your databases.

By having the "View Definition" permission denied by default (to end users), they won't be able to "explore" the database / view table definitions etc using access, or even SSMS.

NB: SSMS provides wizards for managing and viewing permissions and memberships which are very handy for getting things initially setup / tested / fiddled around with.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

You can map Windows Groups to roles or logins. That makes it much easier to manage users, as the Domain Admins will be able to add/remove users in one place, eg for both DB and network access.
@PanagiotisKanavos: Yes, I forgot to mention this. We indeed use this to map ad groups to roles and it makes things much easier. We also use this to grant admin rights to our dev team (on the database server).
@Xan Good point... This actually was our solution for mimicking our front-end user roles. We added AD Groups and assigned them to specific database roles and then dropped the appropriate Windows Auth users into the groups. The problem is that the nature of most of our applications require at least some write privileges to even the most basic user. On the front end we can manage this data entry through code but on the back end our role definitions just show INSERT, UPDATE or DELETE privileges which in some situations can be very dangerous.
@DanK: hence your comment about obscurity - restricting "view definition" stops people "browsing" to discover your schema, but if they know, or can infer it they can still execute "unchecked" code. The issue then is one of input validation and such I suppose... I don't know if it would help, but we have in the past implemented row-level security by exposing data via a view which joins to some mapping table based on a row key (country or product category for example) and username. Something like this can restrict which data "appears" to different users - maybe this is something to consider.
@DanK: Thinking further, for some of our apps we use what we term windows "service accounts" to manage connection to the database. These work exactly as your original solution suggested. We authenticate and authorize users by windows account. We apply roles via AD groups, but we only allow the service account to connect to the database. This works in a web-app context, since it's the account running IIS, but we also use it for windows services. Could you do something similar, using a single windows account for data access? The hurdle would be how to encrypt the password to prevent direct acces
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.