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 which 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:\htdocs\index.php on line 3
Warning: mysql_connect(): The server requested authentication method unknown to the client in A:\htdocs\index.php on line 3
Warning: mysqli_connect(): The server requested authentication method unknown to the client [auth_gssapi_client] in A:\htdocs\index.php on line 4
Warning: mysqli_connect(): (HY000/2054): The server requested authentication method unknown to the client in A:\htdocs\index.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, which 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:\htdocs\index.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.
