SQL 2005 Strings

SQL Native Client ODBC Driver


Standard security:

"Driver={SQL Native Client};
Server=MyServerName;
Database=pubs;
UID=myUsername;
PWD=myPassword;"

Trusted connection:

"Driver={SQL Native Client};
Server=MyServerName;
Database=pubs;
Trusted_Connection=yes;"

Note: 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=UrServerName;
Database=pubs;
Trusted_Connection=yes;
MARS_Connection=yes"

Note: MultipleActiveResultSets=true equals
MARS_Connection=yes



Encrypt data sent over network:

"Driver={SQL Native Client};
Server=UrServerName;
Database=pubs;
Trusted_Connection=yes;
Encrypt=yes"



Attach a database file on connect to a local
SQL Server Express instance:

"Driver={SQL Native Client};Server=.\SQLExpress;
AttachDbFilename=c:\asd\qwe\mydbfile.mdf;
Database=dbname;Trusted_Connection=Yes;"

OR

"Driver={SQL Native Client};
Server=.\SQLExpress;
AttachDbFilename=|DataDirectory|mydbfile.mdf;
Database=dbname;Trusted_Connection=Yes;"

(Note: use |DataDirectory| when your database file
resides in the data directory)


SQL Native Client OLE DB Provider


Standard security:

"Provider=SQLNCLI;
Server=UrServerName;
Database=pubs;
UID=myUsername;PWD=myPassword;"



Trusted connection:

"Provider=SQLNCLI;
Server=UrServerName;
Database=pubs;
Trusted_Connection=yes;"

Note: 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=UrServerName;
Database=pubs;
Trusted_Connection=yes;
MarsConn=yes"

Note: MarsConn=yes equals
MultipleActiveResultSets=true equals
MARS_Connection=yes


Encrypt data sent over network:

"Provider=SQLNCLI;
Server=UrServerName;
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;"

(Note: use |DataDirectory| when your database file
resides in the data directory)



SqlConnection (.NET)


Standard Security:

"Data Source=UrServerName;
Initial Catalog=pubs;
User Id=myUsername;
Password=myPassword;"

OR

"Server=UrServerName;
Database=pubs;
User ID=myUsername;
Password=myPassword;
Trusted_Connection=False"



Trusted Connection:

"Data Source=UrServerName;
Initial Catalog=pubs;
Integrated Security=SSPI;"

OR

"Server=UrServerName;
Database=pubs;
Trusted_Connection=True;"


(Note: 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=myUsername;
Password=myPassword;"

(Note: 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=UrServerName;
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;"

(Note: use |DataDirectory| when your database file resides
in the data directory)


Using "User Instance" on a local SQL Server Express
instance:

"Data Source=.\SQLExpress;
integrated security=true;
attachdbfilename=|DataDirectory|\mydb.mdf;user instance=true;"

Note: 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)
Using SQL Server 2005 Express? Don't miss the server name
syntax: SERVERNAME\SQLEXPRESS (Substitute "SERVERNAME" with the name of the computer)



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.