Indhold
Introduction ... 3
Computers involved... 3
Complete process ... 3
ODBC 32/64 bit issues ... 3
Process of setting up Registry settings for ODBC ... 5
Registry files with settings ... 5
Adjusting parameters for your individual installation ... 5
Parameters to adjust the following files ... 6
32-bit settings file ... 6
64-bit settings file ... 7
Test the ODBC drivers ... 7
Import settings into two Group Policies ... 9
Group Policy Management program ... 9
Create Group Policy ... 9
Import registry settings to group policy ... 11
Fix Hive-field might be needed ... 15
Link the group policy to a computer organizational unit ... 17
PM5 ODBC SysWow64 group policy ... 19
PM 5 Datasource definition ... 20
Introduction
This document shows how to define ODBC sources in Microsoft Active Directory using Group policies. The first sections show how to easily install the ODBC settings into a group policy.
The second section details how the group policy should look like in the Group Policy Management tool. Please notice, that Microsoft supports ODBC settings directly in Active Directory. I have never managed to get this functionality to work, so therefore the following manual describes how to achieve the same thing by using registry settings in the group policy.
Computers involved
You need access to a Microsoft Active Directory tool called Group Policy Management. This is installed on Domain Controllers and can be installed on other computers as well.
You also need a client, for instance a Windows 7 32-bit client.
Complete process
The process involves:
Configure settings in registry files
Run registry files to update client PC
Start Group Policy manager on the domain controller
Define a new policy
Import settings from the client PC to populate the group policy in AD
Assign the group policy to Active Directory Organizational Unit(s), where you client PC’s are located. Do the tasks listed above for ODBC 32-bit and name the group policy ODBC32 and do the same tasks again but for 64-bit ODBC and name the group policy ODBC64.
When Microsoft introduced 64-bit support to their operating systems, ODBC is available in two modes. You can access these by running the command ODBCAD32.exe in the following directories:
C:\windows\system32 C:\windows\syswow64
The system32 directory supports the 64-bit ODBC settings while the Syswow64 supports the 32-bit ODBC settings on a 64-bit machine. On 32-bit machines the SYSWOW64 directory is not installed and the settings can only be done in the system32 directory. This is by no means logical. Please see a PCSYS ReportByEmail manual “RBE Preparation before Install” (www.reportbyemail.com) regarding the difference and search microsoft.com for further information on this subject.
In the following section it is shown how to create a group policy for a 32-bit setup. You need to follow this manual and create two ODBC group policies, one for 32-bit and one for 64-bit.
Process of setting up Registry settings for ODBC
In the following a database called PM5 (PCSYS Preventive Maintenance) is setup on a PC or server using registry-files. After this the settings are imported into a Group Policy, which in turn is applied to computers, where the application is run. This makes it very easy to change SQL-server or SQL-server instances.
Registry files with settings
To easily install PM5 settings in registry, content of files is pasted here to be used on a client PC. Choose a client PC or a server to use as a platform for the Group policy. Notice, that settings are different depending on the operating-system, e.g. 32-bit systems (Windows 7 32-bit or Window 2008 32-bit for instance) needs the normal ODBC settings, while 64-bit systems (Windows 7 64-bit or Windows Server 2008 64-bit/2008R2) needs the SYSWOW64 settings.
Adjusting parameters for your individual installation
The following registry files needs to be installed on a machine, which we will use as a base for all the other clients. Some parameters in the listed files need to be adjusted for your installation.
Database specifies the SQL database name, for PM5 it’s usually Pm5.
Driver is the path for the SQL server path locally on the machine. This is always installed default on Windows operating systems. You can check that the file is located on one of your test machines in the directory specified.
LastUser is not used for any real purpose you can enter anything for this field.
Server is Windows server name, which runs Microsoft SQL server. You can also supply a full FQDN string like cphsql08.cph.pcsys.dk.
Trusted Connection is always set to “Yes”.
Please document the changes you need to make and adjust the files shown below in the next two sections with your own parameters.
Parameters to adjust the following files
You need to change the following parameters in the files below: Server = ?
Database = ?
Insert appropriate values depending on your SQL server installation.
32-bit settings file
Put the following text into a file called PM5.REG:
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\PM5] @="" "Trusted_Connection"="Yes" "LastUser"="heeradmin" "Database"="pm5" "Server"="cphsql08" "Driver"="C:\\Windows\\system32\\SQLSRV32.dll"
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources] @=""
"PM5"="SQL Server"
Double click the file and run it. Accept that it writes information to the registry if prompted.
You can check in the registry, that the settings have been installed / applied to the system by running the ODBC program ODBCAD32.exe from either the System32 or the Syswow64 directories.
64-bit settings file
For the 64-bit platform the ODBC Settings are located different. Only try to run this on a 64-bit system, like Windows 7 64-bit or Windows 2008 R2 Server.
Save the following into a file named PM5-64.reg:
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\PM5] "Driver"="C:\\Windows\\system32\\SQLSRV32.dll" "Server"="cphsql08" "Database"="pm5" "LastUser"="heeradmin" "Trusted_Connection"="Yes" @=""
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\ODBC Data Sources] "PM5"="SQL Server"
@=""
Run the file after you have adjusted the parameters to your own use.
Test the ODBC drivers
Test the ODBC connection to the database by opening the ODBC tool. On 64-bit versions you need to do it through a command prompt:
C:\windows\system32 Odbcad32.exe
This opens the GUI for 64-bit. Change to the DSN Sheet and see if the PM5 database Is visible. If visible open it and test the connection.
When doing the 32-bit testing on a 64-bit system you have to do the following: C:\windows\syswow64
Odbcad32.exe
Notice that it’s vital that you close the first GUI ODBC window before running the commands above, only one ODBCAD32 can run at the same time and if one is already running (64-bit) the last one will not start, it will just show the already running version.
See if the PM5 database with the name PM5 is located under the DSN sheet. Test to see if it can connect to the database.
Import settings into two Group Policies
When applying this to client machines, you will typically use one group policy for 32-bit machines and one for 64-bit machines. In the following section it’s shown for the PM5 Example using the 32-bit version. You need to define two group policies though.
Group Policy Management program
Start the Group Policy Management tool by starting Group Policy from Administrative tools or from commandline. To make sure you have the tool, log into a Microsoft Domain Controller server and do the following configurations on that.
After starting Group policy change to the folder “Group Policy Objects”.
Create Group Policy
Give it a name like ODBC PM5 Example
Rightclick and select Edit. Browse to:
Computer Configuration / Preferences / Windows Settings / Registry:
Import registry settings to group policy
Right-click Registry, select New, select “Registry Wizard…”Browse to the locations listed in the registry files in the previous sections, e.g. for 32-bit machines and 64-bit machines where Office has same number of bits: [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\PM5]
Machines where operating system bits and office bits differs:
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\PM5]
Select the PM5 folder. And select all the items in the lower pane (Remember to scroll down too to select all the hidden selected items too).
After the import the path is visible in the Group Policy:
And the ODBC Data Sources container:
Fix Hive-field might be needed
If that’s the case, you need to doubleclick each line:
Now you can see that this line is correct. Double-click and press Ok on all values. This is a Microsoft AD bug, so it will hopefully be fixed some time in the future.
Link the group policy to a computer organizational unit
In order to get the computers to use the policy, browse to a organizational unit, where the computers, which will be running Pm5, are located. In the following the container Is called PM5 example:
Select the ODBC pm5 Example container and press Ok.
PM5 ODBC SysWow64 group policy
You need to do all the same configurations again with another group policy, but this time you need to point in registry to the following areas:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\PM5
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\ODBC Data Sources In the first one you need to select all items, in the second one only select PM5.
PM 5 Datasource definition
This shows all the settings for the ODBC connection. Inspecting the Database Pm5 with the view set to Standard:
