Almost every ASP.NET application uses databases and the most common one to be used is SQL Server. In this article, we will discuss the means of web applications connecting to SQL Server.
Common ASP.NET connection string mistake
When we develop an application on ASP.NET that uses a database, we inevitably get a multi-level application in the deep backend of which lies a SQL Server with a database. It can be located on the same machine as our application or can be located on a remote The SQL Server and our application is only connected by a thin string, namely a SQL Server connection string.
The most common mistake is when this SQL connection string is embedded in the code with numerous web forms. It should be located in a more decent place like web.config. The harsh truth of life is that during development, in most cases, the connection string looks like this:
User ID=sa; Password=; Initial Catalog=myDatabase; Data Source=(local)
This is not a problem using this type of connection string in the process of development, but moving to an actual server can lead to failures.
SQL Server connection strings
We have two types of ASP.NET to SQL Server connection string.
They are the Regular one:
Data Source=SomeAddress; Initial Catalog=LittleDB; User Id=JohnDou; Password=qwerty;
And then the so-called SQL Trusted Connection:
Data Source=SomeAddress; Initial Catalog=LittleDB; Integrated Security=SSPI;
Server=SomeAddress; Database=LittleDB; Trusted_Connection=True;
In a Regular SQL connection string, we specify the username and password in the connection string. Here we can use the names of local Windows users (local accounts), user names of the AD domain, or the names of SQL Server users if both authentication types are enabled on the SQL Server: Windows and SQL.
When we use the ASP.NET SQL Trusted Connection, only the Windows authentication mode of the SQL server can be used. In this case, the username and password are not specified in the connection string. Instead, user data is taken from the current security context of the application.
ASP.NET SQL Server Regular connection or Trusted Connection?
Give priority to using SQL Trusted Connection whenever it is possible. Regular connection should be used only in case if Trusted Connection cannot be established, for example:
- SQL Server is installed on another network;
- For some reason, we are forced to use the user impersonation mode.
Let’s say we are forced to use the regular SQL connection string and have to specify the username and password in the connection string. This decision can be caused by several reasons.
First, the database server is located in a different network and in a different domain which is not connected with a trusted relationship with our domain.
The second case is hosting when we are unable to use Windows accounts.
The third case is when the user impersonation mode of ASP.NET is enabled in our application.
This mode is described in the web.config file with the following settings:
<identity impersonate = ”true”>
In this case, the use of a Trusted Connection will cause each connection to the SQL Server to be established on behalf of the user initiating the web request. This option is used very rarely. If we want to avoid this, the only option is to specify the name and password in the ASP.NET SQL connection string.
Now we know what ASP.NET SQL Server connection string types exist and know that it is best to useASP.NET SQL Server Trusted Connection string to achieve the greatest security of the application.