SAP BODS - SQL Server 2019 hosted on Azure Cloud Execution error

[former_member837879]

Dear All,

We have SAP BODS 4.2 SP14 Patch 24 (latest patch - 1) installed and running on Windows in our environment. One of the job is configured to load data to SQL Server 2019 hosted on Azure Cloud.

1. Data Store is properly configured and successful to the target SQL Server 2019 on Azure

2. Table import within ETL is working fine from target database SQL Server 2019 on Azure

3. But whenever we try to view the table data using Table -- > Right click -- > View Data, we receive the error attached.

4. As per the SAP recommendation, We have used ODBC drivers (Data Direct ODBC drivers v8.0 SP2) and tried using other drivers as well (SQL Server, SQL Server Native Client 11, ODBC driver 13 for SQL server)

5. ODBC configuration is working fine to the target SQL Server 2019 on Azure (for the above listed ODBC drivers)

5. We have checked the number of connections occuring in the target database and it is just one connection when we try to read the table data.

Could you please advise what we need to check further on this ?

image.png

[jmuiruri]

Greetings @sayeeswaran_88,

Please check whether Force Encryption has been enabled on the database side, if yes please set it to no disable it, it might require you to restart the SQL Server instance. Once the instance is restarted go back to the job and make sure your data store is using ODBC driver 17 and then try viewing the data and if that works make sure the job server is also using ODBC driver 17 and the rerun the job.

Best Regards,

Joseph

[former_member837879]

Force Encryption is already in Disable Mode (Set to 'No')

[jmuiruri]

Greetings,

Could you also check that in the DSN setup Data encryption has not been enabled. Also Test that the connection actually works on the ODBC data source administrator.

Best Regards,

Joseph

[former_member837879]

Hi Joseph,

As the target SQL server 2019 is hosted on Azure, as per SAP Recommendation we are using 'DataDirect ODBC Driver v8.0 SP2' as we are on DS 4.2 SP14

Earlier we have tried the recommendation you made, but still we received the same error

[jmuiruri]

Greetings Noema Basis,

Are able to identify the TLS versions used by your Azure SQL Server?

• You can use openssl to test the SSL/TLS connection between the client (SAP Data Services designer machine) and Azure SQL server.
• the command to connect to Azure SQL server using OpenSSL client would look like below

openssl s_client -connect <server_name>:<port> -ssl3
# you can also use the -tls1, -tls1_1, -tls1_2, options to specify the version of SSL/TLS you want to use

If the connection is successful you should see a message similar to "Verify retrun code: 0 (ok)" at the end of output indicating that the SSL/TLS handshake was successful.

Have you tried using the native SQL connection instead of ODBC?

From my experience the Invalid Attribute error message means the database or the driver is expecting another parameter that is either missing or set incorrectly. The tricky part is identifying which parameter, some drivers have expect different parameters to be configured, and when they are not you end up with this exception.

I have seen the Invalid attribute happening if the database was not listening on the default port 1433. In this case you have to define the port number explicitly at the data store level.

Best Regards,

Joseph

[former_member837879]

SSL connectivity is fine from DS server hosted in Windows.

and from the RDS desktop from where we access the DS client tool.

I have checked with the database team and they confirmed that the SSL/TLS are alredy disabled

Regards

[jmuiruri]

Greetings @Noema Basis,

1. Have you tried using the servers IP address instead of the domain?
2. Also check that both ping by IP and by host name work from this server. Otherwise make sure the the domain name resolves to the correct IP
3. Please also enable the ODBC driver tracing then reproduce the issue and check the ODBC logs for more information. https://learn.microsoft.com/en-us/sql/odbc/admin/setting-tracing-options?view=sql-server-ver16

Best Regards,

Joseph