I have a small web application on an application server which accesses very sensitive data on a database server. Instead of accessing this data directly through a SQL connection I was thinking about writing a web service interface on the database server which can only be used by the application server.
My thoughts are, if the application server ever got hacked, they wouldn't be able to access the data directly. The hackers would have to deal with another layer (the interface) and by this time I would have handled the situation.
I know this would would add some lag to the web application, but since the data is sensitive I believe this is an acceptable trade-off.
Will this extra "layer" of security really make my system more secure or am I missing something?
I think your comment "by this time I would have handled the situation" is off the mark, but otherwise this is a good idea. If an attacker compromises your front end application, you won't necessarily be afforded any extra time to beef up the backend layer. If the backend layer were comprised when the most likely scenario is that you would find out about it at the same time as you found out that your frontend layer was compromised.
The reason the backend layer will help is that it will expose to the front layer only the functionality it requires, not the full power of SQL. And it's defense in depth, so that an attacker will have to find two different kinds of exploits and use them at the same time to get through both layers.
You really want to use a standard three-tier architecture here. Put a web server (no business/application logic) as the front-end; static content only. It should connect back to your application server, through a firewall that only it is allowed to connect through (and only to the specific application server). Then, allow only that application server to talk to the database server.
Internet -> Firewall -> Web Server -> Firewall -> Application Server -> Database
Someone hacks your web server, they get nothing related to your application as that all lives on the application server (yes, they can connect to it, but only using the defined interface which should slow them down, at the least). Even if on the application server, they would still need to work out the interface into the database. This is a standard security architecture for this type of application.
I see where you're coming from, but it seems to smell a bit of "security by obscurity" to me. First point of call would be to make sure the SQL user that the web application uses is locked down appropriately, and only has access to the database objects it needs to. For example, read only access to tables it only needs to read from, write access to tables in needs to write to. On MS SQL this can be made somewhat simpler by only giving execute access to a small range of stored procedures. The user will only need execute access on the SPs, and will not need permissions on the underlying tables. This will then be utilising database level security, instead of using an additional, possibly insecure, abstraction layer.
As already suggested, you could then go on to add further tiers to your architecture, but I'd start at ground level using the security options already available before rolling your own.