WindowsDevCenter.com

ADO.NET, Part 1
Pages: 1, 2

Connecting to a SQL Server Database

To read and write information to and from a SQL Server database, it is necessary first to establish a connection to the database. This is done with the SqlConnection object, found in the System.Data.SqlClient namespace. Here's an example:

' Open a database connection.
Dim strConnection As String = _
   "Data Source=localhost;Initial Catalog=Northwind;" _
   & "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open(  )

This code fragment instantiates an object of type SqlConnection, passing its constructor a connection string. Calling the SqlConnection object's Open method opens the connection. A connection must be open for data to be read or written, or for commands to be executed. When you're finished accessing the database, use the Close method to close the connection:

' Close the database connection.
cn.Close(  )

The connection string argument to the SqlConnection class's constructor provides information that allows the SqlConnection object to find the SQL Server database. The connection string shown in the earlier code fragment indicates that the database is located on the same machine that is running the code snippet (Data Source=localhost), that the database name is Northwind (Initial Catalog=Northwind), and that the user ID that should be used for logging in to SQL Server is the current Windows login account (Integrated Security=True). Table 8-1 shows the valid SQL Server connection string settings.

**Table 8-1:** **SQL Server connection string settings**
Setting	Default Value	Description
`Addr`		Synonym for `Data` `Source`.
`Address`		Synonym for `Data` `Source`.
`Application Name`		The name of the client application. If provided, SQL Server uses this name in its `sysprocesses` table to help identify the process serving this connection.
`AttachDBFilename`		Synonym for `Initial` `File` `Name`.
`Connect` `Timeout`	15	Synonym for `Connection Timeout`.
`Connection Timeout`	15	The number of seconds to wait for a login response from SQL Server. If no response is received during this period, an SqlException exception is thrown. This setting corresponds to the SqlConnection object's ConnectionTimeout property.
`Current` `Language`		The language to use for this session with SQL Server. The value of this setting must match one of the entries in either the "name" column or the "alias" column of the "master.dbo.syslanguages" system table. If this setting is not specified, SQL Server uses either its system default language or a user-specific default language, depending on its configuration. The language setting affects the way dates are displayed and may affect the way SQL Server messages are displayed. Search for "SQL Server Language Support" in SQL Server Books Online for more information.
`Data` `Source`		The name or network address of the computer on which SQL Server is located. This setting corresponds to the SqlConnection object's DataSource property.
`extended` `properties`		Synonym for `Initial` `File` `Name`.
`Initial` `Catalog`		The name of the database to use within SQL Server. This setting corresponds to the SqlConnection object's Database property.
`Initial` `File` `Name`		The full pathname of the primary file of an attachable database. If this setting is specified, the `Initial` `Catalog` setting must also be specified. Search for "Attaching and Detaching Databases" in SQL Server Books Online for more information. `AttachDBFilename` and `extended` `properties` are synonyms for `Initial` `File` `Name`.
`Integrated` `Security`	``false`'	Indicates whether to use NT security for authentication. A value of ``true`' or ``sspi`' (Security Support Provider Interface) indicates that NT security should be used. A value of ``false`' indicates that SQL Server security should be used. Search for "How SQL Server Implements Security" in SQL Server Books Online for more information.
`Net`	``dbmssocn`'	Synonym for `Network` `Library`.
`Network` `Address`		Synonym for `Data` `Source`.
`Network` `Library`	``dbmssocn`'	The name of the .dll that manages network communications with SQL Server. The default value, ``dbmssocn`', is appropriate for clients that communicate with SQL Server over TCP/IP. Search for "Communication Components" and "Net-Libraries and Network Protocols" in SQL Server Books Online for more information.
`Password`		The SQL Server login password for the user specified in the `User` `ID` setting.
`Persist` `Security` `Info`	``false`'	Specifies whether SqlConnection object properties can return security-sensitive information while a connection is open. Before a connection is opened, its security-sensitive properties return whatever was placed in them. After a connection is opened, properties return security-sensitive information only if the `Persist` `Security Info` setting was specified as ``true`'. For example, if `Persist Security Info` is ``false`' and the connection has been opened, the value returned by the SqlConnection object's ConnectionString property does not show the `Password` setting, even if the `Password` setting was specified.
`Pwd`		Synonym for `Password`.
`Server`		Synonym for `Data` `Source`.
`Trusted`_`Connection`	``false`'	Synonym for `Integrated` `Security`.
`User ID`		The SQL Server login account to use for authentication.
`Workstation ID`	the client computer name	The name of the computer that is connecting to SQL Server.

SQL Server Authentication

Before a process can access data that is located in a SQL Server database, it must log in to SQL Server. The SqlConnection object communicates with SQL Server and performs this login based on information provided in the connection string. Logging in requires authentication. Authentication means proving to SQL Server that the process is acting on behalf of a user who is authorized to access SQL Server data. SQL Server recognizes two methods of authentication:

SQL Server Authentication, which requires the process to supply a username and password that have been set up in SQL Server by an administrator. Beginning with SQL Server 2000, this method of authentication is no longer recommended (and is disabled by default).

Integrated Windows Authentication, in which no username and password are provided. Instead, the Windows NT or Windows 2000 system on which the process is running communicates the user's Windows login name to SQL Server. The Windows user must be set up in SQL Server by an administrator in order for this to work.

To use SQL Server Authentication:

(SQL Server 2000 only) Enable SQL Server Authentication. In Enterprise Manager, right-click on the desired server, click Properties, and then click the Security tab. Select "SQL Server and Windows" and click OK.

The network administrator sets up a login account using Enterprise Manager, specifying that the account will use SQL Server Authentication and supplying a password. Programming books (including this one) typically assume the presence of a user named "sa" with an empty password, because this is the default system administrator account set up on every SQL Server installation (good administrators change the password, however).

The network administrator assigns rights to this login account as appropriate.

The data access code specifies the account and password in the connection string passed to the SqlConnection object. For example, the following connection string specifies the "sa" account with a blank password:

"Data Source=SomeMachine; Initial Catalog=Northwind; User ID=sa; Password="

To use Integrated Windows Authentication:

The network administrator sets up the login account using Enterprise Manager, specifying that the account will use Windows Authentication and supplying the Windows user or group that is to be given access.

The network administrator assigns rights to this login account as appropriate.

The data access code indicates in the connection string that Integrated Windows Security should be used, as shown here:

"Data Source= SomeMachine; Initial Catalog=Northwind; Integrated Security=True"

When using Integrated Windows Authentication, it is necessary to know what Windows login account a process will run under and to set up appropriate rights for that login account in SQL Server Enterprise Manager. A program running on a local machine generally runs under the login account of the user that started the program. A component running in Microsoft Transaction Server (MTS) or COM+ runs under a login account specified in the MTS or COM+ Explorer. Code that is embedded in an ASP.NET web page runs under a login account specified in Internet Information Server (IIS). Consult the documentation for these products for information on specifying the login account under which components run. Consult the SQL Server Books Online for information on setting up SQL Server login accounts and on specifying account privileges.

Next week, check out the next excerpt installment that shows you how to connect to an OLE DB data source using ADO.NET.

View catalog information for Programming Visual Basic .NET

Return to the .NET DevCenter.