MSSQL authentication "Active Directory - Password" option

[mosborn]

Hope I can be helpful here. I apologize for not being able to dig into the application source to offer a more concrete suggestion.

Using a Mac, I tried to use both the connection options "Active Directory - Integrated" and "Active Directory - Password" to connect to SQL Server. Both options errored with a message that the login for user '' (as in blank string) failed. I think this has been noted elsewhere.

To diagnose, I starting from scratch to arrive at the following recipe:

A Kerberos login module configuration file containing:

SQLJDBCDriver {
   com.sun.security.auth.module.Krb5LoginModule required useTicketCache=true;
};

MSSQLADDesktop {
   com.sun.security.auth.module.Krb5LoginModule required useTicketCache=true;
};

MSSQLADUserPwd {
   com.sun.security.auth.module.Krb5LoginModule required useTicketCache=false;
};

A simple Groovy script launched with "-D java.security.auth.login.config=my-login-module.conf":

import groovy.sql.Sql

def db = [
  driver: 'com.microsoft.sqlserver.jdbc.SQLServerDriver',
  url: 'jdbc:sqlserver://myserver.my.company.com',
  databaseName: 'mydb',
  encrypt: 'true',
  integratedSecurity: 'true', 
  authenticationScheme: 'JavaKerberos', 
  userName: '[email protected]', 
  password: '$%^*',                            
  trustServerCertificate: 'true',
  jaasConfigurationName: 'MSSQLADUserPwd'] as Properties

Sql.withInstance(db) { sql ->
    sql.eachRow('select top 20 * from mytable') { println it }
}

Swapping MSSQLADUserPwd and MSSQLADDesktop in the code toggles cleanly between AD desktop and user/password modes.

Critical details:

1. The ambiguity of the term "integratedSecurity" confused me for a bit. It implied desktop integration and therefore supplying user/password is not integrated. Of course "integrated" actually means only 'utilizing Kerberos', which can be done in different ways.
2. integratedSecurity = true and authenticationScheme = JavaKerberos are mandatory for both SSO logins and AD user/password. I suspect the current code might be doing the wrong thing?
3. If you need to toggle between desktop/passwordless and user/password, it is clumsy or impossible without the login module file and specifying jaasConfigurationName in the connection string.
4. Looking at the SQL Server driver code, it seems most straightforward to launch the app with the confg file specified with -D java.security.auth.login.config=myfile. I didn't see a good way to manage it programmatically.

---

Coming to the current DBeaver code, AD User/Password worked for me after the following adjustment to SQLServerAuthModelADPassword:

    public Object initAuthentication(@NotNull DBRProgressMonitor monitor, @NotNull DBPDataSource dataSource, @NotNull AuthModelDatabaseNativeCredentials credentials, @NotNull DBPConnectionConfiguration configuration, @NotNull Properties connProperties) throws DBException {
//        connProperties.put(SQLServerConstants.PROP_CONNECTION_INTEGRATED_SECURITY, String.valueOf(false));
        connProperties.put(SQLServerConstants.PROP_CONNECTION_INTEGRATED_SECURITY, String.valueOf(true));
        connProperties.put(SQLServerConstants.PROP_CONNECTION_AUTHENTICATION_SCHEME, SQLServerConstants.AUTH_SCHEME_KERBEROS);
//        connProperties.put(SQLServerConstants.PROP_CONNECTION_AUTHENTICATION, SQLServerConstants.AUTH_ACTIVE_DIRECTORY_PASSWORD);

        if (!CommonUtils.isEmpty(configuration.getUserName())) {
            connProperties.put("UserName", configuration.getUserName());
        }
        if (!CommonUtils.isEmpty(configuration.getUserPassword())) {
            connProperties.put("Password", configuration.getUserPassword());
        }
        return credentials;
    }

I couldn't make desktop login (Active Directory - Integrated) work even with similar mods to SQLServerAuthModelADIntegrated. On the other hand, if I have a kerb ticket and feed a bad user/password to the AD User Password option, it falls back to my desktop login.

I also succeeded in making it work by starting with the Database menu option "New Connection from JDBC URL", using "Connect by URL", and fully specifying the string. This does not work if you create the connection from the normal "Create connection" menu, so whatever is going on with the management of the SQL Server connection details is foiling success.

Hope this helps.