Export (0) Print
Expand All

Connecting to a SQL Server Data Source

SQL Server 2000

  New Information - SQL Server 2000 SP3.

ADO can use any OLE DB provider to establish a connection. The provider is specified through the Provider property of the Connection object. Microsoft® SQL Server™ 2000 applications use SQLOLEDB to connect to an instance of SQL Server, although existing applications can also use MSDASQL to maintain backward compatibility.

Using the Execute method of the Connection object is one way to execute an SQL statement against a SQL Server data source.

The Connection object allows you to:

  • Configure a connection.

  • Establish and terminate sessions with data sources.

  • Identify an OLE DB provider.

  • Execute a query.

  • Manage transactions on the open connection.

  • Choose a cursor library available to the data provider.

There are some differences in connection properties between SQLOLEDB and MSDASQL. For information about connection properties for MSDASQL, see the MSDN Library at Microsoft Web site.

If you are writing a connection string for use with SQLOLEDB:

  • Use the Initial Catalog property to specify the database.

  • Use the Data Source property to specify the server name.

  • Use the Integrated Security keyword, set to a value of SSPI, to specify Windows Authentication (recommended),
    or
    use the User ID and Password connection properties to specify SQL Server Authentication.

    Security Note  When possible, use Windows Authentication. If Windows Authentication is not available, prompt users to enter their credentials at run time. Avoid storing credentials in a file. If you must persist credentials, you should encrypt them with the Win32® crypto API. For more information, see "The Crypto API Function" in the MSDN® Library at this Microsoft Web site.

If you are writing a connection string for use with MSDASQL:

  • Use the Database keyword or Initial Catalog property to specify the database.

  • Use the Server keyword or Data Source property to specify the server name.

  • Use the Trusted_Connection keyword, set to a value of yes, to specify Windows Authentication (recommended),
    or
    Use the UID keyword or User ID property, and the Pwd keyword or Password property to specify SQL Server Authentication.

    Security Note  When possible, use Windows Authentication. If Windows Authentication is not available, prompt users to enter their credentials at run time. Avoid storing credentials in a file. If you must persist credentials, you should encrypt them with the Win32 crypto API. For more information, see "The Crypto API Function" in the MSDN Library at this Microsoft Web site.

For more information about a complete list of keywords available for use with a SQLOLEDB connection string, see Connection Object.

Restrictions on Multiple Connections

SQLOLEDB does not allow multiple connections. Unlike MSDASQL, SQLOLEDB does not attempt to reconnect when the connection is blocked.

Examples
A. Using SQLOLEDB to connect to an instance of SQL Server: setting individual properties

The following Microsoft Visual Basic® code fragments from the ADO Introductory Visual Basic Sample show how to use SQLOLEDB to connect to an instance of SQL Server.

' Initialize variables.
Dim cn As New ADODB.Connection
. . .
Dim ServerName As String, DatabaseName As String

' Put text box values into connection variables.
ServerName = txtServerName.Text
DatabaseName = txtDatabaseName.Text

' Specify the OLE DB provider.
cn.Provider = "sqloledb"

' Set SQLOLEDB connection properties.
cn.Properties("Data Source").Value = ServerName
cn.Properties("Initial Catalog").Value = DatabaseName

' Windows NT authentication.
cn.Properties("Integrated Security").Value = "SSPI"

' Open the database.
cn.Open
B. Using SQLOLEDB to connect to an instance of SQL Server: connection string method

The following Visual Basic code fragment shows how to use SQLOLEDB to connect to an instance or SQL Server:

' Initialize variables.
Dim cn As New ADODB.Connection
Dim provStr As String

' Specify the OLE DB provider.
cn.Provider = "sqloledb"

' Specify connection string on Open method.
ProvStr = "Server=MyServer;Database=northwind;Trusted_Connection=yes"
cn.Open provStr
C. Using MSDASQL to connect to an instance of SQL Server

To use MSDASQL to connect to an instance of SQL Server, use the following types of connections.

The first type of connection is based on the ODBC API SQLConnect function. This type of connection is useful in situations where you do not want to code specific information about the data source. This may be the case if the data source could change or if you do not know its particulars.

In the code fragment shown, the ConnectionTimeout method sets the connection time-out value to 100 seconds. Next, the data source name, and authentication type are passed as parameters to the Open method of the Connection object, using an ODBC data source named MyDataSource that points to the northwind database on an instance of SQL Server.

Dim cn As New ADODB.Connection

cn.ConnectionTimeout = 100
' DSN connection
' cn.Open "DSN=MyDataSource;Trusted_Connection=yes;"

cn.Close

The second type of connection is based on the ODBC API SQLDriverConnect function. This type of connection is useful in situations where you want a driver-specific connection string. To make a connection, use the Open method of the Connection object and specify the driver, server name, authentication type, and database. You can also specify any other valid keywords to include in the connection string. For more information about the keyword list, see SQLDriverConnect.

Dim cn As New ADODB.Connection

' Connection to SQL Server without using ODBC data source.
cn.Open "Driver={SQL Server};Server=Server1;Database=northwind;Trusted_Connection=yes"

cn.Close

See Also
ADO Connection and Error Handling
Show:
© 2014 Microsoft