Connect to SQL Server using Windows Authentication or SQL Server Authentication

We can either use Windows authentication or SQL Server authentication for connecting to SQL Server using ADO.NET. In this post we will discuss Windows authentication and SQL Server authentication in detail.We will see how these two modes of authentication works and what is the benefit of each authentication mode over the other?

Windows Authentication

When you use windows authentication to connect to SQL Server from your application, the identity of application process that is requesting a connection to SQL Server is used to login to SQL Server and therefore you do not need to specify the user id and password in the connection string.
In case of windows application, the identity of application process will usually be the identity of logged on user who is executing the application. While for an ASP.NET applications it is slightly different. By default all ASP.NET applications on windows server 2003, run under 'Network Service' account. So when using windows authentication to connect to SQL Server, ASP.NET application logs in using credentials of 'Network Service' account.

For connecting to SQL Server from your application using windows authentication, you need to grant your application access to database in SQL Server. You can do that by following the steps given below:

1. Create a login in SQL Server for windows user who will be running the application or for 'network service' account in case of ASP.NET application.
2. After creating the login in SQL Server, create database user in required database.
3. Associate login with created user.
4. Assign one or more roles to database user.
5. Assign one or more permissions to each role that you assigned to user.Ideally you can assign roles the permission to execute stored procedures and do not assign direct access to tables

Now when your application connects with SQL Server it will have only those permissions that we have specified for that login.

Windows Authetication with ASP.NET Applications

As already mentioned above that when using windows authentication to connect to SQL Server the identity of application process is used to login to SQL Server.ASP.NET applications run under 'network service' account in windows server 2003 and so login to SQL Server using this account.

In case when more than one ASP.NET applications are hosted by a single web server, by default all of them are running under same 'network service' account. This means one application can access other applications database in SQL Server because all of them are using same login and are treated as single user, so they have to share the information.To prevent this situation and to introduce isolation between ASP.NET applications you can configure ASP.NET applications to run under different service accounts.

Following are the steps to create a custom service account for ASP.NET applications

1. Create a new user account
2. Assign ASP.NET permissions to the new account. To assign ASP.NET permissions use Aspnet_regiis.exe tool and run the following command : aspnet_regiis -ga MachineName\AccountName Where MachineName is the name of your server or the domain name if you are using a domain account, and AccountName is the name of your custom account.
3. Create an application pool running under the newly created account using IIS.
4. Assign ASP.NET application to that pool.

After configuring ASP.NET application to run under new pool, you can use the steps specified earlier to grant newly created account that will now be used by ASP.NET application access to SQL Server.

If we want that ASP.NET application accesses the database in SQL Server using the security context of window user requesting the web page from ASP.NET application rather than the security context of ASP.NET application process itself. We can do that by enabling the impersonation in ASP.NET application. We can enable impersonation by setting the 'impersonate' attribute of 'identity' element to true in web.config configuration file.

By enabling the impersonation in ASP.NET application, access to network resource, database resources etc is determined by privileges of the calling user rather than ASP.NET service account.

There are several advantage of using ASP.NET application service account to make calls on behalf of the original caller rather than using impersonation.

1. You can effectively use connection pooling while you cannot use connection pooling with impersonation model because database access is tied to security context of individual calling user rather than single service account.
2. You only need to grant single service account access to database in SQL Server rather than granting access to each calling user separately which can make management far more difficult.

SQL Server Authentication

In case of SQL Server authentication you need to specify userid and password in connection string to connect to SQL Server from your application. Since you need to specify userid and password in connection string it is desirable from security perspective that you encrypt your connection string. (You can refer to my previous blog to see how to encrypt the connection string)

To use the SQL Server authentication you need to follow the following steps

1. Create SQL Server login
2. Create user in required database and associate it with the new login
3. Assign one or more roles to the user
4. Assign one or more permissions to the role

After creating the login that SQL Server can authenticate and assigning permissions to login you can use the userid and password of login in connection string to connect to SQL Server.

Conclusion

It is better to use windows authentication when possible because you do not need to provide credentials in connection string thus enhancing security. While using SQL Server authentication you may need to take extra overhead for protecting the credentials specified in the connection string.