1

We currently use a connection string to authenticate our database credentials. Due to grown and compliance the developers are no longer allowed to "see" the database credentials that our websites use. Are solution to this problem is use Integrated Authentication. We planned on setting up a user per App Pool and then allowing that use access to the Database.

My question is: Are there any security concerns around this approach? As far has removing the DB credentials from the connection string, is there a better(easier or simpler) approach we should/could be taking?

Hunter D
  • 11
  • 2

3 Answers3

1

If you need to secure and audit access to the production database then Windows Authentication is a better choice than Sql Authentication for a number of reasons:

  1. You can control exactly who can access the database via NT groups and permissions, which means you know who specifically has access to the database. The pool of access with sql authentication is only limited by who knows the password. Given n people who know the password, tracking who did what at a certain point of time is trickier (but not impossible) given that.

  2. Only your sysadmins need know the password for the nt identity with access to the database; in fact, much of the config can be done only knowing the username

  3. Logins and access can be tracked at the domain level far more easily than with SQL Server logins.

What it wont give you is:

  1. Ability to ensure that the developers can't see production data - whoever writes the app can easily include some diagnostic routines to select out data

  2. Ensure that production data only stays in production - anyone making a backup of the production database (say to restore it to a UAT environment for testing) could easily expose production data.

Problems with this approach have already been discussed in other posts; in particular, with ASP.Net applications, you have to consider whether or not you are going to use Impersonation/Delegation (webserver can act as the NT user accessing it) or a Trusted User model (where you configure a fixed identity to access certain resources).

This is further complicated by the IIS version you are using.

dash
  • 89,546
  • 4
  • 51
  • 71
  • An application pool is not user specific, it's application specific. The application will still use a single account to connect to the database. To log on with end-user credentials, you'd have to impersonate the end user. A side effect of impersonation is that connection pooling no longer functions. – Andomar Mar 04 '12 at 22:31
  • You can create an individual application pool for that application running as the "trusted user" yes, but you would also capture the current user identity hitting the site when performing any operations. True about connection pooling - but that's just because it's now instanced per user in impersonation and can't be shared. SOX is often about making sure you know who is doing what, who can do what, and stopping them from doing things they shouldn't be able to do; as an aside, in my old line of work, any app that used SQL Auth failed SOX validation. – dash Mar 04 '12 at 22:34
0

If your connection string is stored in a web.config file, you could create a separate production version of that file that the deverlopers can't see. That's easier to test and setup than integrated authentication using app pools.

One word of warning though: If you restrict developers that much, it will slow down their velocity of change. Since the rest of the world does keep moving, this usually ends with the application becoming a dead legacy package. That's dangerous if you plan to grow, improve or extend.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • In a word Sarbox, we have no choice but to restrict developer access. – Hunter D Mar 04 '12 at 15:05
  • The [Sarbanes–Oxley Act](http://en.wikipedia.org/wiki/Sarbanes–Oxley_Act) ? How would that force you to restrict developer but not sysadmin access? – Andomar Mar 04 '12 at 15:16
  • It forces separation of concerns, someone that is a developer cannot have admin access to the database, nor can they deploy there own code. At least according to Ernst & Young. – Hunter D Mar 04 '12 at 20:29
  • It's a good and a bad thing - as Andomar points out, it can make release cycles slower and impeding developer access to production systems can make it harder to solve problems and deploy. On the other hand, robust deployment practices and better diagnostics are also a good thing. It depends, but if you want SOX then this separation of duties is usually a basic recommendation. – dash Mar 04 '12 at 22:38
  • Also, from experience, it's usually about following the recommendations auditors like E&Y make so you tick all the boxes and declare your app is "SOX Compliant". It usually doesn't deliver functional benefits to the end user (although it should deliver confidence that the app is interacting with data and other systems in a compliant and safe way). – dash Mar 04 '12 at 22:43
0

Use of application pool's identity can be quite complicated to set up, consider trust and delegation problem. A better option can be securing connection strings using encryption.

Community
  • 1
  • 1
Alberto Spelta
  • 3,578
  • 2
  • 21
  • 20