Cannot connect to TDS endpoint of dataverse

[geoffreylagaisse]

Please check the FAQ (frequently-asked questions) first. If you have other questions or something to report, please address the following (skipping questions might delay our responses):

PHP version
8.1 PHP SQLSRV or PDO_SQLSRV version
latest Microsoft ODBC Driver version
18 SQL Server version
SQL Azure version 12.0.2000.8 Client operating system
Debian 8.1-bullseye Table schema

Problem description
Cannot connect to TDS endpoint of dataverse.

Expected behavior and actual behavior
I would expect errors to be logged in Sqlsrv_error, but nothing is logged. Connection simply does not succeed in srv or pdo This should be supported I guess ... https://learn.microsoft.com/en-us/power-apps/developer/data-platform/dataverse-sql-query

Repro code or steps to reproduce

<?php

$adServer = '***.crm4.dynamics.com';
$adDatabase = '***';
$adSPClientId = '***';
$adSPClientSecret = '***';

$conn = false;
$connectionInfo = array("Database"=>$adDatabase, 
                        "Authentication"=>"ActiveDirectoryServicePrincipal",
                        'CharacterSet' => 'UTF-8',
                        'ConnectionPooling' => 'False',  
                        'LoginTimeout' => 30,
                        "UID"=>$adSPClientId,
                        "PWD"=>$adSPClientSecret);

sqlsrv_configure("LogSeverity", SQLSRV_LOG_SEVERITY_ALL);
sqlsrv_configure("LogSubsystems", SQLSRV_LOG_SYSTEM_ALL);

$conn = sqlsrv_connect($adServer, $connectionInfo);
if ($conn === false) {
    echo "Could not connect using Azure AD Service Principal." . PHP_EOL;
    $errors = sqlsrv_errors();
    print_r(sqlsrv_errors());
}
else {
    sqlsrv_close($conn);
}
?>

[v-makouz]

Can you enable ODBC Trace and see if that logs anything? (https://www.easysoft.com/support/kb/kb00945.html)

[geoffreylagaisse]

Hi Below the result

[ODBC][400][1664271695.605828][SQLSetConnectAttr.c][399]
        Entry:
            Connection = 0x55deea198d60
            Attribute = 1400
            Value = 0x2
            StrLen = -4
[ODBC][400][1664271695.605893][SQLSetConnectAttr.c][691]
        Exit:[SQL_SUCCESS]
[ODBC][400][1664271695.605927][SQLDriverConnectW.c][290]
        Entry:
            Connection = 0x55deea198d60
            Window Hdl = (nil)
            Str In = [Server={xxx.crm4.dynamics.com,1433};UID={xxx};PWD={xxx};D...][length = 288]
            Str Out = (nil)
            Str Out Max = 0
            Str Out Ptr = 0x7ffc1b9f213a
            Completion = 0
        UNICODE Using encoding ASCII 'ANSI_X3.4-1968' and UNICODE 'UTF-16LE'

[ODBC][400][1664271695.739659][SQLGetEnvAttr.c][157]
        Entry:
            Environment = 0x55deea1c91e0
            Attribute = 65002
            Value = 0x7ffc1b9ef150
            Buffer Len = 128
            StrLen = 0x7ffc1b9ef0ec
[ODBC][400][1664271695.739711][SQLGetEnvAttr.c][273]
        Exit:[SQL_SUCCESS]
[ODBC][400][1664271695.739729][SQLFreeHandle.c][220]
        Entry:
            Handle Type = 1
            Input Handle = 0x55deea1c91e0
[ODBC][400][1664271695.739801][SQLDriverConnectW.c][701]
        Exit:[SQL_ERROR]
[ODBC][400][1664271695.739829][SQLGetDiagRecW.c][535]
        Entry:
            Connection = 0x55deea198d60
            Rec Number = 1
            SQLState = 0x7ffc1b9f17b4
            Native = 0x7ffbd5a014f0
            Message Text = 0x7ffc1b9f17c0
            Buffer Length = 1025
            Text Len Ptr = 0x7ffc1b9f179c
[ODBC][400][1664271695.739849][SQLGetDiagRecW.c][596]
        Exit:[SQL_NO_DATA]
[ODBC][400][1664271695.739996][SQLFreeHandle.c][290]
        Entry:
            Handle Type = 2
            Input Handle = 0x55deea198d60
[ODBC][400][1664271695.740020][SQLFreeHandle.c][339]
        Exit:[SQL_SUCCESS]
[ODBC][400][1664271705.491228][SQLFreeHandle.c][220]
        Entry:
            Handle Type = 1
            Input Handle = 0x55deea1657f0
[ODBC][400][1664271705.491329][SQLFreeHandle.c][220]
        Entry:
            Handle Type = 1
            Input Handle = 0x55deea167b90
[ODBC][400][1664271705.491574][SQLFreeHandle.c][220]
        Entry:
            Handle Type = 1
            Input Handle = 0x55deea15f840
[ODBC][400][1664271705.491601][SQLFreeHandle.c][220]
        Entry:
            Handle Type = 1
            Input Handle = 0x55deea15ff80

[v-makouz]

It looks like SQLDriverConnect fails, there is no error data set for some reason. Does this happen with other authentication methods or databases?

Also do you think it's possible to get Wireshark logs to see what the server returns when the login fails?

[v-makouz]

Here are some more things to try: Check which version of unixODBC are you have, using odbcinst -j. If it's below 2.3.5 I recommend upgrading as there were some logging bugs fixed in that version. Also you can try connecting without PHP driver using isql -v -k "<connection string>" to see if it behaves differently, this might help determine if PHP driver itself is causing the issue.

[absci]

Close due to inactivity