+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: SQL server connection not working (/showthread.php?tid=90731)
Pages:
1
2
SQL server connection not working - falagar2k - 04-24-2024
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: 258The 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.
RE: SQL server connection not working - InsiteFX - 04-24-2024
Microsoft SQL Server via the SQLSRV driver (version 2012 and above only)
RE: SQL server connection not working - falagar2k - 04-24-2024
(04-24-2024, 03:50 AM)InsiteFX Wrote: 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.
RE: SQL server connection not working - kenjis - 04-24-2024
Try to set an empty string '' to port in the DB Config.
RE: SQL server connection not working - falagar2k - 04-25-2024
(04-24-2024, 05:44 PM)kenjis Wrote: 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: 87RE: SQL server connection not working - kenjis - 04-25-2024
What if you change Database Config file, and remove the config in .env?
RE: SQL server connection not working - falagar2k - 04-25-2024
(04-25-2024, 12:54 AM)kenjis Wrote: 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: 258RE: SQL server connection not working - JustJohnQ - 04-25-2024
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.
RE: SQL server connection not working - falagar2k - 04-25-2024
That's it.
Adding 'dbo' as 'schema' and not having 'port' is the winning combination.
Thank you so much!
RE: SQL server connection not working - kenjis - 04-25-2024
If empty string '' for port does not work, it is a bug.
Quote:Empty string '' for default port (or dynamic port with SQLSRV).
https://codeigniter4.github.io/CodeIgniter4/database/configuration.html#explanation-of-values
It would be helpful if you could send a PR for the bug fix.