GSSAPI-Auth 与 PHP 到 MariaDB (Windows) [英] GSSAPI-Auth with PHP to MariaDB (Windows)

问题描述

我目前正致力于在干净的 Windows 域环境中单点登录到数据库服务器.

由于目前没有选择 MySQL Enterprise 和 MSSQL 服务器，因此只能使用带有 GSSAPI 插件的 MariaDB.

这就是我目前拥有的:

<块引用>

服务器:

MYDOMAIN 的 Windows 7 x64 成员

MariaDB 10.2.14 x64

Apache2 2.4.33 x64 VC11 + mod_authnz_sspi + PHP 5.6.35 + mod_fcgid

MariaDB ODBC 驱动程序 x64

HeidiSQL(与 MariaDB 安装程序一起提供的 SQL 客户端)

FCGID-Config 如下所示(A:"不是我机器上的软盘驱动器，而是我实际放置 Apache2 的位置):

<IfModule !fcgid_module>LoadModule fcgid_module 模块/mod_fcgid.soFcgidIO超时 64FcgidConnectTimeout 16FcgidMaxRequestsPerProcess 200FcgidMaxProcesses 500FcgidMaxRequestLen 8131072FcgidInitialEnv PHPRC "A:/php"FcgidInitialEnv PHP_FCGI_MAX_REQUESTS 200FcgidWrapper "A:/php/php-cgi.exe" .php</IfModule>

Directory-Config 如下所示:

<目录${SRVROOT}/htdocs">选项索引 FollowSymLinksAllowOverride 无身份验证名称我的服务器"AuthType SSPISSPIAuth 开启SSPIAuthoritative OnSSPIOffer基本开SSPIOfferSSPI 开启<全部要求><要求任何>需要 sspi-group mygroup</RequireAny><无要求>要求用户匿名登录"</RequireNone></RequireAll>SetEnvIf 授权 "(.*)" HTTP_AUTHORIZATION=$1<文件匹配.php>AddHandler fcgid-script .php</文件匹配>选项 +ExecCGI</目录>

对 php.ini 没什么特别的，只是以 php.ini-development 为例(在 PHP zip 包中提供)并启用了 php_mysql、php_mysqli、php_mbstring 和 php_ldap 扩展.

我当前的 MariaDBs my.ini 如下所示:

[mysqld]数据目录=M:/数据端口=3306key_buffer_size = 384Mmax_allowed_pa​​cket = 512Mtable_open_cache = 512读取缓冲区大小 = 256Mread_rnd_buffer_size = 128M排序缓冲区大小 = 512Mquery_cache_size = 32Mjoin_buffer_size = 128M默认存储引擎 = INNODBinnodb 页面大小 = 65536innodb_buffer_pool_size = 4Ginnodb_write_io_threads = 8innodb_read_io_threads = 8innodb_thread_concurrency = 16innodb_log_buffer_size = 8Minnodb_log_file_size = 256Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120字符集服务器=utf8线程并发 = 8线程缓存大小 = 8[客户]端口 = 3306插件目录 = M:/lib/plugin[mysql转储]快的max_allowed_pa​​cket = 512M[mysql]没有自动重新散列安全更新

<块引用>

客户:

MYDOMAIN 的 Windows 10 x64 成员

Internet Explorer 11

MySQL 工作台 6.0 CE (6.0.7.11215)

设置完所有内容后，我使用 root 帐户将 VIA HeidiSQL(或任何 SQL 客户端)连接到 MariaDB-Server，并按照此处所述创建实际用户 https://mariadb.com/kb/en/library/authentication-plugin-gssapi/ :

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

现在在与 HeidiSQL 连接时或在为 MySQL Workbench 提供 auth_gssapi_client.dll 之后(并且当进程以 myuser 身份运行时)我连接到 MariaDB-Server 没有问题.

在尝试连接 PHP 之前，我查看了 phpinfo();使用 $_SERVER 变量 REMOTE_USER、PHP_AUTH_USER 和 PHP_AUTH_PW，一切看起来都和预期一样.这是我的 index.php:

正如您在某些时候看到的那样，我使用 MariaDB ODBC 连接器设置了一个 SystemDSN，并将其称为 MariaDB，指向 MariaDBs 插件目录.

这样我至少可以尝试让它工作，然后担心这些:

Warning: mysql_connect(): The server requested authentication method unknown to the client [auth_gssapi_client] in A:htdocsindex.php on line 3警告:mysql_connect():服务器在第 3 行的 A:htdocsindex.php 中请求客户端未知的身份验证方法警告:mysqli_connect():第 4 行 A:htdocsindex.php 中的服务器请求客户端未知的身份验证方法 [auth_gssapi_client]警告:mysqli_connect(): (HY000/2054): The server requested authentication method unknown to the client in A:htdocsindex.php on line 4

我找不到任何关于如何将 mysql 或 mysqli 的 php 扩展(或者我也用相同的结果 pdo_mysql 进行测试)指向 MariaDB 的 auth_gssapi_client.dll 插件的任何解决方案.因此，如果有人能指出我那里也将不胜感激.

但真正的问题，我也(很可能)在使用 mysql 和 mysqli 时遇到的问题是 odbc 连接的结果...:

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

进程本身作为本地系统运行，遗憾的是 GSSAPI 只对运行进程的用户感兴趣，而不对经过身份验证的用户感兴趣.

我正在互联网上搜索并尝试了大约一周的时间以使其正常工作，但仍然没有任何有效的结果(或关于下一步在这方面尝试什么的任何剩余想法).

所以，如果有人知道如何让这项工作发挥作用，那就太好了.

Greetz TheSkyGuy

解决方案

警告: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.

这篇关于GSSAPI-Auth 与 PHP 到 MariaDB (Windows)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！