SQL Server 2005数据库连接串

SQL Native Client ODBC Driver

• 标准连接:
  
  "Driver={SQL Native Client};Server=Aron1;Database=pubs;UID=sa;PWD=asdasd;"
  
• 信任连接:
  
  "Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;"

  等同于
  Integrated Security=SSPI equals Trusted_Connection=yes

  
  
• 提示用户名和密码:
  
  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"

  等同于
  MultipleActiveResultSets=true equals MARS_Connection=yes
  
  

  
  
• 通过网络发送数据时加密:
  
  "Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;Encrypt=yes"
  
• 连接SQL实例时附加一个数据文件:
  
  "Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;"
     - 或 -
  "Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=|DataDirectory|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).

  
  

SQL Native Client 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
  

  
  
• Encrypt data sent over network:
  
  "Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_Connection=yes;Encrypt=yes"
  
• Attach a database file on connect to a local SQL Server Express instance:
  
  "Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;"
     - or -
  "Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilename=|DataDirectory|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).

  
  

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)

  
  
• 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
  

  
  
• Attach a database file on connect to a local SQL Server Express instance:
  
  "Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;"
     - or -
  "Server=.\SQLExpress;AttachDbFilename=|DataDirectory|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|\mydb.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)

  
  

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
  
  
  

  The context connection lets you execute Transact-SQL statements in the same context (connection) that your code was invoked in the first place.