Database connection String Encyclopedia

1. SQL Server

1> ODBC

L Standard Security: "Driver={sql Server};" Server=aron1;database=pubs; Uid=sa; PWD=ASDASD; "

L Trusted Connection: "Driver={sql Server};" Server=aron1;database=pubs; Trusted_connection=yes; "

L Prompt for username and password:

Oconn.properties ("Prompt") = adPromptAlways

oConn.Open "Driver={sql Server}; Server=aron1;database=pubs; "

2> OLE DB, OleDbConnection (. NET)

L Standard Security: "Provider=sqloledb;data source=aron1;initial catalog=pubs; User Id=sa; PASSWORD=ASDASD; "

L Trusted Connection:

"Provider=sqloledb;data source=aron1;initial catalog=pubs;integrated Security=sspi;"

(Use Servername/instancename as Data Source to use an Specifik SQL Server instance, only SQLServer2000)

L Prompt for username and password:

Oconn.provider = "SQLOLEDB"
Oconn.properties ("Prompt") = adPromptAlways
oConn.Open "Data source=aron1;initial catalog=pubs;"

L Connect via a IP address:

"Provider=sqloledb;data source=190.190.200.100,1433; Network Library=dbmssocn;initial catalog=pubs; User Id=sa; PASSWORD=ASDASD; "
(DBMSSOCN=TCP/IP instead of Named pipes, at the "the" of the Data Source is the port to use (1433 is the default))

3> SqlConnection (. NET)

L Standard Security:

"Data source=aron1;initial catalog=pubs; User Id=sa; PASSWORD=ASDASD; "
-OR-
"Server=aron1;database=pubs; User Id=sa; PASSWORD=ASDASD; Trusted_connection=false "
(Both connection strings produces the same result)

L Trusted Connection:

"Data source=aron1;initial catalog=pubs;integrated Security=sspi;"
-OR-
"Server=aron1;database=pubs; Trusted_connection=true; "
(Both connection strings produces the same result)
(Use Servername/instancename as Data Source to use an Specifik SQL Server instance, only SQLServer2000)

L Connect via a IP address:

"Data source=190.190.200.100,1433; Network Library=dbmssocn;initial catalog=pubs; User Id=sa; PASSWORD=ASDASD; "
(DBMSSOCN=TCP/IP instead of Named pipes, at the "the" of the Data Source is the port to use (1433 is the default))

L Declare the SqlConnection:

C#:
Using System.Data.SqlClient;
SqlConnection osqlconn = new SqlConnection ();
Osqlconn.connectionstring= "My connection string";
Osqlconn.open ();
VB.net:
Imports System.Data.SqlClient
Dim Osqlconn As SqlConnection = New SqlConnection ()
Osqlconn.connectionstring= "My connection string"
Osqlconn.open ()

4> Data Shape

L MS Data Shape:
"Provider=msdatashape;data provider=sqloledb;data source=aron1;initial catalog=pubs; User Id=sa; PASSWORD=ASDASD; "
Want to learn data shaping? Check out 4GuyfFromRolla ' s great article about Data shaping >>

5> Read More

l to define which network protocol to use
Example:
"Provider=sqloledb;data source=190.190.200.100,1433; network LIBRARY=DBMSSOCN; Initial catalog=pubs; User Id=sa; PASSWORD=ASDASD; "

Name	Network Library
Dbnmpntw	Win32 Named Pipes
Dbmssocn	Win32 Winsock TCP/IP
Dbmsspxn	Win32 spx/ipx
Dbmsvinn	Win32 Banyan Vines
Dbmsrpcn	Win32 multi-protocol (Windows RPC)

Important note!
When connecting through the SQLOLEDB provider use the syntax network
And when connecting through MSDASQL provider use the syntax NETWORK=DBMSSOCN

L All SqlConnection connection string properties

This table shows all connection string properties for the Ado.net SqlConnection object. Most of the properties are also used in ADO. All properties and descriptions are from MSDN.

Name

Default

Description