SQL Native Client ODBC Driver
Standard security:
"Driver={SQL Native Client};Server=Aron1;Database=pubs;UID=sa;PWD=asda sd;"
Trusted connection:
"Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connect ion=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_Connect ion=yes;MARS_Connection=yes"Encrypt data sent over network:
"Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connect ion=yes;Encrypt=yes"
Attach a database file on connect to a local SQL Server Express instance:
"Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=c:\as d\qwe\mydbfile.mdf;Database=dbname;Trusted_Connect ion=Yes;"
- or -
"Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=|Data Directory|mydbfile.mdf;Database=dbname;Trusted_Con nection=Yes;"
(use |DataDirectory| when your database file resides in the data directory)
Why is the "Database" parameter needed? Answer: If the database was previously attached, SQL Server does not reattach it (it uses the attached database as the default for the connection).Using SQL Server 2005 Express? Don't miss the server name syntax: SERVERNAME\SQLEXPRESS (Substitute "SERVERNAME" with the name of the computer)
SQL Native Client OLE DB Provider
Standard security:
"Provider=SQLNCLI;Server=Aron1;Database=pubs;UID=s a;PWD=asdasd;"
Trusted connection:
"Provider=SQLNCLI;Server=Aron1;Database=pubs;Trust ed_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;Trust ed_Connection=yes;MarsConn=yes"
Encrypt data sent over network:
"Provider=SQLNCLI;Server=Aron1;Database=pubs;Trust ed_Connection=yes;Encrypt=yes"
Attach a database file on connect to a local SQL Server Express instance:
"Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFile name=c:\asd\qwe\mydbfile.mdf;Database=dbname;Trust ed_Connection=Yes;"
- or -
"Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFile name=|DataDirectory|mydbfile.mdf;Database=dbname;T rusted_Connection=Yes;"
(use |DataDirectory| when your database file resides in the data directory)Why is the "Database" parameter needed? Answer: If the database was previously attached, SQL Server does not reattach it (it uses the attached database as the default for the connection).
Using SQL Server 2005 Express? Don't miss the server name syntax: SERVERNAME\SQLEXPRESS (Substitute "SERVERNAME" with the name of the computer
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=Tru e;"
(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=Tru e;MultipleActiveResultSets=true"
Attach a database file on connect to a local SQL Server Express instance:
"Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\m ydbfile.mdf;Database=dbname;Database=dbname;Truste d_Connection=Yes;"
- or -
"Server=.\SQLExpress;AttachDbFilename=|DataDirecto ry|mydbfile.mdf;Database=dbname;Trusted_Connection =Yes;"
(use |DataDirectory| when your database file resides in the data directory)Why is the "Database" parameter needed? Answer: If the database was previously attached, SQL Server does not reattach it (it uses the attached database as the default for the connection).
Using "User Instance" on a local SQL Server Express instance:
"Data Source=.\SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|\myd b.mdf;user instance=true;"
The "User Instance" functionality creates a new SQL Server instance on the fly during connect. This works only on a local SQL Server 2005 instance and only when connecting using windows authentication over local named pipes. The purpose is to be able to create a full rights SQL Server instance to a user with limited administrative rights on the computer. To enable the functionality: sp_configure 'user instances enabled','1' (0 to disable)
Using SQL Server 2005 Express? Don't miss the server name syntax: SERVERNAME\SQLEXPRESS (Substitute "SERVERNAME" with the name of the computer)
Context Connection - connecting to "self" from within your CLR stored prodedure/function
C#:
using(SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
// Use the connection
}
Visual Basic:
Using connection as new SqlConnection("context connection=true")
connection.Open()
' Use the connection
End Using
پاسخ با نقل قول
