Sysadmins of the North
Don't forget to share this post!

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

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 to SQL Server using ODBC connection string

Dim strConn
' Microsoft SQL Server ODBC Driver
Set strConn = Server.CreateObject("ADODB.Connection")
Connstr = Connstr & "DRIVER={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"

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

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"

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 connection string to MySQL 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;"

buy me a coffee
Buy Me A Coffee

About the Author Jan Reilink

My name is Jan. I am not a hacker, coder, developer, programmer or guru. I am merely a system administrator, doing my daily thing at Vevida in the Netherlands. With over 15 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, WordPress, websites & optimization. Want to support me and donate? Use this link: https://paypal.me/jreilink.

follow me on:

Leave a Comment:

1 comment
Add Your Reply
Skip to content
ehs izV