Connectionstring

 

SQL Server
This is a compiled connection strings reference list on how to connect to SQL Server.

ODBC
Standard Security
Driver= {SQL Server}; Server= myServerAddress; Database= myDataBase; Uid= myUsername; Pwd= myPassword;
	COPY TO CLIPBOARD
Trusted connection
Driver= {SQL Server}; Server= myServerAddress; Database= myDataBase; Trusted_Connection= Yes;
	COPY TO CLIPBOARD
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}; Server= myServerAddress; Database= myDataBase;
	COPY TO CLIPBOARD
OLE DB, OleDbConnection (.NET)
Standard Security
Provider= sqloledb; Data Source= myServerAddress; Initial Catalog= myDataBase; User Id= myUsername; Password= myPassword;
	COPY TO CLIPBOARD
Trusted connection
Provider= sqloledb; Data Source= myServerAddress; Initial Catalog= myDataBase; Integrated Security= SSPI;
Use serverName/instanceName 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.	COPY TO CLIPBOARD
Prompt for username and password
This one is a bit tricky. First set the connection object's Provider property to "sqloledb". Thereafter set the connection object's Prompt property to adPromptAlways. Then use the connection string to connect to the database.
oConn.Provider = "sqloledb" oConn.Properties("Prompt") = adPromptAlways Data Source= myServerAddress; Initial Catalog= myDataBase;
	COPY TO CLIPBOARD
Connect via an IP address
Provider= sqloledb; Data Source= 190.190.200.100,1433; Network Library= DBMSSOCN; Initial Catalog= myDataBase; User ID= myUsername; Password= myPassword;
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 >>	COPY TO CLIPBOARD
SqlConnection (.NET)
Standard Security
Data Source= myServerAddress; Initial Catalog= myDataBase; User Id= myUsername; Password= myPassword;
	COPY TO CLIPBOARD
Standard Security 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= myServerAddress; Database= myDataBase; User ID= myUsername; Password= myPassword; Trusted_Connection= False;
	COPY TO CLIPBOARD
Trusted Connection
Data Source= myServerAddress; Initial Catalog= myDataBase; Integrated Security= SSPI;
	COPY TO CLIPBOARD
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= myServerAddress; Database= myDataBase; Trusted_Connection= True;
Use serverName/instanceName 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.	COPY TO CLIPBOARD
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. 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 >>	COPY TO CLIPBOARD
Specifying packet size
Server= myServerAddress; Database= myDataBase; User ID= myUsername; Password= myPassword; 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 ("Failed to reserve contiguous memory"), check this out >>	COPY TO CLIPBOARD
Data Shape
MS Data Shape
Provider= MSDataShape; Data Provider= SQLOLEDB; Data Source= myServerAddress; Initial Catalog= myDataBase; User ID= myUsername; Password= myPassword;
Want to learn data shaping? Check out 4GuyfFromRolla's great article about Data Shaping >> List of all SqlConnection connection string properties >>	COPY TO CLIPBOARD