Salesforce, Python, SQL, & other ways to put your data where you need it

Need event music? 🎸

Live and recorded jazz, pop, and meditative music for your virtual conference / Zoom wedding / yoga class / private party with quality sound and a smooth technical experience

Connect to a database from Windows

25 Jan 2022 🔖 databases sql beginner
💬 EN

Table of Contents

Relational database management systems (a.k.a. RDBMSes or DBMSes) generally run on computers in the cloud that behave as “servers.”

These days, most people use their web browser to make their computer behave as a “client” so that they can read or edit a given database. Rather than connecting their computer directly to a database server over the internet, they connect to a web site, and the web server hosting that web site connects to the database server hosting the database.

However, certain specialized tasks still require making a direct internet connection (over a protocol such as ODBC) between your desktop computer and a database’s server. This article takes a tour “under the hood” of how Windows can connect directly to two common brands of database: Oracle and SQL Server.

Oracle Database

1: Oracle Instant Client

Windows desktop software called Oracle Instant Client must be installed onto the computer, which comes with software called Oracle Net Services (formerly “SQL*Net”) built in and is responsible for connecting to databases over the internet using the ODBC protocol.

Multiple copies and multiple versions of this software can coexist on the same computer, and sometimes that is desired, but it has to be done carefully if that is what is actually intended.

For example, you may need to match the 32-bit vs. 64-bit version of Oracle Instant Client to the RDBMS server the database is hosted in.

Last I checked, the Oracle Instant Client seemed to install by default with a folder pattern of:

C:\oracle\product\(version_number_here)\client_1

2: tnsnames.ora file

You must save at least 1 file on your computer that can serve as a configuration file for Oracle Instant Client, specifying the Oracle databases you’d like to connect to. Each entry in this file will specify:

  • A nickname you’d like to give the database
  • An address on the internet for the database’s server
  • The name the database goes by on its server
  • etc.

This file is typically called tnsnames.ora.

Fresh installations of Oracle Instant Client typically build out a skeleton tnsnames.ora file in the directory where Oracle Instant Client was installed – for example:

C:\oracle\product\(version_number_here)\client_1\network\ADMIN\tnsnames.ora

There can be more than one tnsnames.ora-style file on a computer, particularly if there are deliberately several parallel installations of Oracle Instant Client.

However, it can be tricky to ensure that all software installed on your computer knows which copy of OIC is which. Be careful, and good luck, if you have a complex multiple-OIC setup to deal with.

If you are working with other people on the same databases, you might want to ask them for a copy of their tnsnames.ora file and carefully weave its contents in with your own.

It’s a good idea to keep a backup of the latest contents of any files like this somewhere far away from your installation of Oracle Instant Client.

Fresh installations, upgrades, and troubleshooting can end up wiping out your existing tnsnames.ora file. It’s always good to be able to rebuild it quickly.

3: Path environment variable

Your computer must have the ...\bin\ folder for any installed instances of Oracle Instant Client listed in the system-wide (not user-specific) Windows environment variable called PATH.

You’ll need Windows admin rights to make this happen, if you have to do it manually.

(You might not need to do anything manually. I believe that the Oracle Instant Client installer should try to take care of setting this up.)

Getting PATH just right can be tricky when you have parallel installations of Oracle Instant Client.

Sometimes, an Oracle Instant Client installer gets bundled and executed as part of the installation of another enterprise desktop computing product, which can be a frustrating source of having multiple installations that you didn’t even necessarily want.

Check what’s in PATH – and what order the folders in it are listed in – if things go haywire on you.

For example, the ...\bin\ folder for any copy of Oracle Instant Client that you deliberately installed should probably precede that of any copy that you didn’t mean to install (and you might want to take the ...\bin\ folder of copies you didn’t mean to install out of the PATH altogether if downgrading its position doesn’t help).

Once you have your computer connecting to an Oracle database, it’s a good idea to take note of anything Oracle-related in your PATH variable.

Fresh installations, upgrades, and troubleshooting can end up butchering your existing PATH configuration. It’s always good to be able to rebuild it quickly.

4: GUI software

For human convenience, the computer must have one or more pieces of point-and-click (“GUI“) software installed to make it easier to connect to an Oracle database.

A 32- vs. 64-bit installation choice may need to match the RDBMS version and the Oracle Instant Client version.

Common examples of such software are:

  • Oracle SQL Developer (free, general-purpose database query and maintenance tool)
  • TOra (free, general-purpose database query and maintenance tool)
  • TOAD for Oracle (paid, like SQL Developer but with more features)

But there are other specialized tools in existence that make direct connections to databases from a desktop computer, such as IBM Cognos Framework Manager, which helps ultra-power-users design the data models that ordinary IBM Cognos Analytics report authors build through a web-based user interface.

Some software will let you specify which version of Oracle Instant Client you’d like it to use, and/or which tnsnames.ora-style file you’d like it to use.

Playing with these settings is a great way to fix obscure problems … and to cause them.

This might go without saying, but to connect to a database, of course you’re also going to want to learn your way around GUI software’s user interface well enough to specify which database you’d like to connect to (e.g. from the ones you listed in tnsnames.ora).

5: Networking restrictions

If the server hosting the RDBMS only allows incoming internet connections from certain computers, you need to make sure your computer is one of them.

For example, if it only allows certain IP addresses, you’ll need a stable IP address and to be added to the server’s allow-list.

6: Database credentials

You won’t get far if you don’t have a username and password for the database.

7: Database permissions

You won’t get far if your database user isn’t allowed to do anything in the database.

Nevertheless, just to make sure things are alive, even a user without much in the way of permissions should be able to execute this SQL query:

SELECT 0
FROM DUAL;

The data output should be a 1-row, 1-column table where the header and value are 0:

0
0

Microsoft SQL Server

Since Microsoft wrote both Windows and SQL Server, Windows comes with the ability to connect to SQL Server databases over the internet via the ODBC protocol built in.

There is no need to install anything equivalent to Oracle Instant Client when it comes to connecting to SQL Server databases.

1: Server detail wizards

For each database you’d like to connect to, you must use a wizard-style tool in Windows to tell it:

  • A nickname you’d like to give the database
  • An address on the internet for the database’s server
  • etc.

There is no single file like “tnsnames.ora” – instead, Windows offers a wizard-style interface for specifying each database one at a time,

Instructions can be found as part of Microsoft’s larger “Exporting from ODBC to a Flat File Using a Wizard” tutorial in Lesson 1: Creating a SQL Server ODBC Data Source and as part of Microsoft’s larger “SQL Server Client Configuration How-to Topics” documentation in How to: Open the ODBC Data Source Administrator.

Knowing exactly what to type, and into which parts of the wizard, can be a bit tricky, but hopefully there’s a database administrator (“DBA”) for the database you’d like to connect you who can help walk you through it.

2: GUI software

For human convenience, the computer must have one or more pieces of point-and-click (“GUI“) software installed to make it easier to connect to an Oracle database.

A 32- vs. 64-bit installation choice may need to match the RDBMS version.

Common examples of such software are:

This might go without saying, but to connect to a database, of course you’re also going to want to learn your way around GUI software’s user interface well enough to specify which database you’d like to connect to (e.g. from the ones you set up in the Windows ODBC Data Source Administrator).

3: Networking restrictions

If the server hosting the RDBMS only allows incoming internet connections from certain computers, you need to make sure your computer is one of them.

For example, if it only allows certain IP addresses, you’ll need a stable IP address and to be added to the server’s allow-list.

4: Database credentials

You won’t get far if you don’t have a username and password for the database.

5: Database permissions

You won’t get far if your database user isn’t allowed to do anything in the database.

Nevertheless, just to make sure things are alive, even a user without much in the way of permissions should be able to execute this SQL query:

SELECT 0;

The data output should be a 1-row, 1-column table where the value in its only cell is 0:

[No name 1]
0

Miscellaneous

Side note: You might have colleagues who say “SQL database” when they mean “SQL Server database” because Microsoft seems to have encouraged such slang for branding many years ago or something.

Therefore, you should understand that you’ll often hear people say “Oracle database,” “SQL database,” “MySQL database,” “Postgres database,” etc. as a sort of compare-and-contrast between brands when what they really mean by “SQL database” is “SQL Server database.”

Since SQL is a programming language that most traditional databases understand, not a brand of relational database management system, I personally think saying “SQL database” leads to confusion about whether you mean “generic relational database” or “database running on Microsoft SQL Server.”

Personally, I always say “SQL Server database” or even “Microsoft SQL Server database,” even though it’s a mouthful, just to be clear.

(I suppose I’m guilty of calling “Oracle Database” simply “Oracle,” though, even though the latter is the name of a company and the former is the name of an RDBMS product they sell.)

--- ---