sql server 2005连接字符串

SQL Native Client ODBC Driver

• 标准连接: TL2-YR,eS
  
  "Driver={SQL Native Client};Server=Aron1;Database=pubs;UID=sa;PWD=asdasd;"
  6mY?A <SAO
  k0W2[z$F=Z
• 信任连接: h=9 Zl c
  
  "Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;"

  等同于 Kt-$\ ._x
  Integrated Security=SSPI equals Trusted_Connection=yes

• 提示用户名和密码: t y+ 1_
  
  oConn.Properties("Prompt") = adPromptAlways MWD 7r< '1
  oConn.Open "Driver={SQL Native Client};Server=Aron1;DataBase=pubs;"
  w"3R H}d_P
  zF C V e7
• Enabling MARS (multiple active result sets): zi , QTW$
  
  "Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;MARS_Connection=yes"

  等同于 Uz F]o8 S
  MultipleActiveResultSets=true equals MARS_Connection=yes ?~-q&ZPeo
  Lt1h o8X S
  

• 通过网络发送数据时加密: lGZ\! A' f
  
  "Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;Encrypt=yes"
  %%Dd^+4O 8
  G g]6b 8
• 连接SQL实例时附加一个数据文件: QP_HQ+ eB
  
  "Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;" 2Rg+5J" c9
      - 或 - z z{~ # U$
  "Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;" dR > g{qH
      (use |DataDirectory| when your database file resides in the data directory) |Kz \(A<n

  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).

<*.sTFk3 @
SdkT'a J

• Standard security: EU WcnO
  
  "Provider=SQLNCLI;Server=Aron1;Database=pubs;UID=sa;PWD=asdasd;"
  ]& Ic 8 'R
  3 C 7"
• Trusted connection: -- : - Y
  
  "Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_Connection=yes;"

  Equivalents DB m9}` j
  Integrated Security=SSPI equals Trusted_Connection=yes

• Prompt for username and password: [7qZ/0Le3
  
  oConn.Properties("Prompt") = adPromptAlways ~ [7yB9
  oConn.Open "Provider=SQLNCLI;Server=Aron1;DataBase=pubs;"
  D?M;9$7 Rw
  ,c;_YyNAi
• Enabling MARS (multiple active result sets): 6 s5^L1 6
  
  "Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_Connection=yes;MarsConn=yes"

  Equivalents `J &- )/
  MarsConn=yes equals MultipleActiveResultSets=true equals MARS_Connection=yes n_=Z/Y
  

• Encrypt data sent over network: Z"UUlG ?Ib
  
  "Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_Connection=yes;Encrypt=yes"
  2N2l U6M
  # sS*W>
• Attach a database file on connect to a local SQL Server Express instance: V y9(U* e?
  
  "Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;" IU9jk0[[
      - or - S@d2Yd jU
  "Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;" D wZ 9~E
      (use |DataDirectory| when your database file resides in the data directory) 4#EkI PO
  

  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).

*Xpo (e/
jA S ^pJLM

• Standard Security: |zQ ]n|)q
  
  "Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;" V:3/N}7s*_
      - or - gE Y M
  "Server=Aron1;Database=pubs;User ID=sa;Password=asdasd;Trusted_Connection=False" GZ=M A;
      (both connection strings produces the same result) lk6t>o+UH
  . BZqY\ D%
  W{K >t
• Trusted Connection: T,_i:PphyN
  
  "Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;" Yx; &JJfr
      - or - Zz$YQ8s)n~
  "Server=Aron1;Database=pubs;Trusted_Connection=True;" h [p-
      (both connection strings produces the same result) iE(R 8

  (use serverName\instanceName as Data Source to use an specifik SQLServer instance)

• Connect via an IP address: d FXc u6s.
  "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): oeD,twOhM
  
  "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 ? i 9y;MT
  

• Attach a database file on connect to a local SQL Server Express instance: ) m5A _slg
  
  "Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;" Ts_3~xs 7
      - or - Vs 0bxa e
  "Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;" \ ) 6B @
      (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: WZtI ybv
  
  "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)

|".]?}gX]N
>x>PSx alE

• C#: r{:?z V0P.
  
  using(SqlConnection connection = new SqlConnection("context connection=true")) D <6 K` H6
  { F\H.1mz8]0
       connection.Open(); O# & sB g]
       // Use the connection :N.C}i| rm
  } z }NhhSGv}
  fVU}gg}CEK
  O8(OP%T P?
• Visual Basic: 'U LDB4 !R
  
  Using connection as new SqlConnection("context connection=true") 5<< 0$c :8
       connection.Open() lDhJsm,E%1
       ' Use the connection S nieB Wrs
  End Using 'l W 9S
  }&k Vz
  %o): _r
  

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

SQL Native Client OLE DB Provider

SqlConnection (.NET)

Context Connection - connecting to "self" from within your CLR stored prodedure/function