ASP and ASP.NET connectionstring examples for Microsoft SQL Server and MySQL

A connectionstring 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 connectionstring examples, and as a bonus for ASP to MySQL too.
Published on Wednesday, 17 October 2018
aspnet classic-asp connector-net connector-odbc mysql sql-server

In this article I show you various connectionstrings to connect to an SQL Server or MySQL (MariaDB) database from either ASP.NET or classic ASP. Yes, really, classic ASP :) I recommend you always use SSL/TLS encryption for your connection where possible.

I put this up here, because I often forget which connectionstring 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 connectionstring for Microsoft SQL Server, using System.Data.SqlClient Namespace

// asp.net connectionstring 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()

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;

Microsoft's documentation states:

Beginning in .NET Framework 4.5, when TrustServerCertificate is set to false and Encrypt set to 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 connectionstring. 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);

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();

Classic ASP connectionstring to SQL Server using ODBC

A typical connectionstring to connect ASP to SQL Server is the following. It uses 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"

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}", "", 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 Server connectionstring with encryption in ASP

Use the SQL connectionstring 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;"

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"

SQL Mirroring Failover Partner in the Connection String

Are you using SQL Server Mirroring? Be sure to set the failover partner in the connection string.

Server=sql-principal.example.com;
  Failover Partner=sql-partner.example.org;
  Database=examplecom;
  User Id=examplecom;
  Password=P4ssw0rd;"

If you supply the name of a failover partner server in the connection string, the client will transparently attempt a connection with the failover partner if the principal database is unavailable when the client application first connects.

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"

ASP connectionstring 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;"

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 connectionstring 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;"

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"%>

Conclusion

It is always handy to have multiple connectionstring examples ready to use, whether it's for ASP.NET or classic ASP. This makes developing web applications easier. Use highest available software versions and always encrypt connections.