Connect to a database from Windows

25 Jan 2022 🔖 databases sql beginner minimum viable build
💬 EN

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

Or perhaps:

C:\oracle\(version_number_here)\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.

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

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.

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.

Hsere’s a great way to test whether your computer is allowed to make internet connections to the server hosting the RDBMS, at a really fundamental level that has nothing to do with databases at all:

1. Using the Windows start menu, search for and open up a program called Windows PowerShell.
2. At the command prompt in the window that opens, run this command:

    Test-NetConnection -ComputerName "the-server-domain-name-or-ip-address" -Port "1234"
   

   • Note that you’re going to need to replace the-server-domain-name-or-ip-address with a domain name or IP address for the server, of course.
   • Note that you’re going to need to replace 1234 with the actual port number that the database server would be expecting you to connect to it over. Port 1521 is the most commonly used port number when connecting to a database server that runs an Oracle database.
3. If it displays the following output, ending in TcpTestSucceeded : True, then the your computer is able to talk to the database server over the port you specified at a basic networking level:

    ComputerName     : (the server domain name or IP address, depending which one you typed)
    RemoteAddress    : (the server IP address)
    RemotePort       : (the port number you specified)
    InterfaceAlias   : (something about ethernet or wi-fi or something)
    SourceAddress    : (your computer's IP address)
    TcpTestSucceeded : True
   

4. If it displays the following output, ending in TcpTestSucceeded : False, then that’s a good hint that you need to ask for your computer to be allowed through whatever firewall is protecting the server from your computer, over the appropriate port.

    WARNING: TCP connect to ((an IP address here) : (a port number here)) failed
    WARNING: Ping to (an IP address here) failed with status: TimedOut
    ComputerName : (the server domain name or IP address, depending which one you typed)
    RemoteAddress : (the server IP address)
    RemotePort       : (the port number you specified)
    InterfaceAlias   : (something about ethernet or wi-fi or something)
    SourceAddress    : (your computer's IP address)
    PingSucceeded : False
    PingReplyDetails (RTT) : 0 ms
    TcpTestSucceeded : False
   

5. If you specified the server’s home on the internet by domain name rather than by IP address, and if it displays the following output, ending in PingSucceeded : False, then perhaps your computer can connect to the server, but for some reason or another can’t use DNS to look up its IP address by domain name. Try the Test-NetConnection command again, but use the server’s IP address instead of its domain name (you should be able to get it from the server’s DBA and/or system administrator).

    WARNING: TCP connect to ((an IP address here) : (a port number here)) failed
    WARNING: Ping to (an IP address here) failed with status: TimedOut
    ComputerName : (the server domain name or IP address, depending which one you typed)
    RemoteAddress : 
    InterfaceAlias : 
    SourceAddress : 
    PingSucceeded : False
   

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:

8: Command-line software

If you’re a Windows server sysadmin who’s trying to figure out why some specialized application you’re running won’t connect to a given database, you might appreciate a simple piece of software for connecting to Oracle databases that can run in a Windows cmd or PowerShell prompt.

Two that are very popular are:

• Oracle SQL*PLUS
• Oracle SQLcl

Here’s an example of using sqlplus to initiate a connection to a database:

sqlplus 'username_inside_the_database@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=database_server_domain_name)(Port=1521)))(CONNECT_DATA=(SERVICE_NAME=database_name_inside_the_database)))'

(Be sure to replace username_inside_the_database, database_server_domain_name, port 1521, and database_name_inside_the_database with appropriate values.)

Hopefully, you’ll be immediately presented with a prompt to Enter password. Enter the database user’s password and press enter.

• If you log in successfully, you should get a prompt that starts with “SQL>”.
• If you mistype the username or the password, you will probably receive an error: ORA-01017: invalid username/password; logon denied.

Once you’ve logged in successfully, you can execute the command SELECT 0 FROM DUAL; as described above and make sure you see appropriate output, then execute the command exit; to log SQL*PLUS of the database and return to the Windows computer’s standard command prompt.

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:

• SQL Server Management Studio, a.k.a. “SSMS” (free, general-purpose database query and maintenance tool)
• TOAD for SQL Server (paid; sadly, the freeware download link seems to have disappeared off the internet in mid-2021 – too bad, it was a nice little piece of software that was less cluttered than SSMS)

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.

Hsere’s a great way to test whether your computer is allowed to make internet connections to the server hosting the RDBMS, at a really fundamental level that has nothing to do with databases at all:

1. Using the Windows start menu, search for and open up a program called Windows PowerShell.
2. At the command prompt in the window that opens, run this command:

    Test-NetConnection -ComputerName "the-server-domain-name-or-ip-address" -Port "1234"
   

   • Note that you’re going to need to replace the-server-domain-name-or-ip-address with a domain name or IP address for the server, of course.
   • Note that you’re going to need to replace 1234 with the actual port number that the database server would be expecting you to connect to it over. Port 1433 is a commonly used port number when connecting to a database server that runs a SQL Server database.
3. If it displays the following output, ending in TcpTestSucceeded : True, then the your computer is able to talk to the database server over the port you specified at a basic networking level:

    ComputerName     : (the server domain name or IP address, depending which one you typed)
    RemoteAddress    : (the server IP address)
    RemotePort       : (the port number you specified)
    InterfaceAlias   : (something about ethernet or wi-fi or something)
    SourceAddress    : (your computer's IP address)
    TcpTestSucceeded : True
   

4. If it displays the following output, ending in TcpTestSucceeded : False, then that’s a good hint that you need to ask for your computer to be allowed through whatever firewall is protecting the server from your computer, over the appropriate port.

    WARNING: TCP connect to ((an IP address here) : (a port number here)) failed
    WARNING: Ping to (an IP address here) failed with status: TimedOut
    ComputerName : (the server domain name or IP address, depending which one you typed)
    RemoteAddress : (the server IP address)
    RemotePort       : (the port number you specified)
    InterfaceAlias   : (something about ethernet or wi-fi or something)
    SourceAddress    : (your computer's IP address)
    PingSucceeded : False
    PingReplyDetails (RTT) : 0 ms
    TcpTestSucceeded : False
   

5. If you specified the server’s home on the internet by domain name rather than by IP address, and if it displays the following output, ending in PingSucceeded : False, then perhaps your computer can connect to the server, but for some reason or another can’t use DNS to look up its IP address by domain name. Try the Test-NetConnection command again, but use the server’s IP address instead of its domain name (you should be able to get it from the server’s DBA and/or system administrator).

    WARNING: TCP connect to ((an IP address here) : (a port number here)) failed
    WARNING: Ping to (an IP address here) failed with status: TimedOut
    ComputerName : (the server domain name or IP address, depending which one you typed)
    RemoteAddress : 
    InterfaceAlias : 
    SourceAddress : 
    PingSucceeded : False
   

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:

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