GSSAPI-Auth 与 PHP 到 MariaDB (Windows)

警告:odbc_connect():SQL 错误:[ma-3.0.3]GSSAPI 名称不匹配，请求myuser@MYDOMAIN"，实际名称myserver$@MYDOMAIN"，SQL第 5 行 A:htdocsindex.php 中 SQLConnect 中的状态 28000

这意味着您的应用程序(apache?)连接到机器帐户，也许它以 NetworkService 用户身份运行，或者类似的东西.如果您可以在域用户myuser"凭据下运行它，那么它将连接.

或者，您可以创建与机器帐户对应的 MariaDB 用户帐户，并将其用于连接.

I'm currently working on Single Sign On to a Database-Server in a clean Windows Domain Environment.

And as MySQL Enterprise and MSSQL servers are currently no option, it came down to MariaDB with the GSSAPI plugin.

That's what I currently have:

Server:

Windows 7 x64 member of MYDOMAIN

MariaDB 10.2.14 x64

Apache2 2.4.33 x64 VC11 + mod_authnz_sspi + PHP 5.6.35 + mod_fcgid

MariaDB ODBC Driver x64

HeidiSQL (SQL-Client delivered with the MariaDB installer)

FCGID-Config looks as follows ("A:" is not a floppy drive on my machine, but where I actually put my Apache2):

<IfModule !fcgid_module>
LoadModule fcgid_module modules/mod_fcgid.so

FcgidIOTimeout 64
FcgidConnectTimeout 16
FcgidMaxRequestsPerProcess 200
FcgidMaxProcesses 500
FcgidMaxRequestLen 8131072

FcgidInitialEnv PHPRC "A:/php"
FcgidInitialEnv PHP_FCGI_MAX_REQUESTS 200
FcgidWrapper "A:/php/php-cgi.exe" .php
</IfModule>

Directory-Config looks as follows:

<Directory "${SRVROOT}/htdocs">
Options Indexes FollowSymLinks
AllowOverride None

AuthName "MYSERVER"
AuthType SSPI
SSPIAuth On
SSPIAuthoritative On
SSPIOfferBasic On
SSPIOfferSSPI On

<RequireAll>
    <RequireAny>
        Require sspi-group mygroup
    </RequireAny>
    <RequireNone>
        Require user "ANONYMOUS LOGON"
    </RequireNone>
</RequireAll>

SetEnvIf Authorization "(.*)" HTTP_AUTHORIZATION=$1

<FilesMatch .php>
    AddHandler fcgid-script .php
</FilesMatch>

Options +ExecCGI
</Directory>

Nothing fancy done to the php.ini, just took the example php.ini-development (delivered in PHP zip package) and enabled the php_mysql, php_mysqli, php_mbstring and php_ldap extensions.

My current MariaDBs my.ini looks as follows:

[mysqld]
datadir=M:/data
port=3306

key_buffer_size = 384M
max_allowed_packet = 512M
table_open_cache = 512

read_buffer_size = 256M
read_rnd_buffer_size = 128M
sort_buffer_size = 512M
query_cache_size = 32M
join_buffer_size = 128M

default-storage-engine = INNODB
innodb-page-size = 65536
innodb_buffer_pool_size = 4G
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

character-set-server=utf8

thread_concurrency = 8
thread_cache_size = 8

[client]
port = 3306
plugin-dir = M:/lib/plugin

[mysqldump]
quick
max_allowed_packet = 512M

[mysql]
no-auto-rehash
safe-updates

Client:

Windows 10 x64 member of MYDOMAIN

Internet Explorer 11

MySQL Workbench 6.0 CE (6.0.7.11215)

After setting everything up I connect VIA HeidiSQL (or well any SQL-Client) to the MariaDB-Server with the root account and create the actual user as described here https://mariadb.com/kb/en/library/authentication-plugin-gssapi/ :

CREATE USER myuser IDENTIFIED VIA gssapi AS 'myuser@MYDOMAIN';

Now while connecting with HeidiSQL or after providing MySQL Workbench with the auth_gssapi_client.dll (and while the process is running as myuser) I have no problem connecting to the MariaDB-Server.

Before now trying to connect with PHP I took a look at phpinfo(); where everything seems as much as expected, with the $_SERVER vars REMOTE_USER, PHP_AUTH_USER and PHP_AUTH_PW. Here's my index.php:

<?php
    //phpinfo(); exit;
    $m = mysql_connect("localhost", $_SERVER["PHP_AUTH_USER"]);
    $mi = mysqli_connect("localhost", $_SERVER["PHP_AUTH_USER"]);
    $c = odbc_connect("MariaDB", $_SERVER["PHP_AUTH_USER"], "");
?>

As you may see at some point I set up a SystemDSN with the MariaDB ODBC Connector and called it MariaDB wich is pointed at MariaDBs Plugin dir.

With that I could at least try to get it working at all and after that worry about these:

Warning: mysql_connect(): The server requested authentication method unknown to the client [auth_gssapi_client] in A:htdocsindex.php on line 3
Warning: mysql_connect(): The server requested authentication method unknown to the client in A:htdocsindex.php on line 3
Warning: mysqli_connect(): The server requested authentication method unknown to the client [auth_gssapi_client] in A:htdocsindex.php on line 4
Warning: mysqli_connect(): (HY000/2054): The server requested authentication method unknown to the client in A:htdocsindex.php on line 4

I could not find any solution on how to point the php extensions for mysql or mysqli (or as I also tested with the same result pdo_mysql) to the auth_gssapi_client.dll plugin from MariaDB. So if anyone could point me there that would also been appreciated.

But the real problem, wich I would also (very likely) run into with the mysql and mysqli, is the result from the odbc connect ...:

Warning: odbc_connect(): SQL error: [ma-3.0.3]GSSAPI name mismatch, requested 'myuser@MYDOMAIN', actual name 'myserver$@MYDOMAIN', SQL state 28000 in SQLConnect in A:htdocsindex.php on line 5

The process itself is ofc running as local system, sadly GSSAPI only is interested in the user running the process and not in the authenticated user.

I'm searching the internet and trying stuff for about a week now to get that working, though still without any functioning result (or any remaining idea on what to try out next in that regard).

So if anyone has an idea on how to get that working that would been great.

Greetz TheSkyGuy

Warning: odbc_connect(): SQL error: [ma-3.0.3]GSSAPI name mismatch, requested 'myuser@MYDOMAIN', actual name 'myserver$@MYDOMAIN', SQL state 28000 in SQLConnect in A:htdocsindex.php on line 5

this means that your application (apache?) connects with the machine account, perhaps it runs as NetworkService user, or something like that. If you can run it under domain user "myuser" credentials, then it will connect.

Alternatively, you can create MariaDB user account corresponding to the machine account, and use that for connection.