Accessing Databases from your applications

The best advice really depends on how your infrastructure is configured and how complex your deployment is. Is the code split between web and app servers? Which domains do the machines sit on (web, app, sql) are they all one domain or different domains?

As with all things one size does not fit all -however if possible integrated authentication is the way to go. This should be the default approach unless there is a good reason why not. Therefore your connection string should look something like:

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True

Otherwise you will have to store a user name and password in a configuration file. Holding user names and passwords in plain text is poor idea. You can encrypt parts of the configuration file but if it is subject to a security review it may lead questions about the encryption routine used etc.

The best solution is often to create a separate application pool for each website. Create a new service account for each application pool. Then give the service account minimum permission on the server it is running on. This can involve some trail and error.

Now give each service account in sql server access to only the databases which the service account needs access to and at the correct permission level. Often this is providing db_reader, db_writer and db_executor access within the db. Note in more recent version of sql server you may need to create an executor role yourself.

— Create a db_executor role
CREATE ROLE db_executor

— Grant execute rights to the new role
GRANT EXECUTE TO db_executor

If you have multiple websites hitting the same database you may wish to grant the different service accounts different permissions on individual tables and stored procedures. This is obviously more time consuming to set up but is a safer approach, especially if some websites within a system provide functionality to the public (which may be limited) and other websites provide much more functionality to internal users of the business. As with everything it is a balance between an appropriate level of security and cost/effort.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s