Published on: April 14th 2010


Hello Guys,


I cannot tell you how many clients have extremely bad security layer at the data-tier, thinking they will be off the hook because their security at the application tier is well defined.

This is nice to have ssl/https protocol layers and some firewall layers and all that jazz but if you let your logins do whatever they feel like once they are on the database server, you are in risk of losing your data, being stolen your data or even worse compromise the business overall.

How many clients develop their website with setting their application login as db_owner of their most trusted database and keep it this way once they go live on production due to the lack of expertise of this topic.

suddenly a few months later, they realize that sneaky injection attacks or hungry employees make their way onto the data tier and set up mayhem in the data. as soon as you are aware of it, you prevent the attacks making your application tier more secure and you fire the employees that provoked the horror. But unfortunately you realize it too late and your entire website is compromise. You then put your website down due to maintenance to fix all the mess as unfortunately you either do not have secured backups or if you have, you have no idea how to deal with point-in-time restore or you realize that your backups were not actually set up correctly.

Worse! you have to reinstall the entire database server because the attacks were very damaging even at the server layer.

This nightmare can happen and a good 10% to 20% of my clients call me to fix this kind of mess. One was because they just fired a developer that had all-in access to the data layer (with credit card information !) and another one due to sql injection. during a previous audit of their systems, I warned them a big deal about security but it was always at the bottom of their list, advocating they had to develop and deploy new functionalities first and if security was too much of a pain, they will look at that afterwards!

This is frustrating 🙁 but this is the life of a SQL server consultant. Warn the client, then secure what can be secure without dealing with the client application code (backup regime, having a development lifecycle with DBA validation).

What even worse is when the client needs to be PCI-DSS compliant. Then there are a huge task to do.

No matter what the goal of securing the data, here are the things to make sure to do:

1. reduce the amount of sysadmin people to users and sql service accounts: make sure the users are RESPONSIBLE for the sake of securing the production environment

2. Secure the backup folder and where the backups are robocopied, taped, etc.. Securization can be done at the file server layer and at the backup layer itself by setting up transparent database encryption (if this is something that you like me to develop I can)

3. Secure your sensible data: some data have to be secured like credit card data, password, etc... You can either use an application layer encryption, a dedicated box for encrypting the data or data encryption at the data layer with build-in encryption and symetric/asymetric keys. This is something I can also talk about if needed.

4. Your application logins need to have limited permissions to the databases it connects. There are many ways to minimize the application logins accesses. Here are the main ways:

4a. Minimize the access to stored procedure execution

4b. avoid cross-database query and avoid cross-database chaining: if not possible, keep the stored procedure execution level, add the execution by owner, grant the owner authentication to server, make the database trustworthy and set up synonyms to the other database tables. Make sure the owner of the source database does not have sysadmin permission and add the database owner to the databaase role dedicated to this security on the other database.

4c. Forbid dynamic sql as much as you can especially if your stored procedure require execution as owner to access other database tables

4d. Avoid linked servers, not only for security, but for having a complete independant architecture and avoid distributed transactions.

4e. Use Active Directory / LDAP security if possible to set up common encryption, that is compliant to Microsoft.

4f. Make sure a DBA will audit regularly all security policy. Best would be that your DBA is the only person allowed to deploy anything on the server and on the production database.

4g. Use of schema and database role: to set up "dynamically" the permissions onto a database. Granting execute on the same schema stored procedures will allow your developers to have secured permissions to any stored procedures within the same schema and therefore permissions will be already set and maintainibility will be easy.

4h. Secure data and log file size so that any attacks will not make the other databases that share the ressources unavailable if something goes wrong with this databases. Obviously this require supervision setup and a DBA that can adjust sizes of database files if necessary.

4i. Separate ressources: usually the back office software development is not as tight as front office development for several reasons like the implementation of a third-party software or different developpers running a lot of weird services, ETL "injections", so on and so forth. Your front office databases can be at risk. It is better to separate instances and at best having different server even. Publication of back office data and extraction of front office data can be perform with thourough atchitecture setup using replication, ETL tools and custo;ized publication SQL development (Along my experience, publication of data is extremely frequent!). This allows to secure and avoid the heterogenenous sources on the front office and then security os easier to setup.