-
【
SQL
SERVER
】
Standard Security
Data Source
=
mySer
verAddress;
Initial Catalog
=<
/p>
myDataBase;
User
Id
=
myUsername;
Passw
ord
=
myPassword;
Standard Security alternative syntax
This connection string produces the
same result as the previous one. The reason to
include it is to
point out that some
connection string keywords have many equivalents.
Server
=
myServerAd
dress;
Database
=
my
DataBase;
User
ID
=
myUsername;
Password
=
myPassword;
Trusted_Connec
tion
=
False;
Trusted Connection
Data Sour
ce
=
myServerAddress;
Initial Catalog
=
myDataBase
;
Integrated
Security
< br>=
SSPI;
Trusted
Connection alternative syntax
This
connection string produce the same result as the
previous one. The reason to include it is to
point out that some connection string
keywords have many equivalents.
Server<
/p>
=
myServerAddress;
D
atabase
=
myDataBase;
Trusted_Connection
=
True;
Use serverNameinstanceName as
Data Source to use a specific SQL Server instance.
Please note
that the multiple SQL
Server instances feature is available only from
SQL Server version 2000 and
not in any
previous versions.
Connecting to an SQL Server instance
The syntax of specifying
the server instance in the value of the server key
is the same for all
connection strings
for SQL Server.
Server
=
myServerNametheInstanceName;
Datab
ase
=
myDataBase;
Tr
usted_Connection
=
T
rue;
Trusted Connection from a
CE device
Often a Windows CE device is
not authenticated and logged in to a domain. To
use SSPI or trusted
connection /
authentication from a CE device, use this
connection string.
Data Source
=
myServerAddress;
Initial C
atalog
=
myDataBase;
Integrated
Security
=
SSPI;
User ID
=
myDomainmyUsername;
Password
=
myPassword;
Note
that this will only work on a CE device.
Read more about connecting to SQL
Server from CE devices here
Connect via an IP address
Data Source
=
190.1
90.200.100,1433;
Network Library
< br>=
DBMSSOCN;
Initial
Catalog
=
myDataBase;
User ID
=
myUsername;
Password
=
myPassword;
Specifying packet size
Serve
r
=
myServerAddress;
Database
=
myDataBase;
User
ID
=
myUse
rname;
Password
=
my
Password;
Trusted_Connection
=
False;
Packet
Size
=
4096;
By default, the Microsoft .NET
Framework Data Provider for SQL Server sets the
network packet
size to 8192 bytes. This
might however not be optimal, try to set this
value to 4096 instead.
The default
value of 8192 might cause errors as well
(
check this out
Microsoft OLE DB Provider
for SQL Serv
er
Type:
OLE DB Provider
Usage:
Provider=sqloledb
Manufacturer:
Microsoft
More info about this provider
?
Customize string
example values ?
Standard Security
Provider
=
sqloledb;
Data Sour
ce
=
myServerAddress;
Initial Catalog
=
myDataBase
;
User
Id
=
< br>myUsername;
Password
=
myPassword;
Trusted
connection
Provider
=
< br>sqloledb;
Data Source
=
myServerAddress;
Initial
< br>Catalog
=
myDataBase;
Integrated
Security
=
SSPI;
Use serverNameinstanceName as Data
Source to use a specific SQL Server instance.
Please note
that the multiple SQL
Server instances feature is available only from
SQL Server version 2000 and
not in any
previous versions.
Connecting to an SQL Server instance
The syntax of specifying
the server instance in the value of the server key
is the same for all
connection strings
for SQL Server.
Provider
=
sqloledb;
Data Source
=
myServerNametheInstanceName;
Initial
Catalog
=
myDataBase;
Integrated
Security
=
SSPI;
Prompt for username and password
This one is a bit tricky. First set the
connection object's Provider property to
set the connection object's Prompt
property to adPromptAlways. Then use the
connection string to
connect to the
database.
er =
ties(
Data Source
=
myServerAddress;
Initial Catalog
=
myDataBase;<
/p>
Connect via an IP address
Pr
ovider
=
sqloledb;
D
ata Source
=
190.190.200.100,1
433;
Network
Library
=
DBMSSOCN;
Initial Catalog<
/p>
=
myDataBase;
User <
/p>
ID
=
myUsername;
Password
=
myPassword;<
/p>
DBMSSOCN=TCP/IP. This is how
to use TCP/IP instead of Named Pipes. At the end
of the Data
Source is the port to use.
1433 is the default port for SQL Server.
How to define which network protocol to
use
Disable connection pooling
This one is usefull when receving
errors
application role
enabled connection. Application pooling (or OLE DB
resource pooling) is on by
default.
Disabling it can help on this error.
Pr
ovider
=
sqloledb;
D
ata Source
=
myServerAddress;<
/p>
Initial Catalog
=
my
DataBase;
User
ID
=
myUsername;
Password
=
myPassword;
OLE DB
Services
=
-2;
.NET Framework Data Provider for OLE
DB
Type:
.NET
Framework Wrapper Class Library
Usage:
onnection
Manufacturer:
Microsoft
More info about this wrapper class
library ?
Customize string
example values ?
Bridging to OLE DB Provider for SQL
Server
This is just one connection
string sample for the wrapping OleDbConnection
class that calls the
underlying OLEDB
provider. See respective OLE DB provider for more
connection strings to use
with this
class.
Provider
=
S
QLOLEDB;
Data Source
=
< br>myServerAddress;
Initial Catalog
=
myDataBase;
User
Id
=
myUsername;
< br> Password
=
myPassword;
Microsoft SQL Server ODBC
Driver
Type:
ODBC Driver
Usage:
Driver={SQL Server}
Manufacturer:
Microsoft
Customize string
example
values ?
Standard Security
Driver
=
{SQL
Server};
Server
=
myServerAddress;
Database
=
myDataBase;
Uid
=<
/p>
myUsername;
Pwd
=
myPass
word;
Trusted connection
Driver
=
{SQL
Server};
Server
=
myServerAddress;
Database
=
myDataBase;
Trusted_Connecti
on
=
Yes;
Prompt for username and password
This one is a bit tricky. First you
need to set the connection object's Prompt
property to
adPromptAlways. Then use
the connection string to connect to the database.
ties(
Driver
=
{SQL Serve
r};
Server
=
myServe
rAddress;
Database
=
myDataBase;
SQL Server Native Client
10.0 OLE DB Provider
Type:
OLE DB Provider
Usage:
Provider=SQLNCLI10
Manufacturer:
Microsoft
More info about this provider
?
Customize string
example values ?
Standard security
Note that
the SQL Server Native Client OLE DB Provider does
not support SQL Server 7.0.
Provider
=
SQLNCLI10;
Server
=
myServerAddress;
Dat
abase
=
myDataBase;
Uid
=
myUsername;
<
/p>
Pwd
=
myPassword;
Trusted connection
Provider<
/p>
=
SQLNCLI10;
Server<
/p>
=
myServerAddress;
D
atabase
=
myDataBase;
Trusted_Connection
=
yes;
Equivalent key-
value pair:
Connecting to an SQL Server instance
The syntax of specifying
the server instance in the value of the server key
is the same for all
connection strings
for SQL Server.
Provider
=
SQLNCLI10;
Server
=
myServerNametheInstanceName;
Dat
abase
=
myDataBase;
Trusted_Connection
=
yes;
Prompt for username and
password
This one is a bit tricky.
First you need to set the connection object's
Prompt property to
adPromptAlways. Then
use the connection string to connect to the
database.
ties(
=
SQLNCLI10;
Server
=
myServerAddress
;
DataBase
=
myDataB
ase;
Encrypt data sent over
network
Provider
=
SQLNCLI10;
Server
=
myServerAddress;
Database
=
myDataBase;
Trusted_
Connection
=
yes;
En
crypt
=
yes;
SQL Native Client 9.0 OLE DB
provider
Type:
OLE DB Provider
Usage:
Provider=SQLNCLI
Manufacturer:
Microsoft
More info about this provider
?
Customize string
example values ?
Standard security
Note that
the SQL Server Native Client OLE DB Provider does
not support SQL Server 7.0.
Provider
=
SQLNCLI;
Server
=
myServerAddress;
Datab
ase
=
myDataBase;
Ui
d
=
myUsername;
Pwd
=
myPassword;
Trusted connection
Provider<
/p>
=
SQLNCLI;
Server
=
myServerAddress;
Dat
abase
=
myDataBase;
Trusted_Connection
=
yes;
Equivalent key-value
pair:
Connecting to an SQL Server instance
The syntax of specifying
the server instance in the value of the server key
is the same for all
connection strings
for SQL Server.
Provider
=
SQLNCLI;
Server
=
myServerNametheInstanceName;
Datab
ase
=
myDataBase;
<
/p>
Trusted_Connection
=
yes;
Prompt for username and
password
This one is a bit tricky.
First you need to set the connection object's
Prompt property to
adPromptAlways. Then
use the connection string to connect to the
database.
ties(
=
SQLNCLI;
Server
=
myServerAddress;<
/p>
DataBase
=
myDataBas
e;
Encrypt data sent over network
< br>Provider
=
SQLNCLI;
< br>Server
=
myServerAddress;
Database
=
myDataBase
;
Trusted_Connection
< br>=
yes;
Encrypt
=<
/p>
yes;
SQL Server Native Client
10.0 ODBC Driver
Type:
ODBC Driver
Usage:
Driver={SQL Server Native Client 10.0}
Manufacturer:
Microsoft
More info about this driver
?
Customize string
example values ?
Standard security
Driver
=
{SQL
Server Native Client
10.0};
S
erver
=
myServerAddress;
Database
=
myDataBase;
Uid
=
myUsername;
Pwd
=
myPasswo
rd;
Trusted Connection
Driver
=
{SQL
Server Native Client
10.0};
S
erver
=
myServerAddress;
Database
=
myDataBase;
Trusted_Connection
=
y
es;
Equivalent key-value
pair:
Connecting to an SQL Server instance
The syntax of specifying
the server instance in the value of the server key
is the same for all
connection strings
for SQL Server.
Driver
=
{SQL
Server Native Client 10.0};
Server
=
myServerNametheInstanceName;
Database
=
m
yDataBase;
Trusted_Connection
=
yes;
Prompt for
username and password
This one is a bit
tricky. First you need to set the connection
object's Prompt property to
adPromptAlways. Then use the connection
string to connect to the database.
ties(
Driver
=
{SQL
Server Native Client
10.0};
S
erver
=
myServerAddress;
Database
=
myDataBase;
Encrypt data sent over network
Driver
=
{SQL
Server Native Client
10.0};
S
erver
=
myServerAddress;
Database
=
myDataBase;
Trusted_Connection
=
yes;
Encrypt
=
yes;
SQL Native Client
9.0 ODBC Driver
Type:
ODBC Driver
Usage:
Driver={SQL Native Client}
Manufacturer:
Microsoft
More info about this driver
?
Customize string
example values ?
Standard security
Driver
=
{SQL
Native Client};
Server
=
myServerAddress;
Database
=
myDataBase;
Uid
=
myUsername;
Pwd
=
myPassword;
Trusted Connection
Driver
=
{SQL
Native Client};
Server
=
myServerAddress;
Database
=
myDataBase;
Tru
sted_Connection
=
yes;
< br>
Equivalent key-value pair:
Connecting to
an SQL Server instance
The
syntax of specifying the server instance in the
value of the server key is the same for all
connection strings for SQL Server.
Driver
=
{SQL
Native
Client};
Server
=
myServerNametheInstanceName;
Database
=
myDataBase;
Trusted_Connection
=
yes;
Prompt for
username and password
This one is a bit
tricky. First you need to set the connection
object's Prompt property to
adPromptAlways. Then use the connection
string to connect to the database.
ties(
Driver
=
{SQL
Native Client};
Server
=
myServerAddress;
Database
=
myDataBase;
Encrypt data
sent over network
Driver
=
{SQL
Native Client};
Server
=
myServerAddress;
Database
=
myDataBase;
Tru
sted_Connection
=
yes;
< br>Encrypt
=
yes;
.NET Framework Data Provider for
ODBC
Type:
.NET Framework Wrapper Class Library
Usage:
nnection
Manufacturer:
Microsoft
More info about this wrapper class
library ?
Customize string
example values ?
Bridging to SQL Native Client 10.0 ODBC
Driver
This is just one connection
string sample for the wrapping OdbcConnection
class that calls the
underlying ODBC
Driver. See respective ODBC driver for more
connection strings to use with this
class.
Driver
=
{SQL
Server Native Client
10.0};
S
erver
=
myServerAddress;
Database
=
myDataBase;
Uid
=
myUsername;
Pwd
=
myPasswo
rd;
SQLXML 3.0 OLEDB
Provider
Type:
OLE DB Provider
Usage:
Provider=SQLXMLOLEDB.3.0;Data
Provider=sqloledb
Manufacturer:
Microsoft
More info about this provider
?
Customize string
example values ?
Using SQL Server Ole Db
The
SQLXML version 3.0 restricts the data provider to
SQLOLEDB only.
Provider
=
SQLXMLOLEDB.3.0;
Data Provider
=
SQLOLEDB;
Data
Source
=
myServerAddress
;
Initial Catalog
=
myDataBase;
User
Id
=
myUsername;
Password
=
myPassword;
MSDataShape
Type:
Data Shaping COM
component
Usage:
Provider=MSDataShape;Data
Provider=providername
Manufacturer:
Microsoft
More info about this wrapper COM
component ?