Connecting Your Application to InterSystems IRIS Cloud SQL

Connect from Python using DB-API

Before using these instructions, you should make sure that:

• Your development system has the Python IDEOpens in a new tab of your choice installed and has network access to your deployment.

• You have downloadedOpens in a new tab the InterSystems DB-API driver, intersystems_irispython-version-py3-none-any.whl, to your development system.

• You have gathered the connection informationOpens in a new tab for the Cloud Document deployment you want to connect to. In particular, you have identified the hostname, port, namespace, username, and password for your deployment found in the Making External Connections table on the Deployment Overview page in the InterSystems Cloud Services Portal.

• You have obtained or downloaded the self-signed X.509 certificate provided by the Cloud Document deployment (found on the Deployment Overview page in the InterSystems Cloud Services Portal) and have placed it in a secure location.

You can copy the completed connection code from the listing that follows the instructions, including the steps required to add TLS encryption to the connection.

To code a DB-API connection to InterSystems IRIS, follow these steps:

1. Install the DB-API driver:

   C:\> pip install intersystems_irispython-version-py3-none-any.whl
   

   

2. Import the iris and ssl modules.

   Note:

   When using embedded Python, the iris module is typically imported using iris.cls, iris.gref, or iris.sql. This differs from the import iris statement for the DB-API driver, as shown in this code.

3. Use the connection information for your Cloud SQL deployment to define the connection string, in the form host-identifier:superserver-port/namespace. Specify the username and password.

   Before creating the connection, specify the TLS context and verify the existence of the required certificate.

   Finally, create the connection by calling iris.connect. Close the connection when you are finished with it.

   You can copy the template code used here from the listing below.

   import iris
   import ssl
    
   def main():
       # enter your connection string, username, and password
       connection_string = "host-identifier:superserver-port/namespace"
       username = "username"
       password = "password"
    
       # specify the TLS context
       context = ssl.SSLContext(ssl.PROTOCOL_TLS_CLIENT)
       context.verify_mode=ssl.CERT_REQUIRED
       context.check_hostname = False
       context.load_verify_locations("path-to-cert/cert-file.pem")
   
       # create the connection
       connection = iris.connect(connection_string, username, password, sslcontext=context)
   
       # your application code here
    
       # close the connection
       connection.close()
    
   if __name__ == "__main__":
       main()
   

   

For more information about using Python and DB-API with InterSystems IRIS, see Python DB-API SupportOpens in a new tab.

Connect from Java using JDBC

Before using these instructions, you should make sure that:

• Your development system has version 1.8 of the JDK and the Java IDEOpens in a new tab of your choice installed and has network access to your InterSystems IRIS target.

• You have downloadedOpens in a new tab the InterSystems JDBC driver, intersystems-jdbc-version.jar, to your development system.

• You have gathered the connection informationOpens in a new tab for the Cloud Document deployment you want to connect to. In particular, you have identified the hostname, port, namespace, username, and password for your deployment found in the Making External Connections table on the Deployment Overview page in the InterSystems Cloud Service Portal.

• You have obtained or downloaded the self-signed X.509 certificate provided by the Cloud Document deployment (found on the Deployment Overview page in the InterSystems Cloud Services Portal) and have placed it in a secure location.

You can copy the completed connection code from the listing that follows the instructions, including the steps required to add TLS encryption to the connection.

To code a JDBC connection to InterSystems IRIS, follow these steps:

1. Add the InterSystems Cloud Document driver, intersystems-document-version.jar, as a dependency to your project.

2. On the operating system command line, issue the following command to add the X.509 certificate to your keystore, supplying a keystore password and confirming Trust this certificate? [no]: yes as requested:

   keytool -importcert -file path-to-cert/cert-file.pem 
     -keystore keystore.jks
   

   

   Then, in the directory containing the Java file in which you are coding the connection, create a configuration file named SSLConfig.properties and includes these properties:

   trustStore=path-to-keystore/keystore.jks
   trustStorePassword=keystore-password
   

   

3. Create a main method and import the com.intersystems.jdbc.* libraries. Use the connection information for your Cloud SQL deployment to define the connection string, in the form host-identifier:superserver-port/namespace and define the credentials.

   Create an IRISDataSource object and set the connection parameters accordingly. Be sure to also set the connection security level to 10.

   Finally, call getConnection() to make the connection. Close the connection with close() when you are finished with it.

   You can copy the template code used here from the listing below.

   import com.intersystems.jdbc*;
   import java.sql.Connection;
   
   
   public class JDBCConnection{
     public static void main (String[] args) throws Exception {
         // define the connection parameters
         String dbUrl = 
           "jdbc:IRIS://host-identifier:superserver-port/namespace"; 
         String user = "username";
         String pass = "password";
   
         // create an IRISDataSource and set the parameters,
         //   including the connection security level
         IRISDataSource ds = new IRISDataSource();
         ds.setURL(dbUrl);
         ds.setUser(user);
         ds.setPassword(pass);
         ds.setConnectionSecurityLevel(10);
   
         // create the connection
         Connection dbconnection = ds.getConnection();
         System.out.println("Connected to InterSystems IRIS via JDBC.");
   
         // use the connection here
   
         // close the connection when you are done with it
         dbconnection.close()
     }
   }
   

   

For more information about using Java and JDBC with InterSystems IRIS, see Using Java with InterSystems SoftwareOpens in a new tab.

For detailed information about connecting with TLS from Java applications, see Configuring Java Clients to Use TLS with InterSystems IRISOpens in a new tab.

Connect from .NET using ADO.NET

Before using these instructions, you should make sure that:

• Your development system has the .NET framework and Visual Studio (or another .NET IDEOpens in a new tab of your choice) installed, and has network access to your InterSystems IRIS target.

• You have downloadedOpens in a new tab the InterSystems ADO.NET client assembly, InterSystems.Data.IRISClient.dll, to your development system.

  If InterSystems IRIS is installed locally or in a container on your development system, you can also find the file in install-dir\dev\dotnet\bin\net5.0 (or install-dir/dev/dotnet/bin/net5.0 on UNIX®/Linux), where install-dir is the InterSystems IRIS installation directory (install-dir in a container is /usr/irissys).

• You have gathered the connection informationOpens in a new tab for the Cloud Document deployment you want to connect to. In particular, you have identified the hostname, port, namespace, username, and password for your deployment found in the Making External Connections table on the Deployment Overview page in the InterSystems Cloud Service Portal.

• You have obtained or downloaded the self-signed X.509 certificate provided by the Cloud Document deployment (found on the Deployment Overview page in the InterSystems Cloud Services Portal) and have placed it in a secure location.

You can copy the completed connection code from the listing that follows the instructions, including the steps required to add TLS encryption to the connection.

To code an ADO.NET connection to InterSystems IRIS, follow these steps:

1. Add the InterSystems ADO.Net client assembly, InterSystems.Data.IRISClient.dll, as a dependency and declare it in the application, then create a namespace with an internal class that has a main method.

   

2. Install the X.509 certificate using the instructions for your platform, below. (Both Windows and UNIX/Linux systems provide several ways to install certificates.)

   • On Windows, open a Command Prompt window and enter the following command to add the certificate to the Trusted Root Certification Authorities\ store under Current User:

     certutil -user -addstore Root path-to-certificate\certificate-file.pem 
     

     

   • On the UNIX or Linux command line, follow these steps:

     1. Enter the following command to install the Dotnet Certificate Tool, dotnet-certificate-tool (see https://github.com/gsoft-inc/dotnet-certificate-toolOpens in a new tab):

        dotnet tool install --global dotnet-certificate-tool
        

        

     2. Use the tool to add the certificate to the Root store under CurrentUser (be sure to include --store-name Root as shown):

        certificate-tool add --cert path-to-certificate\certificate-file.pem 
          --store-name Root
        

        
        Note:

        If the certificate is not in PEM format, use the appropriate flag in place of --cert; if the certificate is password-protected, include the --password flag. For more information, see the README file on GitHub.

3. Use the connection information for the InterSystems IRIS target to define the connection string:

   using System;
   using InterSystems.Data.IRISClient;
    
   namespace ADODemo
   {
      internal class Program
      {
         static void Main(string[] args)
         {
            string connectionString = "Server = localhost; " +
              "Port = 1972; Namespace = User;" +
              "User ID = Admin; Password = -------------; SSL = true";
         }
      }
   }
   

   

4. Finally, pass the connection string as an argument to the IRISADOConnection method and create the connection:

   using System;
   using InterSystems.Data.IRISClient;
    
   namespace ADODemo
   {
      internal class Program
      {
         static void Main(string[] args)
         {
            string connectionString = "Server = localhost; " +
              "Port = 1972; Namespace = User;" +
              "User ID = Admin; Password = -------------; SSL = true";
   
            IRISADOConnection connection = new IRISADOConnection(connectionString);
            connection.Open();
            // when finished, use the line below to close the connection
            // connection.Close();
         }
      }
   }
   

   

You can copy the code used here from the listing below.

using System;
using InterSystems.Data.IRISClient;
 
namespace ADODemo
{
   internal class Program
   {
      static void Main(string[] args)
      {
         string connectionString = "Server = host-identifier; " +
           "Port = superserver-port; Namespace = namespace;" +
           "User ID = username; Password = password; SSL = true";
         IRISADOConnection connection = new IRISADOConnection(connectionString);
         connection.Open();
         // when finished, use the line below to close the connection
         // connection.Close();
      }
   }
}

For more information about using .NET and ADO.NET with InterSystems IRIS, see Using .NET with InterSystems SoftwareOpens in a new tab.

For more detailed information about connecting with TLS from .NET applications, see Configuring .NET clients to Use TLS with InterSystems IRISOpens in a new tab.

Connect from C++ using ODBC

Before using these instructions, you should make sure that:

• Your development system has Visual Studio (or another C++ development environment of your choice) installed, and has network access to your InterSystems IRIS target.

• You have downloadedOpens in a new tab the platform-specific InterSystems ODBC driver to your development system. (If InterSystems IRIS is installed locally on your development system, the driver is already installed and you do not need to download it.)

• You have gathered the connection informationOpens in a new tab for the Cloud Document deployment you want to connect to. In particular, you have identified the hostname, port, namespace, username, and password for your deployment found in the Making External Connections table on the Deployment Overview page in the InterSystems Cloud Service Portal.

• You have obtained or downloaded the self-signed X.509 certificate provided by the Cloud Document deployment (found on the Deployment Overview page in the InterSystems Cloud Services Portal) and have placed it in a secure location.

You can copy the completed connection code from the listing that follows the instructions.

To code an ODBC connection to InterSystems IRIS, follow these steps:

1. If InterSystems IRIS is not installed locally, install the InterSystems ODBC driver as follows:

   • On Windows, execute the downloaded installer, ODBC-version-win_x64.exe.

   • On Linux or MacOS:

     1. Create the directory in which you want to install the driver, for example /usr/irisodbc.

     2. Unpack the downloaded .tar file, ODBC-version-platform.tar.gz, in that directory.

     3. Execute the ODBCinstall script.

2. Choose a location and name for a connection and configuration definitions file and set the environment variable ISC_SSLconfigurations to the full path (including filename) of this file. The default name and location is C:\Program Files (x86)\Common Files\InterSystems\IRIS\SSLDefs.ini on Windows, however you can place the file where you like as long as you set the environment variable. On UNIX/Linux, you must set the environment variable so the driver can locate the file.

3. Create the definitions file with the following contents:

   [ConnectionName]
   Address=host-identifier
   Port=superserver-port
   SSLConfig=TLSConfigName
   
   [TLSConfigName]
   VerifyPeer=1
   VerifyHost=0
   CAfile=path-to-certificate\certificate-file.pem
   TLSMinVersion=minimum-supported-tls-version
   TLSMaxVersion=maximum-supported-tls-version
   

   

   The connection definition includes the host identifier and superserver port for the InterSystems IRIS target that appear in the connection string variable connect_cmd in the code above, as well as the name of the TLS configuration definition. In the TLS configuration definition, VerifyPeer=1 and VerifyHost=0 indicate that in order to establish an encrypted connection, the client will verify the server’s certificate — that is, the self-signed certificate provided by the InterSystems IRIS target, specified in CAfile — but will not verify that the Common Name or subjectAlternativeName fields of the certificate match the host name or IP address as specified in the connection definition. For information about the correct values for TLSMinVersion and TLSMaxVersion, see Which TLS Versions Does My Instance of InterSystems IRIS Support?Opens in a new tab For more detailed information about the definitions file and its contents, see Connecting from a Windows Client Using a Settings FileOpens in a new tab.

   Important:

   Generally speaking, the best practice when using TLS encryption is to require peer verification, so that both server and client must verify the certificate of the other party before establishing an encrypted connection. However, in the limited case presented here, only the client’s certificate is verified.

4. Create a DSN entry defining the InterSystems IRIS target as an ODBC data source. This entry contains the connection information for the target, corresponding to the fields of the connection string in the code — host identifier (Host), superserver port (Port), namespace (Database), username (UID), and password (PWD) . The steps to follow depend on your platform:

   • On Windows:

     1. Open the ODBC Data Source Administrator from the Control Panel by selecting Administrative Tools and then ODBC Data Sources (32 bit or 64 bit, depending on your system).

     2. On the User DSN or System DSN tab, select Add, then on the Create New Data Source panel select InterSystems IRIS ODBC35.

     3. Create a name and description for the source, enter the connection information in the appropriate locations, set Authentication Method to Password, and enter the name of the TLS configuration in the definitions file you created in the previous step as SSL/TLS Server Name.

        
        Important:

        The Password with SSL/TLS setting for Authentication Method is not required when using a definitions file, such as ssldefs.ini, as described in this procedure; it is included for legacy purposes only. If the definitions file is properly configured, the connection should use SSL/TLS automatically with Password as the authentication mechanism. If there are problems with the definitions file, the connection attempt may proceed without using SSL/TLS; if the server allows unencrypted connections, the connection may succeed.

     4. Optionally test the connection information using the Test Connection button, then click OK.

   • On Linux:

     1. Using the file extract-directory/dev/odbc/redist/ssl/irisodbc.ini.template as a template and the connection information, create an ODBC initialization file (typically called odbc.ini) in a location available to the connection code with values as described for the Windows DSN above, as shown here:

        [ODBC Data Sources] 
        IRIS-TLS = IRIS-TLS 
        
        [IRIS-TLS] 
        Driver = /home/guest/iris/bin/libirisodbc35.so 
        Description = demo TLS connection
        Host = host-identifier 
        Port = superserver-port 
        Namespace = namespace 
        UID = username 
        Password = password 
        Protocol = TCP 
        Query Timeout = 1 
        Static Cursors = 0 
        Trace = off 
        TraceFile = logfile.log 
        Service Principal Name = iris/target-domain-name 
        Authentication Method = 2 
        Security Level = 10 
        SSL Server Name = tls-config-in-definitions-file
        

        

     2. Set the environment variable ODBCINI to the full path (including filename) of the ODBC initialization file.

   • On UNIX, follow the instructions in Defining an ODBC Data Source on UNIXOpens in a new tab.

5. Import the required libraries and create a main method:

   #ifdef _WIN32
   #include <windows.h>
   #endif
   #include <sql.h>
   #include <sqlext.h>
   #include <stdio.h>
   #include <tchar.h>
   
   int main()
   {
   }
   

   

6. Initialize variables, and allocate the environment and connection handles:

   #ifdef _WIN32
   #include <windows.h>
   #endif
   #include <sql.h>
   #include <sqlext.h>
   #include <stdio.h>
   #include <tchar.h>
   
   int main()
   {
       RETCODE rc;        /* Return code for ODBC function */
       HENV henv = NULL;  /* Environment handle */
       HDBC hdbc = NULL;  /* Connection handle */
       SQLTCHAR szOutConn[600];
       SQLSMALLINT* cbOutConn = 0;
   
       SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
       SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0);
       SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
   }
   

   

7. Define the connection string, with the DSN that you defined. Call the method that creates the connection, SQLDriverConnect, passing in arguments such as the handle variables and connection string.

   #ifdef _WIN32
   #include <windows.h>
   #endif
   #include <sql.h>
   #include <sqlext.h>
   #include <stdio.h>
   #include <tchar.h>
   
   int main()
   {
       RETCODE rc;        /* Return code for ODBC function */
       HENV henv = NULL;  /* Environment handle */
       HDBC hdbc = NULL;  /* Connection handle */
       SQLTCHAR szOutConn[600];
       SQLSMALLINT* cbOutConn = 0;
   
       SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
       SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0);
       SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
   
       SQLTCHAR connect_cmd[255] = _T("DSN=IRIS-TLS;\0");
       rc = SQLDriverConnect(hdbc, NULL, (SQLCHAR*) connect_cmd, SQL_NTS, szOutConn, 600, cbOutConn, SQL_DRIVER_COMPLETE);
   
       if (rc == SQL_SUCCESS)
       {
          printf("Successfully connected!!\n");
       }
       else
       {
          printf("Failed to connect to IRIS\n");
          exit(1);
       }
    
       SQLDisconnect(hdbc);
       SQLFreeHandle(SQL_HANDLE_DBC, hdbc);    /* Free connection handle */
       SQLFreeHandle(SQL_HANDLE_ENV, henv);    /* Free environment handle */
        
       return 0;
   
   }
   

   

You can copy the code used here from the listing below.

#ifdef _WIN32
#include <windows.h>
#endif
#include <sql.h>
#include <sqlext.h>
#include <stdio.h>
#include <tchar.h>
 
int main()
{
     RETCODE rc;                        /* Return code for ODBC functions */
     HENV henv = NULL;                  /* Environment handle */
     HDBC hdbc = NULL;                 /* Connection handle */
     SQLTCHAR szoutConn[600]
     SQLSMALLINT *cbOutConn = 0;
     
     SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
     SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0);
     SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

     SQLTCHAR connect_cmd[255] = _T("DSN=IRIS-TLS;\0"); 
     rc = SQLDriverConnect(hdbc, NULL, (SQLCHAR*) connect_cmd, SQL_NTS, 
       szOutConn, 600, cbOutConn, SQL_DRIVER_COMPLETE);
 
     if (rc == SQL_SUCCESS)
     {
                 printf("Successfully connected!!\n");
     }
     else
     {
                 printf("Failed to connect to IRIS\n");
                 exit(1);
     }          
 
     SQLDisconnect(hdbc);
     SQLFreeHandle(SQL_HANDLE_DBC, hdbc);    /* Free connection handle */
     SQLFreeHandle(SQL_HANDLE_ENV, henv);    /* Free environment handle */
     
     return 0;
}

For more information about using C++ and ODBC with InterSystems IRIS, see Using the InterSystems ODBC DriverOpens in a new tab.

For more detailed information about connecting with TLS using ODBC, see Connecting from a Windows Client Using a Settings FileOpens in a new tab.