SQL Server 2005 連接配置

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
  
  

•  Encrypt data sent over network:
  
  "Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;Encrypt=yes"




•  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"




•  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