ODBC Registry settings are not reflected in UI - ODBC Database administrator(32-bit)

Question

ODBC Registry settings are not reflected in UI - ODBC Database administrator(32-bit)

Kumar, Saravana 21

I am using Windows Server 2019 version 1809 OS Build 17763.1158. I want to enable encryption for ODBC DSN connection So I am calling this command "C:\Windows\SysWOW64\odbcconf.exe /A {CONFIGSYSDSN "ODBC Driver 17 for SQL Server" "DSN=MyDataSource|Server=%ServerName%|Encrypt=yes|TrustServerCertificate=yes"}" from the script file (.bat file).

Registry settings are updated properly under "Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBC.INI\MYDatasource" but when I launch ODBC Databse administrator(32-bit) and slected my datasource clicked on configure -> Next -> Next->Next check "Use string encryption for Data" and "Trust server certificate" checkbox remains unchecked though registry settings has "YES".

Seeya Xi-MSFT 16,586 Reputation points

2021-12-13T06:19:47.75+00:00

Hi @Kumar, Saravana ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya

Accepted answer

2 additional answers

Your answer

Seeya Xi-MSFT 16,586 Reputation points

2021-12-13T06:19:47.75+00:00

Hi @Kumar, Saravana ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya

Answer 1

Hi @Kumar, Saravana ,

Please see this link:
https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/connection-string-syntax#windows-authentication

We recommend using Windows Authentication (sometimes referred to as integrated security) to connect to data sources that support it.

So this is why I want you to add Trusted_Connection=Yes.
According to your script, the SQL authentication method is created by default. And I also did a test. You can check whether the SQL authentication method is selected by default in the configuration window after running your script.

And in this case, the changes you make will also fail when you test the connection. So the registry will not be changed.

then If I execute the script , UI checkbox enable\disable started working based on script values.

So I suggest to choose the windows authentication mode, that is, use the command I posted before.

Best regards,
Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it.

Kumar, Saravana 21 Reputation points

2021-12-17T07:34:32.003+00:00

Thanks Seeya for your support. It worked.... Thanks again!!...

Answer 2

Hi @Kumar, Saravana ,

Welcome to Microsoft Q&A!
Please see this document about these two options:
https://learn.microsoft.com/en-us/sql/connect/odbc/windows/dsn-wizard-4?view=sql-server-ver15

Best regards,
Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Seeya Xi-MSFT 16,586

Hi @Kumar, Saravana ,

In addition, you need to note that if you use the ODBC manager to modify the DSN configuration, you need to test whether the connection is successful before clicking OK.

If the connection fails, please change Trusted_Connection to Yes if you are Windows login.

Please see this link: https://learn.microsoft.com/en-us/sql/relational-databases/native-client/applications/using-connection-string-keywords-with-sql-server-native-client?view=sql-server-ver15
See part from the link:

The above content is based on the command you gave, assuming you are the windows login way. If I misunderstood what you mean, please feel free to let me know.
Finally, good day for you.

Best regards,
Seeya

If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress.

Kumar, Saravana 21 Reputation points

2021-12-09T09:33:22.387+00:00

Thanks Seeya for your response.

I want to enable encryption for ODBC DSN connection through .bat script because I am enabling ODBC encryption as part of installation script.
My script file has "C:\Windows\SysWOW64\odbcconf.exe /A {CONFIGSYSDSN "ODBC Driver 17 for SQL Server" "DSN=MyDataSource|Server=%ServerName%|Encrypt=yes|TrustServerCertificate=yes"}".
When I run the script file, it updates the registry ("Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBC.INI\MYDatasource")for Encrypt=yes and TrustServerCertificate=yes but when I launch ODBC Databse administrator(32-bit) and selected my datasource clicked on configure -> Next -> Next->Next check "Use string encryption for Data" and "Trust server certificate" checkbox remains unchecked(not enabled) even though registry settings has "YES".

My Question is why checkboxes are not enabled for "Use string encryption for Data" and "Trust server certificate" in UI(ODBC Databse administrator(32-bit) )

How can i correct this from script file?
Seeya Xi-MSFT 16,586 Reputation points

2021-12-09T10:00:24.397+00:00
Hi @Kumar, Saravana ,

Try to modify your script file as:

odbcconf.exe /A {CONFIGSYSDSN "ODBC Driver 17 for SQL Server" "DSN=YourDataSource|Server=YourServer|Trusted_Connection=Yes|Encrypt=yes|TrustServerCertificate=yes"}

If you modify them in the ODBC Manager, don't forget to test whether the connection is successful after the modification.

Best regards,
Seeya
Kumar, Saravana 21 Reputation points

2021-12-09T11:36:38.887+00:00

Hi ,

When I run your command it created 64bit system DSN but same behavior only. Also we do not want use 64bit one.

I have one observation, When I launch ODBC Databse administrator(32-bit) - ODBC Manager and modify Encryption settings manually and save the changes.

Once I saved the changes through ODBC Manger then if run my script "C:\Windows\SysWOW64\odbcconf.exe /A {CONFIGSYSDSN "ODBC Driver 17 for SQL Server" "DSN=MyDataSource|Server=%ServerName%|Encrypt=yes|TrustServerCertificate=yes"}" Settings are saved registry as usual . Also UI Checkboxes are enabled for "Use string encryption for Data" and "Trust server certificate" as expected.
I mean manually save settings through ODBC manager then run my script enable\disable check box for "Use string encryption for Data" and "Trust server certificate" working fine as expected. Can you help me here to understand why is this happening ?
Seeya Xi-MSFT 16,586 Reputation points

2021-12-10T02:19:55.89+00:00

Hi @Kumar, Saravana ,

When I run your command it created 64bit system DSN but same behavior only. Also we do not want use 64bit one.

According to your previous script, I omitted one point and didn't mention it. You need to enter this directory which you mentioned before with cd C:\Windows\SysWOW64, and then execute my command. This is for the 32 bit sysDSN.
Whether you want to create and modify sysdsn, you can achieve the same result by writing a shell script or an ODBC manager. The difference between the two is a simple and visual operation(UI). Which method you use depends on you.
BTW, since it is sysDSN, if you want to create or modify it through the command line, please use run as administrator to open it.

Best regards,
Seeya
Seeya Xi-MSFT 16,586 Reputation points

2021-12-15T05:10:32.077+00:00

Hi @Kumar, Saravana ,

I haven't heard from you for a while, has the problem been solved? If you think the answer is helpful to you, please do "Accept Answer". By doing so, it will benefit all community members who are having this similar issue.
Good day for you.

Best regards,
Seeya
Kumar, Saravana 21 Reputation points

2021-12-15T09:54:25.837+00:00

Hi Seeya,

I was on leave so I could not reply you on time ..

As you mentioned I tried the script by running cmd as administrator. (My installer script also does the same)
Still observing the same issue. Once I edit the settings through ODBC manager manually then If I run my script and launch ODBC manager, UI check boxes are enabled\disabled based on my script. I mean First edit if I do manually through ODBC manager, It started working.

Can you please let me know why ?

Thanks,
Saravana K.
Seeya Xi-MSFT 16,586 Reputation points

2021-12-16T06:12:03.417+00:00

Hi @Kumar, Saravana ,

You can achieve the same result by writing a shell script or an ODBC manager. For example, TrustServerCertificate=yes in the script is equivalent to the check box of enable TrustServerCertificate in the ODBC manager. What the result looks like depends on your last operation.

Best regards,
Seeya
Kumar, Saravana 21 Reputation points

2021-12-16T06:31:22.227+00:00

Hi Seeya,

Yes I am achieving through the script but what I observed is " TrustServerCertificate=yes in the script and when I run the script , Registry got updated with TrustServerCertificate=yes but ODBC manager checkbox was not enabled for the same. I am talking about this issue only.

ODBC Manager UI checkbox enable\disable will work once I enabled them manually for first time , then If I execute the script , UI checkbox enable\disable started working based on script values.

Thanks,
Saravana K.

Share via

ODBC Registry settings are not reflected in UI - ODBC Database administrator(32-bit)

2 additional answers

Your answer