This section describes how to install the server-side Windows authentication plugin. For general information about installing plugins, see Section 7.6.1, “Installing and Uninstalling Plugins”.

To be usable by the server, the plugin library file must be located in the MySQL plugin directory (the directory named by the plugin_dir system variable). If necessary, configure the plugin directory location by setting the value of plugin_dir at server startup.

To load the plugin at server startup, use the --plugin-load-add option to name the library file that contains it. With this plugin-loading method, the option must be given each time the server starts. For example, put these lines in the server my.cnf file:

[mysqld]
plugin-load-add=authentication_windows.dll

After modifying my.cnf, restart the server to cause the new settings to take effect.

Alternatively, to load the plugin at runtime, use this statement:

INSTALL PLUGIN authentication_windows SONAME 'authentication_windows.dll';

INSTALL PLUGIN loads the plugin immediately, and also registers it in the mysql.plugins system table to cause the server to load it for each subsequent normal startup without the need for --plugin-load-add.

To verify plugin installation, examine the Information Schema PLUGINS table or use the SHOW PLUGINS statement (see Section 7.6.2, “Obtaining Server Plugin Information”). For example:

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
       FROM INFORMATION_SCHEMA.PLUGINS
       WHERE PLUGIN_NAME LIKE '%windows%';
+------------------------+---------------+
| PLUGIN_NAME            | PLUGIN_STATUS |
+------------------------+---------------+
| authentication_windows | ACTIVE        |
+------------------------+---------------+

If the plugin fails to initialize, check the server error log for diagnostic messages.

To associate MySQL accounts with the Windows authentication plugin, see Using Windows Pluggable Authentication. Additional plugin control is provided by the authentication_windows_use_principal_name and authentication_windows_log_level system variables. See Section 7.1.8, “Server System Variables”.

The method used to uninstall the Windows authentication plugin depends on how you installed it:

In addition, remove any startup options that set Windows plugin-related system variables.

The Windows authentication plugin supports the use of MySQL accounts such that users who have logged in to Windows can connect to the MySQL server without having to specify an additional password. It is assumed that the server is running with the server-side plugin enabled, as described in Installing Windows Pluggable Authentication. Once the DBA has enabled the server-side plugin and set up accounts to use it, clients can connect using those accounts with no other setup required on their part.

To refer to the Windows authentication plugin in the IDENTIFIED WITH clause of a CREATE USER statement, use the name authentication_windows. Suppose that the Windows users Rafal and Tasha should be permitted to connect to MySQL, as well as any users in the Administrators or Power Users group. To set this up, create a MySQL account named sql_admin that uses the Windows plugin for authentication:

CREATE USER sql_admin
  IDENTIFIED WITH authentication_windows
  AS 'Rafal, Tasha, Administrators, "Power Users"';

The plugin name is authentication_windows. The string following the AS keyword is the authentication string. It specifies that the Windows users named Rafal or Tasha are permitted to authenticate to the server as the MySQL user sql_admin, as are any Windows users in the Administrators or Power Users group. The latter group name contains a space, so it must be quoted with double quote characters.

After you create the sql_admin account, a user who has logged in to Windows can attempt to connect to the server using that account:

C:\> mysql --user=sql_admin

No password is required here. The authentication_windows plugin uses the Windows security API to check which Windows user is connecting. If that user is named Rafal or Tasha, or is a member of the Administrators or Power Users group, the server grants access and the client is authenticated as sql_admin and has whatever privileges are granted to the sql_admin account. Otherwise, the server denies access.

Authentication string syntax for the Windows authentication plugin follows these rules:

When invoked by the server to authenticate a client, the plugin scans the authentication string left to right for a user or group match to the Windows user. If there is a match, the plugin returns the corresponding mysql_user_name to the MySQL server. If there is no match, authentication fails.

A user name match takes preference over a group name match. Suppose that the Windows user named win_user is a member of win_group and the authentication string looks like this:

'win_group = sql_user1, win_user = sql_user2'

When win_user connects to the MySQL server, there is a match both to win_group and to win_user. The plugin authenticates the user as sql_user2 because the more-specific user match takes precedence over the group match, even though the group is listed first in the authentication string.

Windows authentication always works for connections from the same computer on which the server is running. For cross-computer connections, both computers must be registered with Microsoft Active Directory. If they are in the same Windows domain, it is unnecessary to specify a domain name. It is also possible to permit connections from a different domain, as in this example:

CREATE USER sql_accounting
  IDENTIFIED WITH authentication_windows
  AS 'SomeDomain\\Accounting';

Here SomeDomain is the name of the other domain. The backslash character is doubled because it is the MySQL escape character within strings.

MySQL supports the concept of proxy users whereby a client can connect and authenticate to the MySQL server using one account but while connected has the privileges of another account (see Section 8.2.19, “Proxy Users”). Suppose that you want Windows users to connect using a single user name but be mapped based on their Windows user and group names onto specific MySQL accounts as follows:

To set this up, create a proxy account for Windows users to connect to, and configure this account so that users and groups map to the appropriate MySQL accounts (local_wlad, local_dev, local_admin). In addition, grant the MySQL accounts the privileges appropriate to the operations they need to perform. The following instructions use win_proxy as the proxy account, and local_wlad, local_dev, and local_admin as the proxied accounts.

Now the Windows users local_user and MyDomain\domain_user can connect to the MySQL server as win_proxy and when authenticated have the privileges of the account given in the authentication string (in this case, local_wlad). A user in the MyDomain\Developers group who connects as win_proxy has the privileges of the local_dev account. A user in the BUILTIN\Administrators group has the privileges of the local_admin account.

To configure authentication so that all Windows users who do not have their own MySQL account go through a proxy account, substitute the default proxy account (''@'') for win_proxy in the preceding instructions. For information about default proxy accounts, see Section 8.2.19, “Proxy Users”.

To use the Windows authentication plugin with Connector/NET connection strings in Connector/NET 8.4 and higher, see Connector/NET Authentication.