Best Practice for Connecting ASP.NET to SQL Server

Question

We have an ASP.NET 4.0 Web application that connects to a SQL Server on a separate machine across a LAN. I use a ConnectionString (with SQL Server authentication) stored in my Web.config to do this. Basically, it's a fairly traditional Web-Server-to-SQL strategy.

However, one of our clients is arguing that this strategy is not secure. This client says that we should only connect to the SQL Server through a separate Web Services layer.

I really don't want to rewrite this app just to satisfy this client. What should I tell him? Does any one know how I might best refute this?

Thanks in advance...

Answer 1

I never liked using the web config. The registry is more secure.

Best Practices is to:

• Hide the important items of the connection string in the registry
• Encrypt the important items in the connection like user names, passwords and server name in the registry
• Access the registry through a class
• Build the connection string on the fly and only when needed per page
• Error handle every page so the connection string won't show, in case of error
• Always close the connections once done. Avoid memory leaks
• Always close and reset DataReaders

For additional Security

• You can build a separate program to create the connection string and reference that project as a library inside the solution
• If you want to be really secure your client is correct. Send the information to a dll that will communicate with the DB. This is a lot of work

Sources:

From ScottGu @ http://msdn.microsoft.com/en-us/library/Aa302406

Scalability:

In regards to scalability: Creating\Editing registry keys across a web farm can be accomplished very easily, with custom admin software.

http://weblogs.asp.net/scottgu/archive/2010/09/08/introducing-the-microsoft-web-farm-framework.aspx

On a final note to all the people who voted me down. Security out of the box is just not enough. Security is an Art and not Science.

Hackers know where the password is stored by default......

ASP.NET 4.0 Fans

Microsoft makes it easy for asp.net 4.0 web sites to deploy registry settings:

http://msdn.microsoft.com/en-us/library/dd394698.aspx

Answer 2

There are many customers that argue the work of an IT professional, just like there are many people visiting the doctor asking for the medicine instead of what disease they have, because they already know the answer since they read about it on the internet.

I mean, they ask you to build the application and you as an IT professional should know best when your application works as expected. You as a professional should have balls to tell your customer that if he think can get somewhere else better, he should go there or perhaps build the application himself; that's what have done in the past with positive results :)

Regarding security; perhaps for their confidence you can encrypt the web.config and show them, but actually it means nothing; if someone can access the server, they could decode it. On the other hand, someone that want to break in to your database should pass trough a lot of barriers. It's hard to break in, perhaps impossible. Another options is simply blocking connections from outside network the network or ip range or whatever. I think this shouldn't be something to worry about.

There much more and either more realistic concerns to worry about, such as preventing cross site scripting and such common treats.

Answer 3

One possibility is to encrypt the section in the web.config. So, only user who can access the webserver directly can decrypt this section.

Here is how this works with the help of the iisreg-tool: http://msdn.microsoft.com/en-us/library/zhhddkxy.aspx

Answer 4

To make it more secure, and satisfy your customer you can use Tunneling between your computers.

You setup a server tunneling program where the database exist, and client tunneling programs on the client computer. You connect the one computer to the other via tunneling, and the database connection happens over tunneling.

And everything is exchange high secure (and compressed if the tunneling support it).

http://en.wikipedia.org/wiki/Tunneling_protocol

http://en.wikipedia.org/wiki/HTTP_tunnel

Ps I connect to my server only via tunneling for anything that I do.

Answer 5

The client is wrong introducing another tier would not automatically improve security.

In a nutshell use SQL server roles for data access for example the built in data_reader and data_writer roles are a good place to start. Always use the most appropriate least privilege account for the application. If you only need to read data use an account that only has access to read.

Use Windows authentication where possible, if this isn't possible then at least encrypt the connectionstring.

More information on how to do what I've described can be found at http://msdn.microsoft.com/en-us/library/ff650037.aspx#pagpractices0001_dataaccess

Answer 6

Security is always a trade-off. What is the client really afraid of?

Having database credential "in the clear"? I have seen auditors point this out as a potential vulnerability, but really, if someone has compromised your web server they can run arbitrary code against the database, so encrypting database credentials doesn't really buy you much.

Your web app should be using a minimal-rights user to connect to the database, so compromising the web server should only give you the rights to read & update data. How would that change if everything went through a web services layer? Again, there is a very real cost - in complexity, and in performance - by going to a web services layer. Only the client can answer whether or not that cost is worth it.

Answer 7

If this is a web project, you need to change IIS servers running user to a domain user and give permission on sql server to that user. Than you can use SSPI on your connection string like below. Like this, you don't need to keep your username or password clearly on web.config.

<configuration>
  <system.web>
    <identity impersonate="true"/> 

  </system.web>

and your connectionString

"Integrated Security=SSPI;Initial Catalog=TestDb;Data Source=10.10.10.10"

Answer 8

You could enable Encrypted connections in your database and tell the client that the connections are encrypted so fully secure?