Contents
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.
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.
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”)
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.
https://technet.microsoft.com/en-us/sysinternals/processmonitor
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.