Posts

Connection to SQL Server fails when the database server is configured to use TLS 1.2

Problem:

When we try to connect to an SQL database with TLS1.2 on and TLS1.0 off, we get this error:

The test connection operation failed for data source x on server x at node <node> with the following exception:

java.sql.SQLException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption.

Error: “SQL Server did not return a response. The connection has been closed.

ClientConnectionId:x”.

DSRA0010E: SQL State = 08S01, Error Code = 0. View JVM logs for further details.

 

Resolution:

TLS 1.2 and SQL Server require a fix from Microsoft to work.

 

To get the Content Store connection working:

– Ensure that the unrestricted jre policy files are added and in cognos configuration you add the SHA256 ciphersuites.

– Use the latest SQL Server jdbc driver (at least 7.0) for the java version that Cognos uses. You can find the latest SQL Server JDBC driver here: https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15

– Edit install_location\bin64\startwlp.bat (Windows) to add the following lines after the line set JVM_ARGS=-Xmx4096m -XX:MaxNewSize=2048m -XX:NewSize=1024m %DEBUG_OPTS%:

set JVM_ARGS=”-Dcom.ibm.jsse2.overrideDefaultTLS=true” %JVM_ARGS%

NOTE: The startwlp.bat file no longer exists in 11.1.5 and higher. 

– Edit install_location\bin64\bootstrap_wlp_os_version.xml to add the following lines after the line <param condName=”${java_vendor}” condValue=”IBM”>-Xscmaxaot4m</param>:

<param>”-Dcom.ibm.jsse2.overrideDefaultTLS=true”</param>

– Edit install_location\bin64\cogconfig.bat (Windows) to add the following lines after the line set J_OPTS=%DD_OPTS% %J_OPTS%:

set J_OPTS=”-Dcom.ibm.jsse2.overrideDefaultTLS=true” %J_OPTS%

– If SQL Server has ‘Force Encryption’ set to ‘Yes’ (meaning that it is using an SSL certificate), you will need to import the SQL Server certificate into the Java cacerts keystore for your JRE. For example (using the Cognos JRE):

  1. Obtain the root Certificate Authority certificate that issued your SQL Server’s certificate (or the self-signed server certificate if it was not issued by a Certificate Authority), and copy to the computer where Cognos Analytics is installed. For example, copy the file sqlcert.cer to the root directory, c:\sqlcert.cer
  2. Type cd C:\Program Files\ibm\cognos\analytics\ibm-jre\jre\lib\security
  3. Type , for example, C:\Progra~1\ibm\cognos\analytics\ibm-jre\jre\bin\keytool -import -trustcacerts -file “c:\sqlcert.cer” -keystore cacerts -alias SQLCert

– Start Cognos Configuration using cogconfig.bat you modified in the previous step. Important: You must start IBM Cognos Configuration using cogconfig.bat

To get the SQL Server Native Client Connection:

Download a version of the Native Client that supports TLS 1.2 as per Microsoft’s KB Article: https://support.microsoft.com/en-ca/help/3135244/tls-1-2-support-for-microsoft-sql-server

To get the JDBC data source connection working:

– Go to Cognos Administration -> Configuration tab -> Dispatchers and Services
– Click on the dispatcher server to drill down to the services
– Beside the QueryService, click the Set Properties button
– Go to the Settings tab
– Add the following the Additional JVM Arguments for the QueryService setting

-Dcom.ibm.jsse2.overrideDefaultTLS=true

– Click OK
– Click on the Status tab
– Select System
– Click on the server to drill down to the services
– Beside QueryService, click the drop down arrow
– Select “Stop immediately”
– Wait 30 seconds for it to fully stop
– Click the drop down again and select “Start immediately”

 

You can also review the article from IBM:

https://www-01.ibm.com/support/docview.wss?uid=swg22016796

AWS Lambda Sql Connection

If you are creating a web app that uses lambda and a mysql database, there will be many times where you need to have sql information passing to and from lambda functions. A way to connect a lambda functions to a mysql database is using npm mysql. To set up the connection, there must be a connection variable defined. This variable must contain the mysql host name, user, password, and database. Once these are defined, the lambda function can connect to the mysql database with the code “connection.connect()”. Once this is ran, your lambda function can now run queries. These queries are written just like a regular sql query. Once you are done running your query make sure to declare your connection closed by stating “connection.end()”.

The process.env variables would be were you put your sql information.

 

**Note: You might need to configure your lambda function to be set up on the mysql database’s VPC.

SQL DECLARE and SET datetime

This article discusses the DECLARE statement in SQL, which you can find the official documentation here: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-local-variable-transact-sql?view=sql-server-2017

The DECLARE statement is used in a SQL statement to declare a variable. The declared variable can then be set using SET statement. Once set, the declared variable can be referred by the SELECT statement.

The DECLARE statement can also be used to declare a DATETIME and the SET statement can modify this DATETIME with DATEADD. This allows us to avoid dealing with JavaScript datetime variables.

For our case, we wanted to query all individuals that were set to turn 18 years old within a year.

In order to do this, we first ‘DECLARE’d the variables we would need and modified them using SET:


Note: First argument of DATEADD function can be any accepted SQL interval (year,month,week,day,hour,second,etc.)

Now that our 3 variables had been set, we were able to refer to them in the WHERE portion of the SELECT statement:

Full query with results:

By specifying the search range using DECLARE and SET, we are able to query the DOBs of just those users that are set to turn 18 years of age within a year of the query date.

How to write fail safe stored procedures

We normally write stored procedures for nearly any transaction that has more than one component to it. For example, creating an invoice record that has many lines would require you to insert a record into a header table, and multiple records into a detail table. But what happens if you insert the header and one of the detail records fails to get inserted properly. In these cases, you’re usually greeted by a SQL Exception and error out. However, depending on how you write your stored procedure, you might now have one header record and some of the detail records in the database. In some cases this might be okay, but in most cases it is not and the preference would be to avoid inserting any of the records if even one of them fails. Luckily MySQL provides a really great solution for this problem.

You simply define the action to be taken once the exception is encountered, in our case a rollback, and then you start and end your transaction in between a “START TRANSACTION” and “END” directive as shown below:
BEGIN

    .. Declare statements ..

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN

          ROLLBACK;
    END;

    START TRANSACTION;

        .. INSERT  ..
        .. UPDATE  ..
        .. SELECT  ..

    COMMIT;
END

Modify column data type without dropping table

“String or binary data would be truncated.”

This is a common error if you’re trying to update a field with more data than it allows, e.g. trying to update a char(30) field with a 50 character string.

 

If you try to modify the column data type you’ll get a new error:

“The changes you made require the following tables to be dropped and re-created”. This might be an inconvenience for you.

Easy fix:  Go to Tools >> Options >> Select Designer and Uncheck >> “Prevent saving changes that require table re-creation”

 

And you’re DONE!  Modify the data type and run your update query.

IP Designer Series – SQL Transaction Node

The SQL Transaction node can be used to run T-SQL in any SQL database.

Connection information

  • Either select the configuration name that contains your JDBC connection information
  • Or select Override Connection and provide the override information

You can click “Build” to use the wizard to build your transaction, or you can write it out manually in the Activity Information field.

IP Designer Series – SQL Query Node

The SQL Query Node can be used to query any SQL Server database.

Connection information

  • Either select the configuration name that contains your JDBC connection information
  • Or select Override Connection and provide the override information

The SQL Query Node can run a SQL Query, Run a stored procedure, or perform a create/update command. If you are running a SQL Query, you can click the “Build” button to build the query in a GUI wizard.

A More Efficient way to Update Report Data Sources

During an upgrade, re-pointing all your LBI reports to your new database may seem like a daunting task. Never fear! You don’t have to do it manually (at least not all of them). Here are the steps to overwrite your data sources using a SQL update query:

  1. Create an ODBC connection on your LBI server that connects to your NEW database server.
    1. Don’t forget that you have to use the 32-bit ODBC tool for LBI data sources!
  2. Create your new data source in LBI
    1. Tools > Reporting Services Report Administration > Server Administration > New Data Source A More Efficient way to Update Report Data Sources_2
  3. Open SQL Server Management Studio (or whatever database management tool you use)
  4. Log-in to your LBI database server
  5. Navigate to the LawsonRS databaseA More Efficient way to Update Report Data Sources_3
  6. BACK UP YOUR ERS_REPORTDATASOURCES TABLE! (You should always back up the table or the database if you are making changes directly)
  7. Run a query to view all the reports for which you plan to change the data source: A More Efficient way to Update Report Data Sources_4
  8. When you are ready, run your update query using the same WHERE clause that you used when you viewed your reports in step 7
    A More Efficient way to Update Report Data Sources_5

    1. NOTE: you are updating RSDATASOURCE. This is so that the report data source will be overridden.
    2. If you have a new username & password for this data source, you will also need to update the DEFAULTUSER and DEFAULTPASSWORD fields as well.
  9. Now your reports should run against the new data source. There are a few cases in which you will need to manually reset the data source in Crystal and republish the report. They are:
    1. If your report mixes schemas (i.e. it uses tables owned by dbo, and views owned by xyz)
    2. If your report has subreports
    3. If your report has a command that explicitly defines the database name (and the database name has changed)
    4. There may be more, but these are the instances that I discovered on a recent upgrade!

Become a Lawson SQL genius in 50 minutes

Have you ever watched your DBA fly through Lawson data as if he had all the tables memorized and get you the exact dataset you wanted to see? Did it seem like magic? If you have always wanted to do that on your own then you have to join this free session. You will get all the tools of the trade and some very nifty sql you can stash away for that rainy day. If you don’t know SQL at all then be sure to attend the course above first. 

Download Powerpoint

Data Access Basics and Intro To SQL

This course is intended to teach you the basics of SQL data queries and what you need to get started. If you don’t know SQL at all, think of this as a mandatory webinar for the Lawson SQL webinar later in the same day.

Download Powerpoint