In Focus

ASP.NET Application Security With SQL Server

In this article, you will learn about ASP.NET Application Security with SQL Server.

Overview

Today, application security has become one of the most  important factors in any environmental setup developed in .NET, Java and so on. Due to this, in a good organization, the network team is vital. Network team sits 24x7 and observes the organization with the help of their monitoring tool. It’s just like you have a security guard or watchmen sitting 24x7 in his cabin and keeping an eye on who is going in and going out. Let’s start by talking about what are the ways that we can secure our .NET application with SQL Server.

Introduction

When you search on the net, you will get tons of Applications regarding how to secure a .NET application which can be in Oracle, SQL, Mongo DB and so on. You will see various ways pointed out in an application like Cross page scripting, SQL Injection and so on. We will be pointing out today how we can secure our application at DB level end.

Just for reference, kindly go through this article,

I just read this article randomly here on C# Corner and decided to write an extension to this article. This is just an extension of this article, you could say. So let’s start...

Security during installation:

  • SQL Server

    While you are installing SQL Server on Server, security features come by default and install DBA login with their SA (System Administrator) password privilege or with windows Authentication and so on. With that password they make users login and provide restricted access like dB reader privileges, by default database mappings, and these users are mapped with the application.

    NOTE

    ‘’sa’’ users are never used; i.e., they are never mapped with the Application. A user is made with sufficient privileges and that user is mapped with an Application which helps in better streamlining of the security.

  • .NET Installation

    Any App server requires login and password to logon. Make sure you don’t login with an administrator or root in Windows Server... Make a user with limited privileges, as shown below:

    install

    For better streamlining of security, it is necessary to have Application Server and DB Server both running on different Servers. In any organization (unless it’s small), you will never see both running on the same Server.

    NOTE

    While installing your Webapps, exe and so on on; Windows Server makes sure your App server is hardened. Hardening of a Server is done for security reasons. Make sure you have all the security loop holes blocked.

    file
Make sure you have a security option which is properly defined. These are some useful links that might help you.
  • Protocols and their communication

    In SQL Server, we all know the default port for SQL is 1433 and for Oracle is 1521. If you have that permission or DBA has that permission, kindly keep a different port number which can be of different combination like 2489 and so on, which is one of the ways to secure our .NET application.

    properties

    Another point is, make sure you hide SQL Server Browser service. The advantage of hiding User Browser service is that the user is not able to see SQL Server instance.  You can see the screenshot below as DBA team has hidden SQL Server Browser service .

    service

  • http:// Protocol IPSEC and SSL

    When a communication is happening between our .NET application and SQL Server, it uses IPSEC and SSL. IPSEC is used to transfer the data within the organization. If you want to transfer data outside, SSL is used. The main advantage of SSL is data is encrypted here and it is moved across the network. With SSL, you can provide a certificate when the transfer of data is done.

    The disadvantage of using SSL is that when a data is moved across the network, the client who has MDAC cannot access the data. For certificate setup and in SSL, you need the root privileges to SSL certificate.

    certificate

    IPSEC does not provide any data security it is done on both windows and server level end and doesn’t require any great configuration. Generally data transfer in an organization is done through firewall and MMC as seen in the below screenshot

    firewall

  • Authentication

    Authentication i the SQL Server is very important as it actually authenticates the user by which ways he wants to login into DB as there are two ways of the authentication -- Windows authentication and SQL Server authentication.

    Windows authentication mode depends on Windows login users. In Windows authentication, it accesses SQL objects and Windows authentication uses Kerberos authentication.

    SQL authentication mode here takes the responsibility of SQL Server by specifying particular user and their passwords having particular membership role and so on.

    Authentication

  • Web.config Encryption

    As we all know encryption in web.config file is important -- be it in audit findings for security purposes, readability of the connection strings and so on. It’s important that any application web.config should be encrypted.

    NOTE - If possible, make encryption and decryption of your web.config file access DLL.

    dll

    dll

  • SQL Server and its features

    SQL Server 2005 has a drawback related to security and it is that when you create a user, you don’t have any option to assign privileges to it as it becomes a point breaking option for an attacker. After many versions, they had resolved this point as of now, you can see that you create a user, and you can make a “read-only user” and and get mapped to the Application.

Conclusion

There are many points on how we can secure our application, I have touchedon a few of them. Hope it was helpful. Let me know your questions related to this article.