[Dovecot] SQL Server

[Michael Orlitzky]

On 05/21/10 18:00, Bryan Vyhmeister wrote:
> On Fri, May 21, 2010 at 4:10 PM, Jerry<dovecot.user at 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 at 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).