Using FreeRadius with both EAP-PEAP authentication and PAM authentication

G1I recently implemented FreeRadius on Fedora Core 7 to provide authentication for our Cisco Wireless Network. I then had to extend that to provide PAM authentication to our RSA Authentication Manager for our remote office VPN because their router didn’t support direct RSA authentication. While that sounded simple it actually was quite painful at first.

 

For some reason, probably configuration errors on my part, I could not get the remote Cisco 2801 router to successfully authenticate to the RSA device through PAM on the freeradius server. It always returned an error stating “No Auth-Type set.” After trying several modifications I found that I could successfully do pam authentication by adding:

 

Update control{

                Auth-Type:=PAM

}

 

into the Authorize{} section. However, this forced ALL authentication to be PAM and that broke wireless authentication. After more fiddling I added a second IP address to the radius server and configured freeradius to use virtual servers listening on two separate IP addresses. The first virtual server handles normal EAP-PEAP authentication and the second virtual server handles PAM authentication.

 

Here are the important parts of my config:

 

The listen section:

 

listen {

        type = auth

        ipaddr = 1.2.3.1

        port = 1642

        virtual_server = one

}

 

listen {

        ipaddr = 1.2.3.1

        port = 1652

        type = acct

        virtual_server = one

}

 

listen {

        type = auth

        ipaddr = 1.2.3.2

        port = 1642

        virtual_server = two

}

 

listen {

        ipaddr = 1.2.3.2

        port = 1652

        type = acct

        virtual_server = two

 

}

 

 

The virtual server section:

 

server one{

        authenticate {

                eap

                pam

                Auth-Type CHAP {

                        chap

                }

 

                Auth-Type MS-CHAP {

                        mschap

                }

        }

 

        authorize {

                mschap

                eap

        }

}

 

server two {

        authenticate {

                eap

                pam

                Auth-Type CHAP {

                        chap

                }

 

                Auth-Type MS-CHAP {

                        mschap

                }

        }

 

        authorize {

                mschap

                eap

                update control {

                        Auth-Type:=PAM

                }

        }

 

 

}

 

 

There are probably some things I can remove from the second virtual server since I am forcing it to use pam, but it works so I’ll leave it alone for now.

 

Later,

Gary

Cleanup and Migration of Access Database to Microsoft SQL - Part 5

The Saga So Far:

In episodes one we covered the basic pains of cleaning up the database tables, queries, macros, etc in order to get a clearer picture of the project scope. In episode two we covered business process analysis. In episode three we covered the process I am using to normalize the data. In episode four we covered basic design criteria for the basic interface.

Access Strikes Back:

I started writing the application to synchronize the access database with the new SQL database in realtime. It used an XML file to control how data changed formats, and even include dynamic sql calls where needed to do lookups (example: converting employee names into employee Id numbers to link into the normalized tables on SQL).

The application worked great. I had added boolean fields to the tables on both access and sql to notify the app when a record was changed and that limited the number of rows that were being returned in the select statements. I added the hooks in the access forms to update these fields since I didn't have triggers available for use. Everything looked fine....until I started running it in production.

I started out simple, adding one table at a time to the live sync. I had narrowed it down to a necessary 15 tables. Unfortunately some of these tables had over 90 fields. Anyway, after I started synchronizing the third table, the access database began to get corrupt and I had to do a weekly "compact and repair" to fix the issue.

As a result of the regular database corruption I switched back to using the nightly push from access to sql and modified the plan for rolling out the new application. Instead of migrating users one job function at a time, we are developing the new system in parallel and will let them "play" with it until they sign-off on the app and we put it into production.

During this process I got together with the guys who wrote our web base time entry system and we decided to design a common middleware piece for our two applications to interface with. I will be using .NET Remoting for my accounting users and they will be using XML Web Services to access their data. This will allow us to develop common classes for the different business objects and apply business rules that will be consistent across all the applications.

We'll see how it goes. We have the diagram for the middleware piece done and are starting the coding of the base object classes (timesheet,employee, blah blah blah).

Check back later for the next update to the saga!!!

Some days I hate computers ;)

Cleanup and Migration of Access Database to Microsoft SQL - Part 4

The Saga So Far:

In episodes one we covered the basic pains of cleaning up the database tables, queries, macros, etc in order to get a clearer picture of the project scope. In episode two we covered business process analysis. In episode three we covered the process I am using to normalize the data.

The Next Step:

Now we have to determine whether to keep the front end reports and forms that users currently use in their day to day operations or to design a new front end application from scratch. In order to determine this I first looked at form design.

In our case the forms used very little code, and the code they did use was autogenerated for buttons. This meant that there was no data validation going on and most of the actions performed by the buttons were handled by macros. This did not lend itself to an easy upgrade for use by SQL server if I wanted to increase the reliability and functionality of the application. The forms also made heavy use of text boxes where combo or drop down boxes should have been used to limit the choices to valid ones for the field. This has caused me no end of grief in trying to normalize the data as it is uploaded to the SQL Server while maintaining the current level of functionality.

Our reports were mainly done outside of Access in Crystal Reports. During the migration saga the company also decided to move to a new reporting software so all the reports were being redesigned anyway by another individual. This person was rewriting them to work with the SQL Server so this became a non-issue.

After weighing the time required to overhaul the myriad of forms and macros and turn them into an efficient reliable user interface, I decided to develop a new front end application in parallel to the current Access Application. Since there are several pieces to this application I needed to devise a way to keep both databases synchronized as I migrated functions from the Access application to the new application that used SQL Server to prevent issues for the various users doing the different functions (TimeKeeping, Accounts Receivable, Accounts Payable, Project Management, and Payroll). This process needs to be in place and working before I begin to design the new program or I will never get anyone to use it.

The next part will go into more detail about the synchronization application and the pitfalls I experienced. I decided to do that in the next segment because I am writing the app as this is being posted to the site. Check back in a couple of weeks for the next part.

Cleanup and Migration of Access Database to Microsoft SQL Server - Part 3

In the Beginning

Now that you have removed everything obvious, its time to move on to normalizing. You can do this one of two ways: either normalize the data in the existing database; or create normalized tables in the SQL database and force the data into the proper format using VBA code when you push it into the database.

Which Method

In my case there is a second database that receives nightly data dumps from the main accounting database. This second database is used by other employees solely for the purpose of running reports. One of their chief complaints is that the data is only up-to-date as of the previous business day. This second database uses duplicates of many of the tables in the main database. For this reason I chose to migrate the secondary database to SQL first since it is less mission critical and I can get the kinks worked out of the process.

The Surgery

I began by listing all the necessary tables for the reporting database. I then looked at each table and determined what needed to be done to normalize the data. This took quite a bit of time and research, not to mention a lot of scratch paper.

Once I had determined the best table design I then proceeded to create the tables and relationships necessary. The original access databases had absolutely no table relationships, and because of this, data was constantly being corrupted by people directly manipulating tables without updating the tables containing related data.

The next step was to create an interface for getting the data into the sql database. Since I am using stored procedures this is a two step process. The first step was to create the actual stored procedure that will do appropriate data checks and lookups before writing the records into the database. The second step is to create the VBA procedures to actually push the data using ADODB. There are two vba functions: The first is to do bulk uploads of all records and the second is to update records when they are modified in the acccess database.

Function number one is pretty straight forward. Create the ADODB objects necessary. Get a recordset from access containing all the records to upload. And finally, loop through the recordset and call the stored procedure for each record. The stored procedure will return the record index of the record and write it back into a newly created field in the access table. This will provide the link between the data in the access database and the sql database for future updates.

Function number two will be called from forms whenever they create or modify a record. If they create a new record the RecIndex field will be empty and the code will know to add a new record and return the record index so it can be placed back in that field. If they modify a record that already has a RecIndex then it will call another stored procedure that updates the corresponding field.

The Work Begins

This process will take some time to complete since I have to do this for over 10 tables as well as creating the hooking functions in the forms to keep the data up-to-date. The next article will look at modifications to the existing database and reports so it can use the data in the database.

Cleanup and Migration of Access Database to Microsoft SQL Server - Part 2

The second step of this process is to take a hard look at the each business process that involves the application. We need to determine which program functions logically follow the business processes and which ones actually slow down the business processes by being incorrectly designed. Throughout this entire process I need to remember that these people are the customers and its in my best interest to give them a robust and stable product. That will minimize the amount of rework that I need to do in the future.

Where To Start

I approach this in a top down fashion. I start by interviewing the managment that oversees the people using the application. I need to determine at a high level how each business process should work (in an ideal world) and which users are involved.

Follow the Chain

Now that I have a high level understanding of each of the processes I can now interview the users involved in the day to day execution of the processes. I need to take detailed notes on how they perform that portion of the process and also find out if the current application lacks any features, or has any flaws, that impact their jobs.

Put it All Together

Now that we have interviewed all the necessary people we need to get a roadmap of the process. Personally I use visio, but you can use pen and paper or whatever other method you desire. The end goal is to generate a flow-chart detailing the steps of each business process and the pieces of the existing application that is used. I also note on the map which pieces of the application will need to be redesigned. In the end it gives you a clear understanding of the scope of the project and you will know what pieces can be ported directly to SQL server and which ones need to be redesigned before being ported over. Remember, our end goal is to get all of the data off of Access and possibly move the front end interface to a platform that meets the business needs better.

Cleanup and Migration of Access Database to Microsoft SQL Server - Part 1

Introduction

Everyone who does anything with databases, particularly Microsoft products, will end up at a job or client where Microsoft Access is being used for some mission critical database and it has not been properly designed or maintained. Don't get me wrong, I like Microsoft Access and find it quite powerful and stable to use when the database is designed correctly. But I also believe the database application reaches a certain size or complexity where it could benefit from migration to SQL server.

I am currently facing this problem. I inherited an accounting database application that was full of garbage and bugs. The data was suspect and everyone was afraid to touch it. Just to give you an idea of what we are dealing with, the database contains: 349 tables (no table relationships at all), 1569 queries, 427 macros, 312 reports, and over 50 switchboards. This is a database application for processing accounts payable, receivable, and payroll, not something used to decode DNA!

Now that you know the garbage we are dealing with you may not consider your cesspool of a database quite so bad. So on to the good stuff.

Part 1 - Where Do I Begin???

The first thing you need to do is document every object in the database and how in relates to every other. This sounds daunting, and it can be, but it is necessary for you to understand how each piece works before you can start to remove the garbage. If your database uses a switchboard and has a complicated one, I would also recommend generating a diagram that shows all of the menus that the users actually access.

I recommend using a product by FMS incorporated called the Total Access Analyzer. It analyzes a database and creates documentation on all of the objects. This documentation includes cross referencing objects with other objects that use them. The version I used had one problem. It didn't seem to check the switchboard items table when it was building cross references, so some items may not be cross referenced correctly. That may have been a problem of my own creation, or it may not, I am not sure. Anyway, using that product it got me 90% of the way documented. The other 10 % I did by hand by creating the diagram of the switchboard items that people actually used.

Now that I had the doumentation I could start to generate a list of potentially unused items. I started by listing all of the items that were in the diagram of the used switchboard items as "production" items. I then checked the Total Access Analyzer generated documentation and added any items that cross referenced the items from the switchboard. For example, if the switchboard opened a specific report, the Total Access Analyzer would also list the query that supplies the data to that report under the cross reference. If you start following all of the cross references you can find a chain of items that are all related to each other, but have no relation to the rest of the application anymore. Write down all of these items. Don't worry if you don't get them all the first time, you will make a more fine grained pass over the database as you analyze the business processes that use the application and how they work together.

Make a development copy of your database. Remove these items from your development copy and verify that your application still works as expected. Have one of the actual application users test your database. Repeat this process until you feel satisfied that you have removed everything that can currently be removed.

In the next part we will begin looking at business process analysis and making sure the application functionality fits the business process...not the other way around.

Please be patient

Please be patient as I recover my articles from my old site and place them here. My previous hosting provider glitched and deleted my site and they couldn't get it working again. As a result I cancelled my hosting and decided to give this a try instead. Hopefully I will have the temporary file deletion script back up in a couple of days, as well as the defprint.exe download.

Thanks,
Gary