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.

Introduction

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.

A couple of ASP.NET to SQL Server connectionstring examples, and as a bonus for ASP to MySQL too.

Security Best Practices

Never hardcode passwords in your production source code. While these examples show the full string for syntax reference, in production you should use Environment Variables, Azure Key Vault, or User Secrets (for development) to keep credentials out of your web.config or appsettings.json files.

Microsoft SQL Server Connection Strings

.NET appsettings.json

In .NET you use the appsettings.json file to define your connectionstring. Modern .NET (Core) uses Microsoft.Data.SqlClient:

"ConnectionStrings": {
  "DefaultConnection": "Server=sql.example.com;Database=examplecom;User Id=db-user;Password=P4ssword;TrustServerCertificate=True;Encrypt=True;"
}

Encrypt=True is the default, often requiring TrustServerCertificate=True if you aren’t using a CA-signed cert on the SQL box.

Legacy ASP.NET (web.config)

The System.Data.SqlClient namespace is the .NET Data Provider for SQL Server.

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

The System.Data.SqlClient APIs are deprecated. For new development, use the Microsoft.Data.SqlClient APIs.

Classic ASP recommended MSOLEDBSQL and alternatives (ODBC Driver 18/17)

A typical connectionstring to connect ASP to SQL Server uses OLE DB. MSOLEDBSQL (Microsoft OLE DB Driver for SQL Server) is the current recommendation for OLE DB, replacing the legacy SQLOLEDB (which is deprecated) and SQLNCLI (which is also deprecated). The Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) is the only driver currently receiving security updates. It supports modern features like Multi-Subnet Failover and TLS 1.2+.

Connstr = "Provider=MSOLEDBSQL;Server=sql.example.com;Database=examplecom;UID=db-user;PWD=P4ssword;"

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"

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

An alternative to MSOLEDBSQL is ODBC, use this if the OLE DB driver isn’t installed.

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.

Migrate SQLOLEDB to MSOLEDBSQL

If you are tasked with modernizing a Classic ASP application to support TLS 1.2 or 1.3, you can often fix connection failures simply by swapping the Provider name. No logic changes are required.

Change this:

ConnStr = "Provider=SQLOLEDB;Data Source=sql.example.com;..."

To this:

ConnStr = "Provider=MSOLEDBSQL;Data Source=sql.example.com;..."

Note: This requires the Microsoft OLE DB Driver for SQL Server to be installed on the web server. If it’s missing, the error will be “Provider cannot be found. It may not be properly installed.”

Deprecated providers: avoid SQLOLEDB and SQLNCLI

Only for archival purposes. Do not use in production!

Drivers like SQLOLEDB (the original provider) and SQLNCLI (Native Client) are deprecated and do not support TLS 1.2 or higher by default. If your connection is failing with ‘SSL Provider: The certificate chain was issued by an authority that is not trusted’, it is likely because you are using these outdated providers.”

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"

MySQL / MariaDB Connection Strings

Connector/NET

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

Connector/ODBC

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

Connector/ODBC and Classic ASP

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

Fixing USC2 / UTF-8 / Emoji support in Classic ASP & MySQL

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).

utf8mb4 (required for emojis) often fails in Classic ASP’s ADO unless the connection string is forced to ucs2 and the ASP page is set to codepage 65001. 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"%>

Advanced Options: Mirroring, Encryption, and Failover

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. Always. 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;

Encrypt connection to SQL Server in Classic 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.

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

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.

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.

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

Verification

Here is a small PowerShell snippet to test a connection string without opening Visual Studio:

# Quick test for SQL Server connection
$connString = "Server=sql.example.com;Database=examplecom;User Id=db-user;Password=P4ssword;Encrypt=True;TrustServerCertificate=True;"
$connection = New-Object System.Data.SqlClient.SqlConnection($connString)
try {
    $connection.Open(); "Success!"
} catch {
    $_.Exception.Message
}
finally {
    $connection.Close()
}

Conclusion

It is always handy to have multiple connectionstring examples ready to use, whether it’s for ASP.NET or classic ASP, SQL Server or MySQL (MariaDB). This makes developing web applications easier.

Use highest available software versions and always encrypt connections. Stop using deprecated SQLOLEDB. Get modern connection strings for SQL Server and MySQL in ASP.NET and Classic ASP, including TLS encryption and failover tips.