1

Following setup: I have a Linux (Debian) machine as a webserver (apache) and a MS SQL Server on Windows. Furthermore I am not able to get a SQL user for this, only authentication through AD/Windows Auth.

I am using the Microsoft ODBC Driver and sqlsrv extension I installed following the instructions from Microsoft.

I run kinit with principal name and password successfully. Now Windows-Auth based connection to the MSSQL Server works perfectly when using sqlcmd or even when using PHP in interactive mode (php -a) using sqlsrv_connect, but when I try to run a simple example connection from a php-file through Apache, it doesnt't work. I get an error message like this:

Array ( [0] => Array ( [0] => HY000 [SQLSTATE] => HY000 [1] => 851968 [code] => 851968 [2] => [Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_33) [message] => [Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_33) ) [1] => Array ( [0] => HY000 [SQLSTATE] => HY000 [1] => 851968 [code] => 851968 [2] => [Microsoft][ODBC Driver 17 for SQL Server]Cannot generate SSPI context [message] => [Microsoft][ODBC Driver 17 for SQL Server]Cannot generate SSPI context ) )

I know the the cache appended _XX is standing for the UID, so thinking that this might be the problem I already experimented with different user requesting the ticket and whatnot, but I couldn't get it working.

I also tried using unixODBC and defining a DSN.It is working when testing with isql and using odbc_connect in php interactive mode (also with python using pyodbc), but not from a php page.

Does anyone have an idea on what i'm missing? It might be pretty obvious, but I'm fairly new to this topic and there is not too much to find regarding access from a linux machine to a Windows based MS SQL server without an extra SQL User. Thanks for your help in advance!

2 Answers2

1

Found the answer myself, could have come up with this earlier, but I'm going to leave this here in case someone else stumbles across this issue:

I already thought it had something to to with access rights/permissions, as it was basically working from everywhere but Apache, but I couldn't figure out why, until I found out that this is caused by systemd:

Systemd has a feature preventing services from accessing /tmp called SecureTmp. Deactivating this feature for Apache as described here for testing solved the issue. I will now see how I can do this differently as I do not want to leave this feature disabled. Hope this is helpful for someone :-)

1

I had a similar issue, and it was also working when testing with sqlcmd and php -a. However, my error message didn't include the default cache file as shown above:

Array
(
    [0] => Array
        (
            [0] => HY000
            [SQLSTATE] => HY000
            [1] => 851968
            [code] => 851968
            [2] => [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available
            [message] => [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available
        )

    [1] => Array
        (
            [0] => HY000
            [SQLSTATE] => HY000
            [1] => 851968
            [code] => 851968
            [2] => [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Cannot generate SSPI context
            [message] => [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Cannot generate SSPI context
        )
)

Solution was to add an environment variable that sets KRB5CCNAME to the path of the credential cache. If using PHP-FPM, edit this accordingly and add to your pool config file:

env[KRB5CCNAME] = FILE:/tmp/krb5cc_UID

Alternatively, set the default_ccache_name property in /etc/krb5.conf:

[libdefaults]
    default_ccache_name = FILE:/tmp/krb5cc_%{uid}

Reference

Mike
  • 101
  • 1
  • 4