Standard security
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
Trusted Connection
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
Oracle
1.OLE DB, OleDbConnection (.NET)
Standard security
This connection string uses a provider from Microsoft.
Provider=msdaora;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;
Trusted connection
Provider=msdaora;Data Source=MyOracleDB;Persist Security Info=False;Integrated Security=Yes;
2.Provider from Oracle.
Standard Security
Proovider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;
Trusted Connection
Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1;
DB2
DB2 .Net Data Provider (IBM.Data.DB2)
Standard
Server=myAddress:myPortNumber;Database=myDataBase;UID=myUsername;PWD=myPassword;
The Server key value syntax is a server name / ip address and an optional port number (note the : in between).
Defining the connection pooling pool size
Server=myAddress:myPortNumber;Database=myDataBase;UID=myUsername;PWD=myPassword;Max Pool Size=100;Min Pool Size=10;
Disable connection pooling
Server=myAddress:myPortNumber;Database=myDataBase;UID=myUsername;PWD=myPassword; Pooling=false;
Connection pooling, time in pool
Server=myAddress:myPortNumber;Database=myDataBase;UID=myUsername;PWD=myPassword; Connection Lifetime=60;
Defines how many seconds the connection can remain idle in the pool before its removed from the pool.
Connection pooling, do not pool
Server=myAddress:myPortNumber;Database=myDataBase;UID=myUsername;PWD=myPassword; Connection Reset=false;
Specifying schema
Server=myAddress:myPortNumber;Database=myDataBase;UID=myUsername;PWD=myPassword; CurrentSchema=mySchema;
All unqualified SQL objects used with the current connection will be qualified with the provide schema name
OLE DB, OleDbConnection (.NET) from Microsoft
TCP/IP
Provider=DB2OLEDB;Network Transport Library=TCPIP;Network Address=XXX.XXX.XXX.XXX;Initial Catalog=MyCtlg;Package Collection=MyPkgCol;Default Schema=Schema;User ID=myUsername;Password=myPassword;
APPC
Provider=DB2OLEDB;APPC Local LU Alias=MyAlias;APPC Remote LU Alias=MyRemote;Initial Catalog=MyCtlg;Package Collection=MyPkgCol;Default Schema=Schema;User ID=myUsername;Password=myPassword;
IBM's OLE DB Provider (shipped with IBM DB2 UDB v7 or above)
TCP/IP
Provider=IBMDADB2;Database=myDataBase;Hostname=myServerAddress;Protocol=TCPIP; Port=50000;Uid=myUsername;Pwd=myPassword;
ODBC
Standard
Driver={IBM DB2 ODBC DRIVER};Database=myDataBase;Hostname=myServerAddress;Port=1234; Protocol=TCPIP;Uid=myUsername;Pwd=myPassword;MySQL
MySQL Connector/ODBC 2.50 (MyODBC 2.50)
Local database
Driver={mySQL};Server=localhost;Option=16834;Database=myDataBase;
Remote database
Driver={mySQL};Server=myServerAddress;Option=131072;Stmt=;Database=myDataBase; User=myUsername;Password=myPassword;
Specifying TCP/IP port
Driver={mySQL};Server=myServerAddress;Port=3306;Option=131072;Stmt=; Database=myDataBase; User=myUsername;Password=myPassword;
The driver defaults to port value 3306, if not specified in the connection string, as 3306 is the default port for MySQL.
MySQL Connector/ODBC 3.51 (MyODBC 3.51)
Local database
Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=myDataBase; User=myUsername;Password=myPassword;Option=3;
Remote database
Driver={MySQL ODBC 3.51 Driver};Server=data.domain.com;Database=myDataBase;User=myUsername; Password=myPassword;Option=3;
Specifying TCP/IP port
Driver={MySQL ODBC 3.51 Driver};Server=data.domain.com;Port=3306;Database=myDataBase;User=myUsername; Password=myPassword;Option=3;
The driver defaults to port value 3306, if not specified in the connection string, as 3306 is the default port for MySQL.
Specifying character set
Driver={MySQL ODBC 3.51 Driver};Server=data.domain.com;charset=UTF8;Database=myDataBase;User=myUsername; Password=myPassword;Option=3;
OLE DB, OleDbConnection (.NET)
Standard
Provider=MySQLProv;Data Source=mydb;User Id=myUsername;Password=myPassword;
MySQL Connector/Net (.NET)
Standard
Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
Default port is 3306.
Specifying port
Server=myServerAddress;Port=1234;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
Download the driver at MySQL Developer Zone >>
Named pipes
Server=myServerAddress;Port=-1;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
It is the port value of -1 that tells the driver to use named pipes network protocol. This is available on Windows only. The value is ignored if Unix socket is used.
Multiple servers
Use this to connect to a server in a replicated server configuration without concern on which server to use.
Server=serverAddress1 & serverAddress2 & etc..;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
Using encryption
This one activates SSL encryption for all data sent between the client and server. The server needs to have a certificate installed.
Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword; Encryption=true;
This option is available from Connector/NET version 5.0.3. In earlier versions, this option has no effect.
Specifying default command timeout
Use this one to specify a default command timeout for the connection. Please note that the property in the connection string does not supercede the individual command timeout property on an individual command object.
Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;default command timeout=20;
This option is available from Connector/NET version 5.1.4.
Specifying connection attempt timeout
Use this one to specify the length in seconds to wait for a server connection before terminating the attempt and receive an error.
Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;Connection Timeout=5;
Inactivating prepared statements
Use this one to instruct the provider to ignore any command prepare statements and prevent corruption issues with server side prepared statements.
Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;Ignore Prepare=true;
The option was added in Connector/NET version 5.0.3 and Connector/NET version 1.0.9.
Specifying port
Use this one to specify what port to use for the connection.
Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;Port=3306;
The port 3306 is the default MySql port.
The value is ignored if Unix socket is used.
Specifying network protocol
Use this one to specify which network protocol to use for the connection.
Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword; Protocol=socket;
"socket" is the default value used if the key isn't specified. Value "tcp" is an equivalent for "socket".
Use "pipe" to use a named pipes connection, "unix" for a Unix socket connection and "memory" to use MySQL shared memory.
Specifying character set
Use this one to specify which character set to use to encode queries sent to the server.
Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword; CharSet=UTF8;
Note that resultsets still are returned in the character set of the data returned.
Specifying shared memory name
Use this one to specify the shared memory object name used for the communication.
Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;Shared Memory Name=MYSQL;
This one is applicable only when the connection protocol value is set to "memory".
MySqlConnection (.NET)
eInfoDesigns.dbProvider
Data Source=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Command Logging=false;
SevenObjects MySqlClient (.NET)
Standard
Host=myServerAddress;UserName=myUsername;Password=myPassword;Database=myDataBase;
Core Labs MySQLDirect (.NET)
Standard
User ID=root;Password=myPassword;Host=localhost;Port=3306;Database=myDataBase; Direct=true;Protocol=TCP;Compress=false;Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0;
Read more at Core Lab >>
And on the product page >>
MySQLDriverCS (.NET)
Standard
Location=myServerAddress;Data Source=myDataBase;User ID=myUsername;Password=myPassword;Port=3306;Extended Properties="""";
This is a free simple .NET compliant MySQL driver.
Access
OLE DB, OleDbConnection (.NET)
Standard security
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=;
With database password
This is the connection string to use when you have an access database protected with a password using the Set Database Password function in Access.
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Jet OLEDB:Database Password=MyDbPassword;
Workgroup (system database)
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Jet OLEDB:System Database=system.mdw;
Workgroup (system database) specifying username and password
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Jet OLEDB:System Database=system.mdw;User ID=myUsername;Password=myPassword;
DataDirectory functionality
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\myDatabase.mdb;User Id=admin;Password=;
Access2007
Standard security
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;
With database password
This is the connection string to use when you have an Access 2007 database protected with a password using the "Set Database Password" function in Access.
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Jet OLEDB:Database Password=MyDbPassword;
DataDirectory functionality
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\myAccess2007file.accdb;Persist Security Info=False;
Excel
Standard
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
Standard alternative
Try this one if the one above is not working. Some reports that Excel 2003 need the exta OLEDB; section in the beginning of the string.
OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
Important note!
The quota " in the string needs to be escaped using your language specific escape syntax.
c#, c++ \"
VB6, VBScript ""
xml (web.config etc) "
or maybe use a single quota '.
"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.
"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.
SQL syntax "SELECT * FROM [sheet1$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.
Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance.
If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: "Could not decrypt file."
