SQL server connection not working

Trying to connect my CodeIgniter 4.5.1 project to a SQL Server (14.0) database. Running on Windows with XAMPP, PHP version 8.2.

I downloaded the appropriate DLLs from Microsoft, put them in my PHP extensions folder and enabled the extensions

In my .env file I entered the database information:

PHP Code:

database.default.hostname = "MYSERVER\TEST"
database.default.database = 'mydb'
database.default.username = 'myuser'
database.default.password = 'mypassword'
database.default.DBDriver = 'sqlsrv' 


I get the following error:

Code:

Unable to connect to the database.
Main connection [sqlsrv]: [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Der Wartevorgang wurde abgebrochen.
SQLSTATE: 08001, code: 258

The server is running and the credentials are correct (I triple-checked).

If I connect to the DB the "hard" way (using the same credentials), it works just fine (just dropped code below into my controller):

PHP Code:

        $serverName = "MYSERVER\TEST"; 
        $uid = "myuser";  
        $pwd = "mypassword";  
        $databaseName = "mydb"; 
        
        $connectionInfo = array( "UID"=>$uid,
                                "PWD"=>$pwd,
                                "Database"=>$databaseName); 
        
        $conn = sqlsrv_connect( $serverName, $connectionInfo);  
        
        $tsql = "SELECT id, name FROM users";

        $stmt = sqlsrv_query( $conn, $tsql);  
        if ( $stmt )  
        {  
            echo "Statement executed.<br>\n";  
        }  
        else  
        {  
            echo "Error in statement execution.\n";  
            die( print_r( sqlsrv_errors(), true));  
        }  
              
        while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_NUMERIC))  
        {  
            echo "Col1: ".$row[0]."\n";  
            echo "Col2: ".$row[1]."\n";
            echo "-----------------<br>\n";  
        }  
        
        sqlsrv_free_stmt( $stmt);  
        sqlsrv_close( $conn);  


I am probably just doing something incredibly stupid, but I have been staring at this for hours and I am at my wit's end. Anyone have an idea? Thank you for reading.

Microsoft SQL Server via the SQLSRV driver (version 2012 and above only)

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

Microsoft SQL Server via the SQLSRV driver (version 2012 and above only)

According to this overview version 14.0 is MS SQL Server 2017, so it's way above 2012.

Try to set an empty string '' to port in the DB Config.

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

Try to set an empty string '' to port in the DB Config.

Thank you for your reply.

However, the suggestion does not seem to work.

I added the following line in my .env file:

PHP Code:

database.default.port = '' 


and now get this error message:

Code:

Unable to connect to the database.
Main connection [sqlsrv]: [Microsoft][ODBC Driver 17 for SQL Server]SQL Server Network Interfaces: Connection string is not valid [87]. SQLSTATE: 08001, code: 87

What if you change Database Config file, and remove the config in .env?

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

What if you change Database Config file, and remove the config in .env?

Thanks again.

I commented out all database related lines in .env.

Removed the default config in app\Config\Database.php and replaced it with this:

PHP Code:

/**
    * Sample database connection for SQLSRV.
    *
    * @var array<string, mixed>
    */
    public array $default = [
        'DSN'        => '',
        'hostname'   => 'MYSERVER\TEST',
        'username'   => 'myuser',
        'password'   => 'mypassword',
        'database'   => 'mydb',
        'schema'     => 'dbo',
        'DBDriver'   => 'SQLSRV',
        'DBPrefix'   => '',
        'pConnect'   => false,
        'DBDebug'    => true,
        'charset'    => 'utf8',
        'swapPre'    => '',
        'encrypt'    => false,
        'failover'   => [],
        'port'       => 1433,
        'dateFormat' => [
            'date'     => 'Y-m-d',
            'datetime' => 'Y-m-d H:i:s',
            'time'     => 'H:i:s',
        ],
    ]; 


I thought that maybe the 'schema' setting was missing, but I still get the same error. Changing 'port' to '' does not change the error message.

In both cases I get:

Code:

Unable to connect to the database.
Main connection [sqlsrv]: [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Der Wartevorgang wurde abgebrochen.
SQLSTATE: 08001, code: 258

This works for me in CI 4.4.6 with MS SQL Server Express 2019:

Code:

public array $sqlsrv = [
'DSN'        => '',
'hostname'  => '127.0.0.1\sqlexpress',
'username'  => 'user',
'password'  => 'pass',
'database'  => 'table',
'schema'    => 'dbo',
'DBDriver'  => 'SQLSRV',
'DBPrefix'  => '',
'pConnect'  => false,
'DBDebug'    => true,
'charset'    => 'utf8',
'swapPre'    => '',
'encrypt'    => false,
'failover'  => [],
'dateFormat' => [
'date'    => 'Y-m-d',
'datetime' => 'Y-m-d H:i:s',
'time'    => 'H:i:s',
],
];

Maybe this helps you... I had to remove the port and add dbo as schema.

That's it.

Adding 'dbo' as 'schema' and not having 'port' is the winning combination.

Thank you so much!

If empty string '' for port does not work, it is a bug.

Empty string '' for default port (or dynamic port with SQLSRV).
https://codeigniter4.github.io/CodeIgnit...-of-values

It would be helpful if you could send a PR for the bug fix.

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper