Database connection string

Understanding Database Authentication Options

Before any application can issue SELECT, INSERT, UPDATE, or DELETE queries to a Microsoft SQL Server database, the database first must identify the requestor. This process is known as authentication and SQL Server provides two methods of authentication:

• Windows Authentication – the process under which the application is running is used to communicate with the database. When running an ASP.NET application through Visual Studio 2005’s ASP.NET Development Server, the ASP.NET application assumes the identity of the currently logged on user. For ASP.NET applications on Microsoft Internet Information Server (IIS), ASP.NET applications usually assume the identity of domainName\MachineName or domainName\NETWORK SERVICE, although this can be customized.
• SQL Authentication – a user ID and password values are supplied as credentials for authentication. With SQL authentication, the user ID and password are provided in the connection string.

Windows authentication is preferred over SQL authentication because it is more secure. With Windows authentication the connection string is free from a username and password and if the web server and database server reside on two different machines, the credentials are not sent over the network in plain-text. With SQL authentication, however, the authentication credentials are hard-coded in the connection string and are transmitted from the web server to the database server in plain-text.

These tutorials have used Windows authentication. You can tell what authentication mode is being used by inspecting the connection string. The connection string in Web.config for our tutorials has been:

Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|\NORTHWND.MDF; Integrated Security=True; User Instance=True

The “Integrated Security=True” and lack of a username and password indicate that Windows authentication is being used. In some connection strings the term “Trusted Connection=Yes” or “Integrated Security=SSPI” is used instead of “Integrated Security=True”, but all three indicate the use of Windows authentication.

The following example shows a connection string that uses SQL authentication. Note the credentials embedded within the connection string:

Server=serverName; Database=Northwind; uid=userID; pwd=password