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;

[box]

User Configuration > Preferences > Control Panel Settings > Data Sources

OR

Computer Configuration > Preferences > Control Panel Settings > Data Sources

[/box]

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.

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.

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

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.

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.

[box]

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

[/box]

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

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.

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

[box]User Configuration > Preferences > Windows Settings > Registry > Collection
[/box]

Select New > Registry Wizard.

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

3. Navigate to;

[box]

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

[/box]

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

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

 

Related Articles, References, Credits, or External Links

NA