Spludlow Web Header

ODBC Notes 32 bit


Introduction. 1

Setting up ODBC in Windows. 1

Sage Line 50 Example (Using 32-bit ODBC Driver). 1

Permissions Errors with ODBC (Using Sysinternals Process Monitor to find the problem). 1



ODBC has been around since Moses was a lad. Many propriety software products provide an OBDC interface. It can often be the only way of getting directly to the data yourself with certain products.

If a particular software product is using a well-known database for storing its data, for example SQL Server or SQLite, then it’s no problem just access the data directly. However, if it is using a proprietary data store then good old ODBC is there for you.

To use ODBC you need to install the ODBC drivers on the host that will be querying the data. The drivers are specific to a particular product and will be available from whoever provides the software, often installed along with the main program or maybe as an option, possibly as a separate download.

You will often find a particular product’s ODBC implementation will be read-only. This makes total sense as giving users the ability to directly modify data, in an accounts package for example, is likely to end in tears.

Don’t let read-only deter you though you can still achieve much from a read-only data source. Writing you own reports is the most obvious thing you can do, also reading delivery addresses from an accounts package and submitting them to courier software is another easy one. You could create your own tables in a separate database and link the records together on the primary key this would allow you, for example, to start storing extended information against a customer that the original data source did not have provision for. You can see where I’m going there is no end to the functionality you can provide with a read-only data source.

Sage Line 50 is a classic example of an accounts package used by many small businesses in the UK that uses a propriety data store with ODBC drivers. Creating an Intranet web that provides a customer search and displays recent orders and accounts details from Sage was the first thing I ever did in .net, when it was first released back in 2003. Sure you can search customers and display details in the Sage program itself but users found it much quicker and easier to use the Intranet when a customer is on the phone and the web pages can be tailored to just the way the business wants it. Before long custom web reports where added, automatic data transfer to the courier’s software, bespoke order confirmation and despatch emails, the list goes on. All this from read-only ODBC data access.

Setting up ODBC in Windows

When you have installed the ODBC drivers you can check they are installed with the ODBC control panel. If you search control panel or Windows search for ODBC you get 2 hits (I have provided there full paths here, not the most obvious names and locations):

·         Set up ODBC data sources (32-bit)            "C:\Windows\SysWOW64\odbcad32.exe"

·         Set up ODBC data sources (64-bit)            "C:\Windows\System32\odbcad32.exe"

Hopefully you will have 64-bit drivers but you may be working with some legacy product that still only provides 32-bit drivers. The program using the ODBC drivers must match the architecture. I will discuss in a bit how to deal with 32-bit ODBC without having to run all your code in 32-bit.

Within the ODBC control panel you can first look at the “Drivers” tab to make sure it is there and verify which architecture the drivers are for. There is a good chance that the driver installer will also have added a “System DSN”, this provides DSN (Data Source Name) that is available on that host from any application that can consume ODBC. You can often configure some basic parameters here like the file system path to the data files. You can also create a “File DSN”, this is just where the details are stored in a file rather than by Windows, both types will have slightly different connection strings when we come to it.

If you have Access you can test it out by connecting to a ODBC data source, the architecture must match, it you are running 64-bit Office and the drivers are 32-bit you simply will not see the DSN when you look for it.

Sage Line 50 Example (Using 32-bit ODBC Driver)

Here I will run through connecting to Sage Line 50. Although this example uses Sage the same principle can be applied to any product using ODBC.

I have purposely used an antique version (V10) that is easily over a decade old to demonstrate using 32-bit drivers.

For this test I didn’t even install Sage on my development machine, only the ODBC drivers, and used an old backup containing the data files.

Here I will show you how to setup the “Spludlow-Service32” service process (Windows Service) that is not installed as standard when installing the Spludlow Framework. If you are using 64-bit drivers then you can skip this step.

To start with you should test that you can connect to the data source using the “TestForm” application running in 32-bit just to make sure everything’s right at the start. Let’s run through the whole process:

Testing the ODBC driver in process:

·         Right click on the “TestForm” application within the Visual Studio solution explorer and select “Properties”

·         Click the “Build” tab on the left. You should see a “Prefer 32-bit” check box. Check it.

·         Within “button1_Click” enter the following code (This is using a system DSN for the connection string):

o   Spludlow.Data.IDAL source = Spludlow.Data.DAL.Create(@"[ODBC] DSN=SageLine50v10;Uid=Sage.User;Pwd=p@ssw0rd;");

o   Spludlow.Data.Schemas.SchemaReport(source, "ODBC");

·         Run the program and click the button you should get no errors.

·         Look on the Logs Intranet page and confirm you have a schema

Installing “Spludlow-Service32” and configuring for DALODBC:

·         Create an empty directory “C:\Program Files\SpludlowV1\Spludlow-Service32” (Note, in theory this should be in “C:\Program Files (x86)” as it’s running 32 bit but it don’t matter and the Spludlow Release system won’t be expecting it there)

·         Obtain or build the binaries for “Spludlow-Service32” copy the entire contents of “bin\Release” to the empty directory so the full path of the service will be sitting at “C:\Program Files\SpludlowV1\Spludlow-Service32\Spludlow-Service32.exe”

·         Run the following from the command prompt as Administrator (This just runs “InstallUtil.exe” you can do it manually it you like) make sure no errors

o   "C:\Program Files\SpludlowV1\Spludlow-Process\Spludlow-Process.exe" Spludlow Spludlow.Setup ServiceProcessInstall "C:\Program Files\SpludlowV1\Spludlow-Service32\Spludlow-Service32.exe" "HOSTORDOMAIN\SpludlowUser" "p@ssw0rd" False

·         Go and check in Computer Management->Services that is installed right, if there are any problems (that you forgot in the build) you can run the above command with “True” as the last parameter to remove it and start again. Don’t start it yet.

·         If you want to use the Framework’s release system on this service then on the host that you built it add the following line into the file “C:\ProgramData\SpludlowV1\Config\DevelopSources.txt” change the project path to wherever your project sits (Note tabs used as column delimiters)

o   Spludlow-Service32                        C:\Develop\SpludlowV1\Spludlow-Service32

·         Register the service to the Spludlow Framework by adding the following line to the file “C:\ProgramData\SpludlowV1\Config\Applications.txt”:

o   Spludlow-Service32                        C:\Program Files\SpludlowV1\Spludlow-Service32                            Service                 Spludlow-Service32V1:32150

·         Create an empty text file “C:\ProgramData\SpludlowV1\Config\Spludlow-Service32.txt” and put the following lines in (this file describes what task(s) the service will perform):

o   #Server Key                        Assembly            Type                                                      Port

o   Server   SageODBC           Spludlow             Spludlow.Data.DALODBC              32151

·         Go to the Intranet Status page and you should see a line for “SageODBC” the Service should still be stopped and the thread shows it offline. Don’t start it yet.

·         Go to the Intranet Config page in the “Send and View Host Configuration files” section and click on view configuration link for the right host in the “Online (Normal)” column. Right down near the bottom confirm you have a line resembling this one:

o   <add key="Spludlow.Remotings.SageODBC" value="net.tcp://myhost:32151/Spludlow.Data.DALODBC" />

·         Still on the Intranet Config page in the “Send and View Host Configuration files” section tick your host and click the “Send Spludlow.config” button. This gets the remoting address confirmed in the previous step on the host’s configuration.

·         The previous step automatically restarts all services on the host so the new service should be up and running, check it on the Intranet Status page.

·         If the service is running on a separate host to “TestForm” you need to perform another process described here “Putting the Remoting Address at Network level in Configuration”

·         Switch “TestFrom” back to 64-bit by unticking the “Prefer 32-bit” check box, described in the first steps. This proves the ODBC drivers are running out of process.

·         Run the following code in “button1_Click” of “TestFrom.exe”, this is the remote version of the code in the earlier example:

o                   Spludlow.Data.IDAL source = Spludlow.Data.DALODBC.MakeClient("SageODBC");

o                   try

o                   {

o                                   source.Initialize(@"DRIVER={Sage Line 50 v10};DIR=C:\TEST\SageLine50v10\ACCDATA;UID=Sage.User;PWD=p@ssw0rd;");

o                                   Spludlow.Data.Schemas.SchemaReport(source, "Remote ODBC");

o                   }

o                   finally

o                   {

o                                   Spludlow.ServiceModels.Close(source);

o                   }

·         Check the Logs page on the Intranet and confirm it worked.

·         You can now put the ODBC connection string in config, as ODBC is only local you can put it at the host configuration level, use the Intranet configuration page and send it out:

o   <add key="Spludlow.ConnectionString.Sage" value="DRIVER={Sage Line 50 v10};DIR=C:\TEST\SageLine50v10\ACCDATA;UID=Sage.User;PWD=p@ssw0rd;" />

·         You can change the Initialize line in code now to (if it changes in future you only have to change it in one place in config):

o   source.Initialize(“@Sage”)

Permissions Errors with ODBC (Using Sysinternals Process Monitor to find the problem)

When testing with the Sage ODBC driver I found it ran fine in process, using TestForm.exe, logged in as myself who has local Administrator permissions. When I remoted the DAL and ran it from Spludlow-Service32 that runs under the “SpludlowUser” account I got the ODBC error “ERROR [28000] User ID or Password invalid”. I’m using the same connection string with the same user ID and password so I suspect this error is misleading. I have already made sure the data directory has permissions for “SpludlowGroup” so what is going off?

In this example I fix a problem with a very old Sage ODBC driver but the same technique can be applied to fixing all manner of problems.


The program “Process Monitor” is an excellent tool for this sort of problem. The tool basically lists everything that processes are doing; File System Access, Registry Access, Network Access, and Thread activity. When first run it may freak you out, as it’s logging everything that’s going on, you just need to set up its filter. Here’s what you do:

·         Run the program. It may start by displaying the filter dialogue but if it doesn’t it will be listing stuff out like a mad man, perform the next 2 steps.

·         Click the Magnifying Glass icon to stop capture a cross will appear over the magnifying glass to indicate capture has stopped

·         Click the Rubber on a sheet of paper icon that is next door but one to the magnifying glass icon, this will clear the screen.

·         Click the funnel icon that is the next icon along and the filter dialogue box appears.

·         There are a load of red cross filters setup as standard, ignore these and leave them, they are to filter out system stuff. If you setup a filter before for another process then remove it now.

·         Change the dropdown list to “Process Name”

·         Make sure the next dropdown list is set to “is”

·         In the textbox enter “Spludlow-Service32.exe”

·         Make sure the last dropdown list is set to “include”

·         Click the “Add” button and a green tick line should appear.

·         Click the “OK” button to get back to the main screen.

·         Click the magnifying glass icon with a cross to enable capture the cross should go away.

·         You should now start to see a few thread events appear every few seconds

·         Just to play it safe stop and re-start “Spludlow-Service32.exe”

·         Loads to events should appear. Click the rubber icon to clear the screen

·         Perform the test that causes the problem. In this case I ran a simple test from “TestForm.exe” more info !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

·         You should now see a good few pages of events, the problem will be in here somewhere

Now this is the tricky bit, some intuition may be required to spot the problem. Let’s start by looking at the “Result” column you can see there are many “SUCCESS” and various others like “NAME NOT FOUND”, I suspect the problem is permissions related so look for “ACCESS DENIED” events.

Looking through the “ACCESS DENIED” events I can see 2 groups of 3 events related to the file “C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\3319a3eab6155119898e3b666aba62aa_44564ac1-1088-451e-ae63-408d70226d12” (This filename will be different on other hosts). Could this be the problem? I’ve no idea what this file does, it doesn’t relay matter but let’s have a look at it. Find the file right click on it and select properties. I notice the file was created a few days ago so it would have been created when I first started testing with the Sage ODBC driver running from the logged in user (not “SpludlowUser”). Click on the “Security” tab click the “Edit” button and add “SpludlowGroup”, leave it at is with “Read” and “Read & Execute”, OK the dialogue box.  Perform the test again and see if it worked, Bingo, it did.


Spludlow Web Footer