Here is the answer for setting up RoundCube Mail and MSSQL Server

[JabbaTek]

For all of those who have been having issues with MSSQL and RoundCube, I give to you the answer.

I have RoundCube (with HMail) running in the following environment.  It works great and is fast.

Windows Server 2008 Standard
IIS 7
PHP 5.x.x
Beta version on RoundCube (latest version)
Miscrosft SQL Server 2005

First you will need to have installed MSSQL.  Most will be running MSDE 2000 or SQL Express 2005.  Both are the free versions from Microsoft.

Second, create a database such as RoundCube.  IF you have SQL Express 2005, then you will need to download (if you haven't already) SQL Management Studio Express.  

Third, open the mssql.initial file found in the \SQL folder where RoundCube Mail is installed.  Copy the contents of that file into a new query window in Management Studio.  Make sure you selected your new database before opening a new query window to set the contect to that database.  Execute the statements that you just copied in.

Fourth, you will need to edit the db.inc.php file in the Config folder.  Find the line that starts with $rcmail_config['db_dsnw'].  I will provide 2 examples of how to write the line.  The examples assume the following:

Machine name (of host) = MAILSERVER
Database name = RoundCube
Username = rcmail
Password = webmail

Example 1:
'mssql://rcmail:webmail@mailserver/RoundCube'

Example 2 (assumes you have an instance of MSSQL called SQLEXPRESS):
'mssql://rcmail:webmail@mailserver\\SQLEXPRESS/RoundCube'

if you installed SQL Express 2005, you will most likely have the instance.

Last but not least, there is a missing function in the \program\lib\MDB2\Driver\mssql.php file.  The function is SetCharSet.  If you do not add the missing function, you will receive an error message stating that the SetCharSet method is not implemented.  I have attached a copy of my mssql.php file for convenience, but here are the missing lines in that file.

    function setCharset($charset, $connection = null)
    {
       
      return true;

    }

Once this is all done, it should work just fine (at least the database portion).

If you have questions or run into trouble getting it to work please reply and I will be happy to try and help.

[Rilla]

JABBATEK YOU ARE AN 0:) !!!  I LOVE YOU!!!

I was having issues with my server and your solution above works perfectly

I have been searching everywhere for this solution and you solved it!!

THANK YOU!

-Rilla

[JabbaTek]

In addition to all that I have explained, you will need to make sure that the username you plan to use to connect to the RoundCube Mail database has read/write permissions to that database.

[Rilla]

I got these settings to work by using the default config files.  I copied the config files into a backup file and edited the deafult config files.  I did not use the roundcube configurator.

JabbaTek - How did you manage the config files for your server?

-Rob

[JabbaTek]

The "configurator" or the web page admin interface does not provide an option for MSSQL.  So it does not know how to configure it.  I did the same as you, I had to manually edit the db.inc.php file.  

If figured out the how to format the string by looking at the parseDSN function.

I got the db connection to work, then I got the method not implemented error.  I figured out that the mssql.php, along with the others like mysql.php, override a base class of mdb2.php.  That's where I discovered that the setCharSet function was missing from the mssql.php file.  SQL server doesn't support the commands it was trying to perform so I removed the code and had the function just return true.  It worked and the error went away.

[JabbaTek]

If you get an error stating that Inerting a null value caused a constraint violation, you will need to open the affected table, usually the Users table and set the columns to allow null.  If you need help with this on MSSQL, please let me know.

If you do not want to ask questions in the thread, you can send me a private message.

[JabbaTek]

It also appears that the scripts to create the tables for RC Mail in an MSSQL database have an error.  The piece that creates the Identities table leaves off a column. It does not add the "html_signature" column (w/o quotes).  You will need to add this column manually to your Identities table so you can create an identity so you can send messages.

Below is the information for creating the column in the IDentities table.

Name: html_signature
Data Type: bit
Allow Null: False or No
Default Value: 0

[JabbaTek]

It turns out that RC Mail will not work with an instance of mssql other than the default machine name.  So let's say your server name is MAILSERVER.  With SQL 2005 and up, if you install the express (free) versions, they create an instance called SLEXPRESS automatically.  This means that the host (server) that you would refer to is actually MAILSERVER\SQLEXPRESS.  

The parseDsn function is not setup to handle this scenario.  I will be working on fixing the parseDsn function and submitting it for download and for inclusion (hopefully) in future releases.  

I will post the fix along with a complete guide to setting up with mssql, once I have the function fixed.  The guide will include all of the kinks previously mentioned.

[Bonx]

Hmmm..

First some details:

Windows SRV 2008
IIS 7
PHP 5.2.6
hMail
Roundcube 0.2 Beta (latest DL)
SQL Server 2005 std. (installed with instancename 'SQLSERVER')
Database is uprunning (can login via webinterface and see the full DB with full tables as in mssql.initial)
I have defined the user as DBO

Connection string:
$rcmail_config = 'mssql://rcwebmail:webmail@localhost\\SQLServer/RoundCube';

Tried with and without a defined DSN - gave me same result

The result is, that roundcube cant connect to the database.

Can some tell me whats missing or what i'm doing wrong ?

/ Bronx

[JabbaTek]

MSSQL Server does not use "localhost" as an identifier.  You have to use either "(Local)" with the parenthesis but not the quotes, or you need to use the machine name in place of localhost.  If your machine name is SERVER then either of the following examples should work.

'mssql://rcwebmail:webmail@(local)\\SQLServer/RoundCube';

'mssql://rcwebmail:webmail@server\\SQLServer/RoundCube';

If neither of the above examples work, let me know.  There may be an issue with using named instances with RC Mail which I may have figured out how to fix.  

Also, make sure that mssql appears in the list of extensions when you run phpinfo().

[Bonx]

I haved tried a lot.

- MSSQL is enabled in PHP, see below:
mssql
MSSQL Support enabled
Active Persistent Links  0  
Active Links  0  
Library version  7.0

- I have tried several things in the connection string
    - Localhost
    - IP Address
    - Machine name

Just tried with (local), still the same result.

Is it possible to connect via TCP/IP instead ?

let me know if I have to try anything else

/ Bonx

[lee.rekab@gmail.com]

Hi,

I am trying to get Roundcube to work with SQL Server 2008. Is this currently supported, or planned to be soon?

Lee

[vhtnyc]

Hi there,

I tried to run the query mssql_initial.sql on SQL2008Express and got this error.

I followed all your steps but no luck.
When I went to the website, it just gave me blank screen
Hmail is running fine.

O/S: Windows Server 2003 Std R2
SQL2008Express

mssql://roundcube:mypass@hmailserver\sqlexpress/roundcube
or
mssql://roundcube:mypass@hmailserver\\sqlexpress/roundcube
or
mssql://sa:sa_pass@hmailserver\sqlexpress/roundcube

Please help !

Thank you.

[vhtnyc]

I forgot to attach the error when I ran the mssql.initial.sql query on SQL2008Express

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ','.

Hope somebody can point me the right direction.

Thanks.

[fatlip]

I have followed these instructions and I know I am really close to having this working!!

I am running 2K3 Enterprise with IIS6.
PHP 5.2.9-1 using fastcgi, mssql enabled in the config files
MSSQL Express 2005
Roundcube 0.2.1

I created the database 'roundcube' in SQL Express, opened and ran the 'mssql.initial.sql' file. It generated the following error, although it looks like it created all the tables.

Code Select Expand

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ','.

I created a user rcmail with a password of rcwebmail. Edited the db.inc.php to look like this:

Code Select Expand

$rcmail_config['db_dsnw'] = 'mssql://rcmail:rcwebmail@HMAIL\\SQLEXPRESS/roundcube';

I placed the extra code in the mssql.php file as shown below:

class MDB2_Driver_mssql extends MDB2_Driver_Common
{
    // {{{ properties

    var $string_quoting = array('start' => "'", 'end' => "'", 'escape' => "'", 'escape_pattern' => false);

    var $identifier_quoting = array('start' => '[', 'end' => ']', 'escape' => ']');

function setCharset($charset, $connection = null)
{

return true;

}

    // }}}
    // {{{ constructor


I enable trace debugging and when I attempt to load the webmail page, this is what I get.

Code Select Expand

MDB2 Error: connect failed (-24): _doConnect: [Error message: unable to establish a connection]


Any ideas?