What goes between SQL Server and Client?

Question

This question is an updated version of a previous question I have asked on here.

I am new to client-server model with SQL Server as the relational database. I have read that public access to SQL Server is not secure. If direct access to the database is not a good practice, then what kind of layer should be placed between the server and the client? Note that I have a desktop application that will serve as the client and a remote SQL Server database that will provide data to the client. The client will input their username and password in order to see their data. I have heard of terms like VPN, ISA, TMG, Terminal Services, proxy server, and so on. I need a fast and secure n-tier architecture.

P.S. I have heard of web services in front of the database. Can I use WCF to retrieve, update, insert data? Would it be a good approach in terms of security and performance?

Answer 1

A web-service tier is pretty common for smart-clients as a layer between the user-client and the server. This allows:

• simple networking (http only)
• you have an app-layer in which to put validation etc without upsetting the db
• you can have security that isn't tied to the db
• the db can run as fewer accounts (app accounts), allowing greater connection pooling
• you can "scale out" the app layer
• you can cache etc above the db
• you can have a richer app layer, with more services than sql server provides
• the client has a known API, and never knows about the db (which is an implementation detail)

You can use WCF to talk to the app layer, but you shouldn't think in terms of "INSERT", "UPDATE" etc - you should think in terms of operations that make sense to your domain model - the "CreateOrder" operation, etc. ADO.NET Data Services allows an API more similar to your "INSERT" etc, but it isn't necessarily as controlled as you might like for a secure service.

Performance is really a factor of "what queries am I running?" and "how much data am I transferring?". As long as you keep the operations sane (i.e. don't fetch the entire "Orders" data over the wire just to find the most recent order-date), then you should be OK.