Connecting to Microsoft SQL Server
To connect to a Microsoft SQL Server database, you simply specify the OLE DB provider for SQL Server in the ADO connection string, and then include any additional provider-specific arguments required. The following is a summary of the connection string arguments you will most frequently use when connecting to a SQL Server database:
□ Provider=SQLOLEDB;
□ Data Source=server name; —This will typically be the NetBIOS name of the computer that SQL Server is installed on. If SQL Server is installed as a named instance, the server name will have the following syntax: NetBIOS Name\SQL Server Instance Name.
□ Initial Catalog=database name; —Unlike Access, one instance of SQL Server can contain many databases. This argument will contain the name of the database you want to connect to.
□ User ID=username; — The username for SQL Server authentication.
□ Password=password; —The password for SQL Server authentication.
□ Network Library=netlib; —By default, the SQL Server OLE DB provider will attempt to use named pipes network protocol to connect to SQL Server. This is required for using Windows integrated security (explained later). There are many instances, however, where it is not possible to use named pipes. These include accessing SQL Server from a Windows 9x operating system and accessing SQL Server over the Internet. In these cases, the preferred protocol for connecting to SQL Server is TCP/IP. This can be specified on each machine by using the SQL Server Client Network Utility, or you can simply use the Network Library connection string argument to specify the name of the TCP/IP network library, which is dbmssocn.
□ Integrated Security=SSPI; —This connection string argument specifies that you want to use Windows integrated security rather than SQL Server authentication. The User ID and Password arguments will be ignored if this argument is present.
A Note About SQL Server Security
SQL Server can be set to use three types of security: SQL Server authentication, Windows integrated security, and mixed mode. SQL Server authentication means that separate user accounts must be added to SQL Server, and each user must supply a SQL Server username and password to connect.
This type of security is most commonly used when SQL Server must be accessed from outside the network. With Windows integrated security, SQL Server recognizes the same usernames and passwords that are used to log in to the Windows network. Mixed mode simply means you can use either one of the two.
Following are examples of two different SQL Server connection strings. The first example shows a connection string that uses SQL Server authentication and the TCP/IP connection protocol. The second example shows a connection string that uses Windows integrated security:
Public Const gsCONNECTION As String = _ "Provider=SQLOLEDB;" & _
"Data Source=ComputerName\SQLServerName;" & _ "Initial Catalog=Northwind;" & _ "User ID=User;Password=password;" & _ "Network Library=dbmssocn"
Public Const gsCONNECTION As String = _ "Provider=SQLOLEDB;" & _
"Data Source=ComputerName\SQLServerName;" & _ "Initial Catalog=Northwind;" & _ "Integrated Security=SSPI"
Average user rating: 5 stars out of 2 votes
Post a comment