Deploy ODBC Settings via Group Policy

KB ID 0000805 

Problem

I’ve briefly mentioned this before when I wrote about Group Policy Preferences so when I had to do this on-site this week, I jumped straight into the group policy management console, and found that because my ODBC connection was using SQL authentication (with the SQL sa account), this would NOT WORK, (it only works with Windows authentication and even then it needs a tweak). If you are using SQL authentication jump down to the bottom of the article.

Solution

NOTE: Below I’m dealing with user DSN ODBC connections, so I’m looking at User Policies, if you want to send out Machine DSN ODBC connections then you need to be looking at Computer Policies.

Deploy ODBC Settings via Group Policy Preferences (Windows Authentication)

The GPP is pretty easy to locate you will find it in;

User Configuration > Preferences > Control Panel Settings > Data Sources

OR

Computer Configuration > Preferences > Control Panel Settings > Data Sources

However you will find there is a bug in the system which means it does not deploy.

ODBC Settings fail to Deploy via GPO

1. Locate the ODBC connection that you are trying to deploy > right click > Copy.

ODBC via Group Policy

2. Right click your desktop and ‘paste’ > You will get an XML file > Open it with notepad > Delete the username and the cpassword information > Save the file.

ODBC via Group Policy cpassword

3. Then delete the original ODBC file from your group policy.

ODBC via GPO

4. Drag the XML file into the policy, in its place > Select ‘Yes’ to import it.

WARNING: Do not open its settings/properties from this point forward, or it will break again.

ODBC via Group Policy

Getting ODBC Settings from a Clients Registry

1. You may wish to locate and extract the ODBC settings from a working client, you can locate the settings in a working client machines registry and simply export them so you can import them on a target machine, or deploy them via GPP or logon script.

ODBC export from registry

User DSN's
Computer>HKEY_CURRENT_USER>Software>ODBC>ODBC.INI
Machine DSN's 
Computer>HKEY_LOCAL_MACHINE>Software>ODBC>ODBC.INI

2. Simply right click the key that corresponds to the ‘name’ of the ODBC connector that you wish to export, > right click > Export > Save.

ODBC export registry key

Deploy ODBC Settings via Group Policy Preferences (SQL Authentication)

In this example I’ve merged the ODBC connection details into the registry, you could just as easily set them up manually, as long as they exist, either on the machine you are creating the policy on, or another machine you have ‘remote registry’ rights to.

ODBC export registry key

1. Create or edit a group policy and navigate to;

User Configuration > Preferences > Windows Settings > Registry > Collection

Select New > Registry Wizard.

Deploy Registry Setting via GPO

2. Select where you want to collect the registry information from > Next.

Collect Registry Setings for GPo

3. Navigate to;

User DSN's
Computer>HKEY_CURRENT_USER>Software>ODBC>ODBC.INI
Machine DSN's 
Computer>HKEY_LOCAL_MACHINE>Software>ODBC>ODBC.INI

Select the OBDC name that corresponds to the one you want to collect, then select all the settings within that key > Finish.

Collect ODBC Registry Keys

4. The finished GPP should look like this > Close the policy editor.

GPP for ODBC Settings

 

Related Articles, References, Credits, or External Links

NA

Author: Migrated

Share This Post On