Using Authentication Modes with ADO.NET

Best Practices: Using Authentication Modes with ADO.NET

Authentication is the process of determining if a user is who he claims to be. When your application connects to a SQL Server database, you have a choice of Windows authentication or SQL Server authentication. Windows authentication offers greater protection. But sometimes, you might need to use SQL authentication to connect to the database using a number of different accounts. Make use of following guidelines to protect your approach as much as possible.

 

1.       If possible, use Windows Authentication: Use Windows authentication when your application connects to SQL Server or other databases that support Windows authentication because

a.       Accounts are centralized and managed by your Active Directory, so user works with a single (Windows) security model rather than the separate SQL Server security model.

b.       No user names and passwords are embedded in the code

c.       No user names and passwords are sent over the network in clear text

d.       Strong password policies can be controlled and enforced by domain or local security policy. E.g. password expiration, minimum length.

 

The following example uses Windows authentication with the ADO.NET data provider for SQL Server

 

SqlConnection Conn = new SqlConnection("Data Source=dbserver; Initial Catalog=pubs; Integrated Security=SSPI;");

 

The following example uses the ADO.NET data provider for OLE DB data sources.

 

OleDbConnection Conn = new OleDbConnection("Provider=SQLOLEDB; Data Source=dbserver; Integrated Security=SSPI; Initial Catalog=northwind");

 

2.       If you use SQL Server Authentication, use strong passwords: If you use SQL server Authentication, use a least-privileged account with a strong password to prevent an attacker from guessing the password. A strong password should be at least 7 characters in length and contain a combination of alphanumeric, numeric and special characters.

 

Avoid using blank password with sa account as in the following connection string.

 

String SqlConnectionString = "Server=YourServer\Instance; Database=YourDatabase; uid=sa; pwd=;"

 

3.       If You Use SQL Server Authentication, Protect Credentials on the Network: When you connect to SQL Server with SQL authentication, the credentials are not encrypted prior to transmission across the network. So, an attacker can easily capture credentials by using a network monitor. Therefore, you should use Internet Protocol Security (IPSec) or Secure Sockets Layer (SSL) to create an encrypted communication channel between web server and database while building ASP.NET applications.

 

Use SSL when you need granular channel protection for a particular application, instead of for all applications and services running on a computer. Here is a link that shows how to use SSL to secure communication with SQL Server: http://msdn2.microsoft.com/en-us/library/aa302414.aspx

 

If you want to secure all of the IP traffic between the Web and database servers, use IPSec. You can also use IPSec to restrict which computers can communicate with one another. This link shows how to use IPSec to provide secure communication between two servers: http://msdn2.microsoft.com/en-us/library/aa302413.aspx

 

4.       If You Use SQL Server Authentication, Protect Credentials in the Configuration Files: To protect credentials in configuration files, place connection strings inside the <connectionStrings> section of web.config file (for ASP.NET apps) or app.config file (for Windows apps). Following example shows a part of configuration file.

 

<connectionStrings>

<add name="MyConnectionString" connectionString="Data Source=dbserver; Initial Catalog=pubs; Integrated Security=SSPI;"/>

</connectionStrings>

 

            Following code can be used to retrieve above connection string.

 

using System.Configuration;

...

string connectionString = ConfigurationManager.ConnectionStrings["MyConnectionString "].ConnectionString;

 

For additional protection of ASP.NET apps, encrypt <connectionStrings> section using either RSA or DPAPI encryption with help of aspnet_regiis utility. For more information about how to use DPAPI and RSA encryption to encrypt configuration file elements, see:

 

1.       How To: Encrypt Configuration Sections in ASP.NET 2.0 Using DPAPI, at http://msdn2.microsoft.com/en-us/library/ms998280.aspx

2.    How To: Encrypt Configuration Sections in ASP.NET 2.0 Using RSA, at http://msdn2.microsoft.com/en-us/library/ms998283.aspx

 

Note: Encrypting connection strings with aspnet_regiis does not change the code required to access the string because the decryption occurs automatically.

 

 

 

Thanks & Regards,

Arun Manglick

SMTS || Microsoft Technology Practice || Bridgestone - Tyre Link || Persistent Systems || 3023-6258

 

DISCLAIMER ========== This e-mail may contain privileged and confidential information which is the property of Persistent Systems Pvt. Ltd. It is intended only for the use of the individual or entity to which it is addressed. If you are not the intended recipient, you are not authorized to read, retain, copy, print, distribute or use this message. If you have received this communication in error, please notify the sender and delete all copies of this message. Persistent Systems Pvt. Ltd. does not accept any liability for virus infected mails.