Bryan Vyhmeister put forth on 5/21/2010 5:00 PM:

On Fri, May 21, 2010 at 4:10 PM, Jerry <dovecot.user@seibercom.net> wrote:

...

If you can access the Microsoft SQL Server from the machine hosting Dovecot, there should be no problem at all. I have used Microsoft's SQL sever for for several projects and it is an extremely fast and robust piece of software.

Perhaps you could post a clearer picture of exactly what you are attempting to accomplish.

Sorry. I should have been more specific. I want to use SQL Server for the userdb and password database. I looked through the wiki and did a number of searches but could not find an answer. If I were to use sqlite, mysql, or pgsql, these would be listed under driver = in the dovecot-sql.conf (or whatever you want to name it). How would you go about using SQL Server? There is no sql-server driver as far as I know. I have and am currently using dovecot from passwd files and from sqlite databases so I am pretty familiar with how everything works. I am just not sure how to go about this for SQL Server. Thank you.

I think you're SOL Bryan until someone adds mssql driver support to Dovecot. If you are a C veteran and know mssql at a development level, you could possibly grab the source for one of the other sql drivers and rewrite it for mssql duty. The current sql driver source files are:

driver-mysql.c driver-pgsql.c driver-sqlite.c

Source files for stable Dovecot are available here: http://www.dovecot.org/releases/1.2/dovecot-1.2.11.tar.gz

Good luck.

-- Stan

Pascal Volk put forth on 5/22/2010 2:36 AM:

In the meantime the FreeTDS <http://www.freetds.org/> libraries may be useful: "FreeTDS is a set of libraries for Unix and Linux that allows your programs to natively talk to Microsoft SQL Server and Sybase databases."

I should have looked around before posting. FreeTDS might just save Bryan a little development time. :)

Have you ever used FreeTDS Pascal?

-- Stan

On 05/21/10 18:00, Bryan Vyhmeister wrote:

On Fri, May 21, 2010 at 4:10 PM, Jerry<dovecot.user@seibercom.net> wrote:

...

If you can access the Microsoft SQL Server from the machine hosting Dovecot, there should be no problem at all. I have used Microsoft's SQL sever for for several projects and it is an extremely fast and robust piece of software.

Perhaps you could post a clearer picture of exactly what you are attempting to accomplish.

Sorry. I should have been more specific. I want to use SQL Server for the userdb and password database. I looked through the wiki and did a number of searches but could not find an answer. If I were to use sqlite, mysql, or pgsql, these would be listed under driver = in the dovecot-sql.conf (or whatever you want to name it). How would you go about using SQL Server? There is no sql-server driver as far as I know. I have and am currently using dovecot from passwd files and from sqlite databases so I am pretty familiar with how everything works. I am just not sure how to go about this for SQL Server. Thank you.

Bryan

You can specify arbitrary .NET DLL functions to be executed from triggers in Microsoft SQL Server, and there are .NET libraries to talk to Postgres, so just do it the other way around: run Dovecot off of a local Postgres install, and feed it data from Microsoft SQL every time something changes.

Here's an example assembly.

/* Begin C# */ using System; using System.Net;

public class StoredProcedures {

public static void InsertPostgres(string username, string password) { /* This part is your job. */ }

public static void UpdatePostgres(string username, string password) { /* This part is your job. */ }

public static void DeletePostgres(string username, string password) { /* This part is your job. */ }

} /* End C# */

Now,

1. Compile the assembly to a DLL. Call it e.g. PostgresStoredProcedures.

2. Enable CLR code to run within the database:

   EXEC sp_configure 'clr enabled', 1; RECONFIGURE;

3. Make your database trustworthy. This is necessary because it will be accessing external resources.

   ALTER DATABASE <exampledb> SET TRUSTWORTHY ON

4. Add the assembly to your database. This will fail if you haven't set TRUSTWORTHY ON in step 3. Note that this should be done *within* the database, not to SQL server in general (i.e. the master database).

   CREATE Assembly PostgresStoredProcedures FROM '<dll_path>' WITH PERMISSION_SET = EXTERNAL_ACCESS

5. Create the stored procedures within the database. Their signatures should match those of the class methods.

   CREATE PROCEDURE InsertPostgres(@username nvarchar(256), @password nvarchar(256)) AS EXTERNAL NAME PostgresStoredProcedures.StoredProcedures.InsertPostgres

   CREATE PROCEDURE UpdatePostgres(@username nvarchar(256), @password nvarchar(256)) AS EXTERNAL NAME PostgresStoredProcedures.StoredProcedures.UpdatePostgres

   CREATE PROCEDURE DeletePostgres(@username nvarchar(256), @password nvarchar(256)) AS EXTERNAL NAME PostgresStoredProcedures.StoredProcedures.DeletePostgres

6. You can now execute the stored procedures from within SQL:

   exec InsertPostgres 'user@example.com' 'password'

At this point, you just need to define three triggers on your user database -- one for each of insert, update, and delete -- that will execute those stored procedures. This is left as an exercise (but really is trivial once you've made it that far).

I appreciate all the responses. I will look into the different options. Thanks again.

Bryan