ASP and ASP.NET connection string examples for Microsoft SQL Server and MySQL

A connection string is sometimes a bit obscure. Do I need ODBC or OLE DB? When you need one and Google for it, you often find old ones that either don’t work at all anymore, or are not optimal. This article provides you with a couple of ASP.NET to SQL Server connection string examples, and as a bonus for ASP to MySQL too.

I put this up here, because I often forget which connection string to use when, when a client asks a question or encounters connection problems… Throughout the examples, replace ‘sql.example.com’, ‘mysql.example.com’, ‘db-user’, ‘P4ssword’ and ‘examplecom’ with your own information.

ASP.NET connection string for Microsoft SQL Server, using System.Data.SqlClient Namespace

// asp.net connection string for sql server
string strConnection = "Data Source=sql.example.com;" + 
	"User ID=db-user;" + 
	"Password=P4ssword;" + 
	"Initial Catalog=examplecom;";

SqlConnection objConnection = new SqlConnection(strConn);
objConnection.Open()Code language: C# (cs)

Manually failover all databases in an SQL Server Database Mirroring configuration

Enable TLS/SSL in System.Data.SqlClient – encrypted SQL connection

Ideally all connections should be encrypted (using TLS/SSL), so that data transfers between a SQL Server instance and a client application are secure. To enable the use of TLS/SSL in your System.Data.SqlClient connection to encrypt and secure the connection, add Encrypt=True and TrustServerCertificate=True like:

Encrypt=True; TrustServerCertificate=True;Code language: HTML, XML (xml)

Microsoft’s documentation states:

Beginning in .NET Framework 4.5, when TrustServerCertificate is false and Encrypt is true, the server name (or IP address) in a SQL Server SSL certificate must exactly match the server name (or IP address) specified in the connection string. Otherwise, the connection attempt will fail.

SqlConnection.ConnectionString Property

ASP.NET Connector/NET to MySQL

A fully-managed ADO.NET driver for MySQL. Also see my MySql.Data.MySqlClient test script for reference and more information.

string strConn = "server=mysql.example.com;" +
	"user=db-user;database=examplecom;" + 
	"port=3306;password=P4ssword;
	SslMode=REQUIRED;";
	
MySqlConnection conn = new MySqlConnection(strConn);Code language: C# (cs)

ASP.NET Connector/ODBC to MySQL

Connector/ODBC is a standardized database driver for Windows, Linux, Mac OS X, and Unix platforms. Also see my MySQL Connector/ODBC test script for reference and more information.

string strConn = "DRIVER={MySQL ODBC 8.0 Unicode Driver};" +
	"Provider=MSDASQL;" +
	"SERVER=mysql.example.com;" +
	"DATABASE=examplecom;" +
	"UID=db-user;" +
	"PASSWORD=P4ssword;" +
	"SslMode=REQUIRED;";

OdbcConnection MyConnection = new OdbcConnection(strConn);
MyConnection.Open();Code language: C# (cs)

Classic ASP connection to SQL Server using ODBC

Dim strConn
' Microsoft SQL Server ODBC Driver
Set strConn = Server.CreateObject("ADODB.Connection")
Connstr = Connstr & "DRIVER={ODBC Driver 18 for SQL Server};"
Connstr = Connstr & "DATABASE=examplecom;"
Connstr = Connstr & "SERVER=sql.example.com;"

' open the connection here using: Connstr, "username", "password"
SqlConn.Open Connstr, "db-user", "P4ssword"Code language: C# (cs)

Choose your DRIVER= string carefully. Use “{ODBC Driver 18 for SQL Server}” if ODBC Driver for SQL Server 18 is installed, otherwise use “{ODBC Driver 17 for SQL Server}”, “{SQL Server}”, or ask your hosting company for instructions.

By not adding “db-user” and “P4ssword” to Connstr, you can safely print the connection string for debugging purposes using: Response.Write(Connstr).

SQL connection string with encryption in ASP

Use the connection string properties Encrypt and trustServerCertificate (if needed) to allow applications to use Transport Layer Security (TLS) encryption. For example:

Set strConn = Server.CreateObject("ADODB.Connection")
Connstr = Connstr & "DRIVER={ODBC Driver 18 for SQL Server};"
Connstr = Connstr & "DATABASE=examplecom;"
Connstr = Connstr & "SERVER=sql.example.com;"
Connstr = Connstr & "Encrypt=Yes;"
Connstr = Connstr & TrustServerCertificate=Yes;"Code language: VBScript (vbscript)

You may find more information in Microsofts article Using Encryption Without Validation.

Block brute force attacks on SQL Server, block IP addresses in Windows Firewall using PowerShell

Microsoft OLE DB Provider voor SQL Server (MSOLEDBSQL)

Dim strConn
Set strConn = Server.CreateObject("ADODB.Connection")
Connstr = "Provider=MSOLEDBSQL;"
Connstr = Connstr & "DATABASE=examplecom;"
Connstr = Connstr & "SERVER=sql.example.com;"

' open the connection here using: Connstr, "db-user", "P4ssword"
SqlConn.Open Connstr, "db-user", "P4ssword"Code language: VBScript (vbscript)

Deprecated: Microsoft OLE DB Provider for SQL Server (SQLOLEDB)

Only for archival purposes. Do not use in production!

Dim oldSqlConn
Set oldSqlConn = Server.CreateObject("ADODB.Connection")
Connstr = "Provider=SQLOLEDB;"
Connstr = Connstr & "DATABASE=examplecom;"
Connstr = Connstr & "SERVER=sql.example.com;"

' open the connection here using: Connstr, "username", "password"
oldSqlConn.Open Connstr, "db-user", "P4ssword"Code language: VBScript (vbscript)

ASP connection string to MySQL databases using Connector/ODBC

Dim strConn
Set sCstrConnonn = Server.CreateObject("ADODB.Connection")
strConn.Open "Provider=MSDASQL;" & _
"DRIVER={MySQL ODBC 8.0 Unicode Driver};" &_
	"Server=mysql.example.com;" & _
	"Database=examplecom;" & _
	"UID=db-user;" & _
	"PWD=P4ssword;" &_
	"SSLMODE=REQUIRED;"Code language: VBScript (vbscript)

UTF-8 with MySQL and classic ASP

Internally, VBScript runs with UCS2 character encoding, not UTF-8. UCS2 stands for 2-byte Universal Character Set and is a character encoding standard in which characters are represented by a fixed-length 16 bits (2 bytes).

If you want to fully support UTF-8 in ASP, for example with MySQL utf8mb4, then you have to define UCS2 as your connection’s charset:

charset=ucs2;

This makes the ASP connection string for UTF-8 support:

strConn.Open "Provider=MSDASQL;" & _
"DRIVER={MySQL ODBC 8.0 Unicode Driver};" &_
	"Server=mysql.example.com;" & _
	"Database=examplecom;" & _
	"UID=db-user;" & _
	"PWD=P4ssword;" &_
	"SSLMODE=REQUIRED;" &_
	"charset=ucs2;"Code language: VBScript (vbscript)

To save UTF-8 encoded values (é, ë, á, ö, etc) in your MySQL database table, you must set ASP’s codepage to 65001 too:

<%@language="VBScript" codepage="65001"%>Code language: HTML, XML (xml)