ODBC Connection String: Connecting to Your Database

Open database connectivity (ODBC) is an API that lets you connect to any database engine regardless of your operating system or the application from which you want to connect. ODBC connections use a driver that “sits” between the application and the database system. It’s this driver that makes your database connection independent of your application and database system. You still must install the proper driver for your database server, but you don’t need to worry about writing any customized code that connects to your database from your application. ODBC connections handle the connection, but each connection string is different depending on your database engine.

Learn how to work with MySQL for beginners and connect to a database for the first time

The ODBC Connection String

The ODBC connection string has four major parts: the server address, database name, user name and password. The server address is the name of the server that holds your database. You can use an IP address or the server name. The server name can just be a Microsoft computer name if you are on a private network. If you need to connect to a database server on the Internet or on another network, you can also use a fully qualified name such as “server.domainname.com” in the string.

Depending on your database engine, a default port is assumed when you don’t specify it. For instance, 1433 is the default port for SQL Server. You specify the port by adding it to the end of your server name. Some ODBC connection strings for engines such as MySQL have a separate “port” setting you can use in the string. The connection will assume the default port unless you specify otherwise.

The next part of the connection string is the database name. You can run dozens of databases on one server, so the connection driver needs to know what database to connect to. You specify the database name in the “database” property in the connection string. For Microsoft SQL Server, the property is named “initial catalog.” Some servers run instances with the database name, so the database name is combined with the instance name such as “instance\database name.”

Understanding SQL database servers as a beginner

The last two properties are the user name and password needed to connect to the database. Some database server software such as Microsoft SQL Server will let you connect to the database with a trusted connection. This type of setting is only used in environments where you know the database is protected and you don’t need any strict security on your database.

In a Windows environment, you can also use Windows network user names and passwords to allow users to connect. If you use this type of environment, you need a Windows domain and account name to log in to the server. Most administrators prefer to use separate database user names and passwords. This is an increased security method, because if a hacker is able to get access to a user’s user name and password, they still need a secondary database user name and password to access the database.

Get database training for MySQL with Udemy.com courses

Sample Connection Strings

ODBC driver software does its best to make it easy for you to connect to the database from your application. However, each connection string is slightly different from other connection strings. The way you format your string is dependent on your database software. You can find different ODBC connection strings listed on the Internet and cataloged to make it easier for you.

The following is an example of a Microsoft SQL Server connection string:

Server = servername; Database = dbname; User Id = usrname; Password = pwd;

The above connection string is self explanatory. The server, database name, user name and password are all included in the string. You can eliminate the user name and password in a trusted environment using the following connection string:

Server = servername; Database = dbname; Trusted_Connection = True;

As you can see, no user name and password is included in the above connection string. For obvious reasons, this type of connection is only used when you don’t need much security on your network including your database server. Your database server must also allow the user to log in this way for it to be successful.

The following is an ODBC connection string for a MySQL server:

Server = servername; Database = dbname; Uid = username; Pwd = password;

Notice the slightly different syntax between the above MySQL server connection string and the Microsoft SQL Server connection string. The basic information is still the same, but the format for some of the string properties is different. These minor differences are based on the driver and database engine.

The following ODBC connection string is for MySQL and uses another port than the default:

Server = servername; Port=1234; Database = dbname; Uid = username; Pwd = password;

You can also enable encryption with your ODBC connection strings. The following string enables encryption for a MySQL database connection:

Server = servername; Database = dbname; Uid = username; Pwd = password; SslMode = Required;

In the above connection string, SSL is required. If you change the “Required” property setting to “preferred,” you give the application user the option to use SSL or not.

These are a few ODBC connection string examples, but there are dozens more that correspond with different database engines. PostgreSQL, Oracle, IBM and Access also use ODBC connections. You can also use these connection strings for files such as CSV and Excel spreadsheet files. You only need to copy and paste the connection string into your application once, and you can reuse it over and over each time you need to query your database. This is what makes the ODBC connection string standard so convenient for application developers.

Take a Microsoft SQL Server training class and get to know the basics