SQL Server
-
- ODBC
- Standard Security:
"Driver={SQL Server};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;" - Trusted connection:
"Driver={SQL Server};Server=Aron1;Database=pubs;Trusted_Connection=yes;" - Prompt for username and password:
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Driver={SQL Server};Server=Aron1;DataBase=pubs;"
- Standard Security:
- OLE DB, OleDbConnection (.NET)
- Standard Security:
"Driver={SQL Server};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;" - Trusted connection:
"Driver={SQL Server};Server=Aron1;Database=pubs;Trusted_Connection=yes;" - Prompt for username and password:
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Driver={SQL Server};Server=Aron1;DataBase=pubs;"
- Standard Security:
"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;" - Trusted Connection:
"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"(use serverName/instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)
- Prompt for username and password:
oConn.Provider = "sqloledb"
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Data Source=Aron1;Initial Catalog=pubs;" - Connect via an 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 end of the Data Source is the port to use (1433 is the default))
- ODBC
SqlConnection (.NET)
- 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)
- 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 SQLServer instance, only SQLServer2000)
- Connect via an 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 end of the Data Source is the port to use (1433 is the default))
- 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()
SQL Server 2005
- SQL Native Client ODBC Driver
- SQL Native Client OLE DB Provider
- SqlConnection (.NET)
- Standard security:
"Driver={SQL Native Client};Server=Aron1;Database=pubs;UID=sa;PWD=asdasd;"- Trusted connection:
"Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;"Equivalents
Integrated Security=SSPI equals Trusted_Connection=yes- Prompt for username and password:
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Driver={SQL Native Client};Server=Aron1;DataBase=pubs;"- Enabling MARS (multiple active result sets):
"Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;MARS_Connection=yes"Equivalents
MultipleActiveResultSets=true equals MARS_Connection=yes
Using MARS with SQL Native Client, by Chris Lee >>- Encrypt data sent over network:
"Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;Encrypt=yes"Download the SQL Native Client here >> (the package contains booth the ODBC driver and the OLE DB provider)
- Standard security:
"Provider=SQLNCLI;Server=Aron1;Database=pubs;UID=sa;PWD=asdasd;"- Trusted connection:
"Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_Connection=yes;"Equivalents
Integrated Security=SSPI equals Trusted_Connection=yes- Prompt for username and password:
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Provider=SQLNCLI;Server=Aron1;DataBase=pubs;"- Enabling MARS (multiple active result sets):
"Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_Connection=yes;MarsConn=yes"Equivalents
MarsConn=yes equals MultipleActiveResultSets=true equals MARS_Connection=yes
Using MARS with SQL Native Client, by Chris Lee >>- Encrypt data sent over network:
"Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_Connection=yes;Encrypt=yes"Download the SQL Native Client here >> (the package contains booth the OLE DB provider and the ODBC driver)
- 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)
- 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 SQLServer instance)- Connect via an 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 end of the Data Source is the port to use (1433 is the default))- Enabling MARS (multiple active result sets):
"Server=Aron1;Database=pubs;Trusted_Connection=True;MultipleActiveResultSets=true"Note! Use ADO.NET 2.0 for MARS functionality. MARS is not supported in ADO.NET 1.0 nor ADO.NET 1.1
Streamline your Data Connections by Moving to MARS, by Laurence Moroney, DevX.com >>
- Access
-
- ODBC
- OLE DB, OleDbConnection (.NET)
- Standard Security:
"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:/mydatabase.mdb;Uid=Admin;Pwd=;" - Workgroup:
"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:/mydatabase.mdb;SystemDB=C:/mydatabase.mdw;" - Exclusive:
"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:/mydatabase.mdb;Exclusive=1;Uid=admin;Pwd="
- Standard security:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=/somepath/mydb.mdb;User Id=admin;Password=;" - Workgroup (system database):
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=/somepath/mydb.mdb;Jet OLEDB:System Database=system.mdw;" - With password:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=/somepath/mydb.mdb;Jet OLEDB:Database Password=MyDbPassword;"
-