Forum Discussion
SSMA Extension Pack for Oracle not compatible with ODBC 18
Good day,
We use SSMA Extension Pack for Oracle on our DB, and upon installation of ODBC 18 Drivers the following error now occurs. From my understanding, this is due to SSMA Extension Pack using older drivers and being forced to connect with encryption due to latest ODBC.
So the only way to fix would be for MSFT to update SSMA Extension pack for Oracle. Is this correct?
Attached screenshot of the error.
Please help advise, thanks.
Hi,
exactly the same problem here - thank you for throwing this in
Surprisingly, still no reaction; Microsoft, please help!!
one would assume that there's an interest in people moving from Oracle to MsSQL(?)BTW, we "solved" this by using the "ODBC Driver 17" version (which does not yet include
the breaking change regarding the handling of encryption)
We would like to be able to use a current driver!!Hi, Sean.
It's worth noting up front that there is no need to revert to the ODBC 17 driver as it's only a change in default encryption settings between 17 and 18. Both versions let you enable or disable various aspects of encryption.
I'm unfamiliar with SSMA but the error is straightforward.
The message is stating that it can see a certificate on the server used in the data source but that the certificate is not trusted (as is typically the case where someone has used a self-signed certificate). So, encryption being enabled by default is not the root cause of the error.
If a trusted certificate is used on the target server, the error will go away.
If your organisation operates an internal PKI infrastructure (such as AD CS) then you can get a trusted certificate for free.
If your organisation does not operate an internal KPI then you'll likely elect to purchase a certificate issued by a public certificate authority - just like any other web server certificate (or if you think you can cope with the technical knowledge and process overhead, take a look at LetsEncrypt which is "free" in terms of payment for the certificate, but tends to cost in time investment).
I don't recommend disabling encryption, as to use an extreme analogy, it makes as much sense as asking all the Internet's web sites to roll back from using https to http.
Still, the ODBC driver does support both the trusting of untrustworthy certificates (such as the self-signed option mentioned above) and even disabling encryption altogether (again, I don't recommend this).
Parameter Values Description TrustServerCertificate Yes, No Yes: Trust any certificate;
No (default): Require a trusted certificate.
Encrypt Yes, No, Strict Yes: Require encryption;
No: Do not require encryption;
Strict: Forces a trusted certificate to be required, thereby entirely ignoring the TrustServerCertificate parameter.
Now, how you set this for SSMA is what I don't know. I can see different options but that just leaves me unclear as to which one might be relevant.
In .NET, it's simply another keywords added into the connection string, but again, SSMA appears to be a different beast. One method appears to be during the setup of the extension pack on the SQL Server (i.e. the target server):
While another two options relate to the SSMA for Oracle console:
- Creating Script Files (OracleToSQL) - SQL Server | Microsoft Learn
- Creating the Server Connection Files (OracleToSQL) - SQL Server | Microsoft Learn
Perhaps someone else can speak to that topic, or you can figure it out for yourself, but it's certainly appears to be possible.
Cheers,
Lain
Hi Lain,
thank you very much for the prompt response -
actually I'm aware that ODBC Driver 18 also supports "no encryption" - it's an option that can be set in the connection string (Encrypt=No)
(and thank you for the hint about using encryption - we're also aware of this topic)The problem is, that SSMA for Oracle also comes with an "Extension Pack" that needs to be installed on the server where the SQL
Server resides -
it provides DLLs that are then used by extended stored procedures that come with SSMACalling these xp_*-procedures obviously generates a new session
Unfortunately, there seems to be no way to set the "no encryption" option - or is there one?
The documentation unfortunately does not mention anything of the kindAny help would be much appreciated
KR
ChristianHi, Christian.
I'm afraid I cannot answer anything about SSMA. I'm approaching this more from the generic SQL Server and ODBC perspectives.
I would be curious to know if you can see any registered system or file DSNs though, which you can check through running both of the following commands:
- 64 bit: C:\Windows\System32\odbcad32.exe
- 32 bit: C:\Windows\SysWOW64\odbcad32.exe
If there are any interesting looking DSNs, you should be able to jump into the properties and adjust the two encryption-related settings there.
I won't get my hopes up though as I have not encountered a single reference to the use of ODBC in the SSMA documentation I've read so far (which isn't a lot). It's all been OLE DB provider references.
Cheers,
Lain
