Forum Discussion
SSMA Extension Pack for Oracle not compatible with ODBC 18
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 SSMA
Calling 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 kind
Any help would be much appreciated
KR
Christian
Hi, 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
- Hi Lain,
thanks again for the fast response - and thank you for your time
The extension pack seems to require ODBC; so we seem to be left with options to either stick with
"ODBC Driver 17" (not good) or introduce encryption on SQL Server (with some impact)
Maybe Microsoft could add some hint about this issue in the docs of SSMA(?) Could possible help others
KR
Christian