I searched around and found two different approaches to creating 32-bit System DSN’s on a 64-bit box. One was using powershell to load registry keys, the other used GPO Client Side Preferences to define User DSN’s which creates the 64-bit DSN and a 32-bit equivalent.
The thing is, I actually prefer using GPO’s for settings instead of scripting them. Also, I want to be able to update DSN’s without having users logging off and back on.
For simplicity I create and test the 32-bit System DSN on the 64-bit box using the 32-bit ODBC Admin, %systemroot%\SysWOW64\odbcad32.exe.
Then I load up the GPMC, create or edit my policy and go to Computer Settings > Preferences > Registry.
Right Click on Registry and create a New Collection Item.
If you use this GPO to create only System DSN registry keys you can simply type in a logical name for the DSN. In my case, I named my collection 32-bit System DSN’s because I have several other registry entries in my GPO.
Right-click on your newly created Collection Item, and select New > Registry Wizard. Assuming you manually created the 32-bit DSN on the same box as you are running the GPMC, simply click Next.
Now go to HKLM > Software > Wow6432Node > ODBC > ODBC.INI
Here you should find any 32-bit System DSN’s you have created. Click the one you want to create using the GPO and select all the settings within the selected key. Click Finish.
Now you will have a new Collection Item, “Registry Wizard Values” within the one you created (the one I named “32-bit System DSN’s”) . You should rename this to something that makes sense to you.
Now, all you need to do, is apply this GPO to all 64-bit servers or client computers that require the 32-bit System DSN.
Great article! I followed the steps and the registry key is being created; however, when I run C:\Windows\SysWOW64\odbcad32.exe from the local machine, I cannot see the System DSN. How do I get the DSN to show up in that screen?
I actually didn’t think much of it, since the sources were working. However, to see them within the 32-bit ODBC Admin, you will need to include this value when adding your ODBC Source to the GPO:
HKLM > Software > Wow6432Node > ODBC > ODBC.INI > ODBC Data Sources > Name of your newly created data source.
You can add the key manually to the GPO if you deleted the manually created data source prior to deploying the GPO.
To do this, you need to add a String value named exactly similar to the Data Source Name you created, and as the Value enter the Driver name, eg. “SQL Server Native Client 11.0”.
That should do it.
Let me know if you have any issues.
Excellent! thanks, worked like a charm!
I was adding a SQL Server ODBC Connections, so the value of my String was “SQL Server”
I’m glad it helped.
How do I know what the driver name is? I added “SQL Server Native Client 11.0” and it didn’t work.
Did you choose the “SQL Server Native Client 11.0” as your driver? Perhaps it was the SQL Server or a previous version of the Native Client.
Each Registry Key under this one represents an installed driver:
HKLM > Software > Wow6432Node > ODBC > ODBCINST.INI
Please note that it’s not the ODBC.INI Key.
So you should be able to find the correct one from there.
I used SQL server as my driver, so would I choose %WINDIR%\system32\SQLSRV32.exe as the value for the string?
You must use the name of the Key, in your case you used the SQL Server driver. The name of the key under the ODBCINST.INI key, is SQL Server.
So, you should put in SQL Server as the data in the String value named exactly as your System DSN name.
Let me know if this works for you. If not, we can set up a TeamViewer session (or similar) so that I can help you through this one.
I got it! Thanks for your help, this site has been extremely helpful! Question, if I use [computer configuration] -> [preferences] -> [control panel settings] -> [data sources] and I create a system DSN will that show up on a Windows XP machine? I have no problem having it show up on a Windows 7 machine but on a Windows XP machine it isn’t showing up and it doesn’t look like it is trying to apply it. Is this a compatibly issue?
I just installed client-side extensions for XP and my drive maps came up, but my shortcuts and DSN’s didn’t. Your thoughts?
Nic, you must have read my reply as I was typing – that’s pretty cool 😀
Where are you trying to place the shortcuts? Under All Users or the individual users?
Do you get any entries in the Event Log in regars to either shortcuts or DSN’s?
If your GPO Client Side Preferences are not applied an XP system, 32- or 64-bit, it is usually because you need to install Group Policy Preference Client Side Extensions for Windows XP.
You should be able to find them here for both 32- and 64-bit:
Hopefully that will make it work for you as well.
This was a tremendous help, Dani.
Thanks for writing this, it is hands down the best method I’ve found to deploy 32 bit ODBC connections to 64 bit computers. Combined with item level targeting it works like a charm.
Thank you Adrian.
I am glad this was of use to you.
What would happen if this same GPO is applied to 32-bit machines? I have a mix and they are not currently split into OU’s based on 32/64.
The short answer is; probably nothing. I have not tried it, but one of two things could happen:
1) The Wow6432node is created as a subkey under Software, as are the rest of the keys below ODBC etc. But since it’s a 32-bit OS this key is ignored completely.
2) The GP Preferences extension is unable to create the ODBC key because the Wow6432node does not exist. This will probably create en error in the eventlog.
But my advice would be to use the targeting feature of the GP Preferences to ensure that you only target 64-bit machines using this method.
I’ve been researching this for a couple days and it seems that there’s no way for the registry key to store the password used when doing SQL authentication. The registry stores the ‘LastUser’ key, but the password is kept elsewhere, some place that’s not the same as the odbc.ini\ hive. Does anyone know where that key is?
On which operating systems are you trying to do this?
I have faced a similar issue with Windows 10 clients, and found no workaround. The only way to automate ODBC datasource creation on Windows 10 is to configure your SQL Databases security/logins to use Windows Authentication.
This works brilliantly, but I am wondering on removal of these registry items when no longer needed.
Looks like you can set the entire collection to be removed at once, so only two options I can think of are setting each registry item to remove when no longer applied, or create a totally new item to remove the whole folder at once?