Connection strings for SQL Server
2012
.NET libraries for SQL Server 2012
- .NET Framework Data Provider for SQL Server -
SqlConnection
- .NET Framework Data Provider for ODBC -
OdbcConnection
- .NET Framework Data Provider for OLE DB -
OleDbConnection
- Context Connection
OLE DB providers for SQL Server 2012
ODBC drivers for SQL Server 2012
Developers Community
Find solutions
and post questions regarding connection string related issues.
.NET Framework Data Provider for SQL Server
Type = .NET
Framework Class Library
Usage = System.Data.SqlClient.SqlConnection
Manufacturer Microsoft
Standard Security
Server=myServerAddress;Database=myDataBase;User
Id=myUsername;Password=myPassword;
Trusted Connection
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;
Connection to a SQL Server instance
The server/instance
name syntax used in the server option is the same for all SQL Server
connection strings.
Server=myServerName\myInstanceName;Database=myDataBase;User
Id=myUsername;
Password=myPassword;
Trusted Connection from a CE device
A Windows CE
device is most often not authenticated and logged in to a domain but it is
possible to use SSPI or trusted connection and authentication from a CE device
using this connection string.
Data
Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
User ID=myDomain\myUsername;Password=myPassword;
Note that this
will only work on a CE device.
Connect via an IP address
Data Source=190.190.200.100,1433;Network
Library=DBMSSOCN;Initial Catalog=myDataBase;
User ID=myUsername;Password=myPassword;
DBMSSOCN=TCP/IP 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. Read more here.
Enable MARS
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;
MultipleActiveResultSets=true;
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;
Why is the
Database parameter needed? If the named database have already been attached,
SQL Server does not reattach it. It uses the attached database as the default
for the connection.
Attach a
database file, located in the data directory, on connect to a local SQL Server
Express instance
Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;
Trusted_Connection=Yes;
Why is the
Database parameter needed? If the named database have already been attached,
SQL Server does not reattach it. It uses the attached database as the default
for the connection.
User Instance
on local SQL Server Express
The User
Instance feature is deprecated with SQL Server 2012, use the SQL Server
Express LocalDB feature instead.
LocalDB automatic instance
Server=(localdb)\v11.0;Integrated
Security=true;
The first
connection to LocalDB will create and start the instance, this takes some time
and might cause a connection timeout failure. If this happens, wait a bit and
connect again.
LocalDB automatic instance with specific data file
Server=(localdb)\v11.0;Integrated
Security=true;AttachDbFileName=C:\MyFolder\MyData.mdf;
LocalDB named instance
To create a
named instance, use the SqlLocalDB.exe program. Example SqlLocalDB.exe
create MyInstance and SqlLocalDB.exe
start MyInstance
Server=(localdb)\MyInstance;Integrated
Security=true;
LocalDB named instance via the named pipes pipe name
The Server=(localdb)
syntax is not supported by .NET framework versions before 4.0.2. However the
named pipes connection will work to connect pre 4.0.2 applications to LocalDB
instances.
Server=np:\\.\pipe\LOCALDB#F365A78E\tsql\query;
Executing SqlLocalDB.exe
info MyInstance will get you
(along with other info) the instance pipe name such as
"np:\\.\pipe\LOCALDB#F365A78E\tsql\query".
LocalDB shared instance
Both automatic
and named instances of LocalDB can be shared.
Server=(localdb)\.\MyInstanceShare;Integrated
Security=true;
Use SqlLocalDB.exe
to share or unshare an instance. For example execute SqlLocalDB.exe
share "MyInstance" "MyInstanceShare" to share an instance.
Database mirroring
If you connect
with ADO.NET or the SQL Native Client to a database that is being mirrored,
your application can take advantage of the drivers ability to automatically
redirect connections when a database mirroring failover occurs. You must
specify the initial principal server and database in the connection string and
the failover partner server.
Data
Source=myServerAddress;Failover Partner=myMirrorServerAddress;
Initial Catalog=myDataBase;Integrated Security=True;
There is
ofcourse many other ways to write the connection string using database
mirroring, this is just one example pointing out the failover functionality.
You can combine this with the other connection strings options available.
Asynchronous processing
A connection to
SQL Server that allows for the issuing of async requests through ADO.NET
objects.
Server=myServerAddress;Database=myDataBase;Integrated
Security=True;
Asynchronous Processing=True;
SQL Server Native Client 11.0 OLE DB Provider
Type OLE DB
Provider
Usage Provider=SQLNCLI11
Manufacturer Microsoft
Standard security
Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase;Uid=myUsername;
Pwd=myPassword;
Are you using
SQL Server 2012 Express? Don't miss the server name syntax
Servername\SQLEXPRESS where you substitute Servername with the name of the
computer where the SQL Server 2012 Express installation resides.
Trusted connection
Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;
Equivalent
key-value pair: "Integrated Security=SSPI" equals
"Trusted_Connection=yes"
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=SQLNCLI11;Server=myServerName\theInstanceName;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.
oConn.Properties("Prompt")
= adPromptAlways
oConn.Open "Provider=SQLNCLI11;
Server=myServerAddress;DataBase=myDataBase;
Enable MARS
Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;
MARS Connection=True;
Encrypt data sent over network
Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;
Encrypt=yes;
Attach a database file on connect to a local SQL Server
Express instance
Provider=SQLNCLI11;Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf;
Database=dbname;Trusted_Connection=Yes;
Why is the
Database parameter needed? If the named database have already been attached,
SQL Server does not reattach it. It uses the attached database as the default
for the connection.
Attach a database file, located in the data directory, on
connect to a local SQL Server Express instance
Provider=SQLNCLI11;Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;
Database=dbname;Trusted_Connection=Yes;
Why is the
Database parameter needed? If the named database have already been attached,
SQL Server does not reattach it. It uses the attached database as the default for
the connection.
Database mirroring
If you connect
with ADO.NET or the SQL Native Client to a database that is being mirrored,
your application can take advantage of the drivers ability to automatically
redirect connections when a database mirroring failover occurs. You must
specify the initial principal server and database in the connection string and
the failover partner server.
Provider=SQLNCLI11;Data
Source=myServerAddress;Failover Partner=myMirrorServerAddress;
Initial Catalog=myDataBase;Integrated Security=True;
There is
ofcourse many other ways to write the connection string using database
mirroring, this is just one example pointing out the failover functionality.
You can combine this with the other connection strings options available.
SQL Server Native Client 11.0 ODBC
Driver
Type ODBC
Driver
Usage Driver={SQL Server Native Client 11.0}
Manufacturer Microsoft
Standard security
Driver={SQL
Server Native Client 11.0};Server=myServerAddress;Database=myDataBase;
Uid=myUsername;Pwd=myPassword;
Are you using
SQL Server 2012 Express? Don't miss the server name syntax
Servername\SQLEXPRESS where you substitute Servername with the name of the
computer where the SQL Server 2012 Express installation resides.
Trusted
Connection
Driver={SQL
Server Native Client 11.0};Server=myServerAddress;Database=myDataBase;
Trusted_Connection=yes;
Equivalent
key-value pair: "Integrated Security=SSPI" equals
"Trusted_Connection=yes"
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 11.0};Server=myServerName\theInstanceName;
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.
oConn.Properties("Prompt")
= adPromptAlways
Driver={SQL Server Native Client
11.0};Server=myServerAddress;Database=myDataBase;
Enable MARS
Driver={SQL Server Native Client
11.0};Server=myServerAddress;Database=myDataBase;
Trusted_Connection=yes;MARS_Connection=yes;
Encrypt data sent over network
Driver={SQL Server
Native Client 11.0};Server=myServerAddress;Database=myDataBase;
Trusted_Connection=yes;Encrypt=yes;
Attach a
database file on connect to a local SQL Server Express instance
Driver={SQL
Server Native Client 11.0};Server=.\SQLExpress;
AttachDbFilename=c:\asd\qwe\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;
Why is the
Database parameter needed? If the named database have already been attached,
SQL Server does not reattach it. It uses the attached database as the default
for the connection.
Attach a
database file, located in the data directory, on connect to a local SQL Server
Express instance
Driver={SQL
Server Native Client 11.0};Server=.\SQLExpress;
AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;
Why is the
Database parameter needed? If the named database have already been attached,
SQL Server does not reattach it. It uses the attached database as the default
for the connection.
Database mirroring
If you connect
with ADO.NET or the SQL Native Client to a database that is being mirrored,
your application can take advantage of the drivers ability to automatically
redirect connections when a database mirroring failover occurs. You must
specify the initial principal server and database in the connection string and
the failover partner server.
Driver={SQL
Server Native Client 11.0};Server=myServerAddress;
Failover_Partner=myMirrorServerAddress;Database=myDataBase;Trusted_Connection=yes;
There is
ofcourse many other ways to write the connection string using database
mirroring, this is just one example pointing out the failover functionality.
You can combine this with the other connection strings options available.
Please note if
you are using TCP/IP (using the network library parameter) and database
mirroring, including port number in the address (formed as
servername,portnumber) for booth the main server and the failover partner can
solve some reported issues.
.NET Framework Data Provider for ODBC
Type = .NET
Framework Wrapper Class Library
Usage = System.Data.Odbc.OdbcConnection
Manufacturer Microsoft
Use an ODBC driver from .NET
Driver={any
odbc driver's name};OdbcKey1=someValue;OdbcKey2=someValue;
See the
respective ODBC drivers connection strings options. The .net OdbcConnection
will just pass on the connection string to the specified ODBC driver. Read more
here.
.NET Framework Data Provider for OLE DB
Type = .NET
Framework Wrapper Class Library
Usage = System.Data.OleDb.OleDbConnection
Manufacturer Microsoft
Use an OLE DB provider from .NET
Provider=any oledb provider's
name;OledbKey1=someValue;OledbKey2=someValue;
See the
respective OLEDB provider's connection strings options. The .net
OleDbConnection will just pass on the connection string to the specified OLEDB
provider. Read more here.
SQLXML 4.0
OLEDB Provider
Type = OLE DB
Provider
Usage = Provider=SQLXMLOLEDB.4.0;Data Provider=providername
Manufacturer Microsoft
Using SQL Server Native Client provider
Provider=SQLXMLOLEDB.4.0;Data
Provider=SQLNCLI11;Data Source=myServerAddress;
Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
Context Connection
Type = .NET
Framework Class Library
Usage
Manufacturer Microsoft
Context Connection
Connecting to
"self" from within your CLR stored prodedure/function. The context
connection lets you execute Transact-SQL statements in the same context
(connection) that your code was invoked in the first place.
C#
using(SqlConnection connection = new SqlConnection("context
connection=true"))
{
connection.Open();
// Use the connection
}
VB.Net
Using connection as new SqlConnection("context
connection=true")
connection.Open()
' Use the connection
End Using
*****End*****
Type = .NET Framework Class Library
Manufacturer Microsoft
Password=myPassword;
User ID=myDomain\myUsername;Password=myPassword;
Connect via an IP address
User ID=myUsername;Password=myPassword;
MultipleActiveResultSets=true;
Trusted_Connection=Yes;
Trusted_Connection=Yes;
Initial Catalog=myDataBase;Integrated Security=True;
Asynchronous Processing=True;
Usage Provider=SQLNCLI11
Manufacturer Microsoft
Pwd=myPassword;
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.
oConn.Open "Provider=SQLNCLI11;
Server=myServerAddress;DataBase=myDataBase;
MARS Connection=True;
Encrypt data sent over network
Encrypt=yes;
Database=dbname;Trusted_Connection=Yes;
Attach a database file, located in the data directory, on connect to a local SQL Server Express instance
Database=dbname;Trusted_Connection=Yes;
Initial Catalog=myDataBase;Integrated Security=True;
Usage Driver={SQL Server Native Client 11.0}
Manufacturer Microsoft
Uid=myUsername;Pwd=myPassword;
Trusted Connection
Trusted_Connection=yes;
Database=myDataBase;Trusted_Connection=yes;
Driver={SQL Server Native Client 11.0};Server=myServerAddress;Database=myDataBase;
Trusted_Connection=yes;MARS_Connection=yes;
Trusted_Connection=yes;Encrypt=yes;
AttachDbFilename=c:\asd\qwe\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;
AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;
Failover_Partner=myMirrorServerAddress;Database=myDataBase;Trusted_Connection=yes;
Usage = System.Data.Odbc.OdbcConnection
Manufacturer Microsoft
.NET Framework Data Provider for OLE DB
Usage = System.Data.OleDb.OleDbConnection
Manufacturer Microsoft
Usage = Provider=SQLXMLOLEDB.4.0;Data Provider=providername
Manufacturer Microsoft
Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
Usage
Manufacturer Microsoft
C#
using(SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
// Use the connection
}
VB.Net
Using connection as new SqlConnection("context connection=true")
connection.Open()
' Use the connection
End Using
*****End*****
No comments:
Post a Comment