ZF-6739: Connect to a database with trusted connection

Description

I am running with "{{mssql.secure_connections = On}}" in my "{{php.ini}}" file, so I don't have to assign a username and password whenever I make query.

This works fine with PHP native {{mssql_*}} functions and the ADOdb Framework, but sadly not with the Zend Framework. At least I haven't found out how to connect to a database without assigning a username and a password.

I have found a solution to make it possible, however it is dependant on another "patch" (http://framework.zend.com/issues/browse/ZF-3493) by Bart McLeod.

The changes are made to the function {{_dsn()}} which should be changed to:


    protected function _dsn()
    {
        // baseline of DSN parts
        $dsn = $this->_config;


        if (isset($dsn['port'])) {
            $seperator = ':';
            if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
                $seperator = ',';
            }
            $dsn['host'] .= $seperator . $dsn['port'];
            unset($dsn['port']);
        }

        // this driver supports multiple DSN prefixes
        // @see http://php.net/manual/en/…
        if (isset($dsn['pdoType'])) {
            switch (strtolower($dsn['pdoType'])) {
                case 'freetds':
                case 'sybase':
                    $this->_pdoType = 'sybase';
                    break;
                case 'mssql':
                    $this->_pdoType = 'mssql';
                    break;
                case 'odbc':
                    $this->_pdoType = 'odbc';
                    $dsn['DATABASE']    = $dsn['dbname'];
                    unset($dsn['dbname']);
                    $dsn['DRIVER']    = '{SQL Server}';
                    $dsn['SERVER']    = $dsn['host'];
                    if (!$dsn['username'] && !$dsn['password']) {
                        $dsn['Trusted_Connection'] = "yes";
                    }
                    unset($dsn['host']);
                    break;
                case 'dblib':
                default:
                    $this->_pdoType = 'dblib';
                    break;
            }
            unset($dsn['pdoType']);
        }

        // don't pass the username and password in the DSN
        unset($dsn['username']);
        unset($dsn['password']);
        unset($dsn['driver_options']);

        // use all remaining parts in the DSN
        foreach ($dsn as $key => $val) {
            $dsn[$key] = "$key=$val";
        }

        $dsn = $this->_pdoType . ':' . implode(';', $dsn);
        return $dsn;
    }

I would've loved to not being forced to use ODBC, but couldn't couldn't figure out how to make a trusted connection with PDO_MSSQL :(.

Comments

Crossposted from ZF-3493 since that issue apparently got closed prematurely IMHO.

Please someone look into this and let me know if I can help in any way.


I just went through updating ZendFramework from 0.9.3 to 1.10.0 and while it should've been a simple overwrite of all the files then it took a bit longer than that.

The reason... I had to manually add the attached mssql-odbc-patch as well as the changes from ZF-6739.

I can understand that there is the right way of implementing stuff as well as the wrong way, but reading that this issue might get changed to "wont-fix" (as well as ZF-905) and the lack of response in issue ZF-6739 (which is depending on this fix) then I fear that I have to manually make changes in all future releases of Zend Framework.

This fix doesn't break any functionality and just adds an extra option/setting, so why is it, that it can't be implemented?

Do we really have to install Microsofts SQLserver driver and if so, does that then solve the problems in this issue and the related issues (text fields above 4096 bytes, trusted connection and multiple queries).