Add support to connect to MSSQL Server with Integrated Security (windows authentication)

[EugenZ]

Is it possible to connect to the MSSQL database not with user credentials, but with domain user account?

[mp911de]

Currently, password authentication is the only supported login scheme.

We don't support Kerberos, NTLM or integrated authentication, mostly due to two reasons:

1. integrated authentication involves quite a bit of native code JNI
2. Adding additional authentication mechanisms isn't something we can achieve ourselves but we need some support here. Right now, it's unclear whether these authentication mechanisms can be integrated in a non-blocking way

That being said additional authentication scheme support depends on whether someone more knowledgeable than us is able to contribute these.

[lemonprogis]

Hello, does this include the possibility of using authentication=ActiveDirectoryPassword?

[mp911de]

I'm not sure as I'm not familiar with AD authentication. right now, we don't support AD-based authentication.

[vakmancordero]

sad to read that...
currently many implementations for making connections to SQL servers require authentication using integrated security.
I was trying to implement r2dbc with multiple databases with integrated security.

[vakmancordero]

so, I will try through the sqljdbc_auth.dll, If I have success, then I will let you know

[nittalasrikanth]

so, I will try through the sqljdbc_auth.dll, If I have success, then I will let you know

Hello... did you find a resolution or a workaround for this issue ? We are running into the same problem (ntlm security on MS SQL Server) within our enterprise. Thanks!

[mp911de]

As it looks like now, integrated authentication requires a platform-specific binary. In the SQL Server JDBC driver, native code is available through com.microsoft.sqlserver.jdbc.AuthenticationJNI which is package-private. You might want to file a ticket at https://github.com/microsoft/mssql-jdbc to make SSPI usable from outside the library.

[lemonprogis]

Been discussing over on mssql-jdbc questions thread some. Would it be possible to do what is suggested?

microsoft/mssql-jdbc#818 (comment)

Thanks!

[mp911de]

I haven’t looked yet into the DLL exports. If these are namespace-free, then we could add an integration. If they point into the SQL Server JDBC package, the we cannot add it as it would cause splitpackages.

[lemonprogis]

@mp911de, awesome! Do we need a separate issue to look into that? Also, if I can help in any way, let me know.

[mp911de]

If you like, feel free to come up with a pull request. I’m currently on vacation and I don’t have a windows testing environment.

[lpandzic]

Note that JDBC driver was migrated to MSAL in August.
I assume the access is reactive as they use reactor types as is visible in changes.

@mp911de I have two questions:

1. What is the proper way to channel community interest in getting this issue done? On Jira there was a vote option but on github unfortunately there's nothing similar.
2. What would you need for testing environment? Docker containers for AD, MSSQL and app that communicates with DB through Kerberos?

[mp911de]

Thanks for the heads-up. Using MSAL that is based on Reactor internally, we should have all pre-requisites in place. In general, you can leave a :+1: on the ticket. Other than that, the ticket is labeled with ideal-for-contribution which means it is up for grabs for anyone that wants to contribute this functionality.

Since I'm not so deeply involved with AD, Kerberos etc. I don't have an answer to that question. I would expect that this feature can be only properly tested within an Azure or/and native Windows environment. We don't have access to either one from this project and I don't see how we could achieve that without anyone sponsoring the project.

Probably you want to reach out to Microsoft and make them aware of this situation.

[lovababu]

I might be late to the party :). Is r2dbc-mssql supports Azure MSI (authentication=ActiveDirectoryMSI) based authentication?

[steffimahajan]

I might be late to the party :). Is r2dbc-mssql supports Azure MSI (authentication=ActiveDirectoryMSI) based authentication?

Hey, did you find anything I am also trying same thing

[juancdelgado]

Hello,

I am also having issues with this (values have been changed for privacy):

This is what I have for a ConnectionFactory:

`
@bean
public ConnectionFactory connectionFactory() {
ConnectionFactoryOptions options = ConnectionFactoryOptions.builder()
.option(ConnectionFactoryOptions.DRIVER, "sqlserver")
.option(ConnectionFactoryOptions.HOST, "my-server.database.windows.net")
.option(ConnectionFactoryOptions.PORT, 1433)
.option(ConnectionFactoryOptions.USER, "[email protected]")
.option(ConnectionFactoryOptions.PASSWORD, "a_password123")
.option(ConnectionFactoryOptions.DATABASE, "my_db")
.option(Option.valueOf("sendStringParametersAsUnicode"), false)
.option(Option.valueOf("encrypt"), true)
.option(Option.valueOf("trustServerCertificate"), true)
.option(Option.valueOf("hostNameInCertificate"), "*.database.windows.net")
.option(Option.valueOf("authentication"), "ActiveDirectoryPassword")
.build();

log.trace("Connection Factory Details: " + options);
return ConnectionFactories.get(options);

}
`

I'm getting the following error, only after I try to execute a sql query:
Caused by: io.r2dbc.mssql.ExceptionFactory$MssqlNonTransientResourceException: Cannot open server "my-app.cloud.com" requested by the login. The login failed.

This is an Azure Active Directory Acccount which i'm providing the service account username and password

[KyleTTownsend]

Hello,

I am also having issues with this (values have been changed for privacy):

This is what I have for a ConnectionFactory:

` @bean public ConnectionFactory connectionFactory() { ConnectionFactoryOptions options = ConnectionFactoryOptions.builder() .option(ConnectionFactoryOptions.DRIVER, "sqlserver") .option(ConnectionFactoryOptions.HOST, "my-server.database.windows.net") .option(ConnectionFactoryOptions.PORT, 1433) .option(ConnectionFactoryOptions.USER, "[email protected]") .option(ConnectionFactoryOptions.PASSWORD, "a_password123") .option(ConnectionFactoryOptions.DATABASE, "my_db") .option(Option.valueOf("sendStringParametersAsUnicode"), false) .option(Option.valueOf("encrypt"), true) .option(Option.valueOf("trustServerCertificate"), true) .option(Option.valueOf("hostNameInCertificate"), "*.database.windows.net") .option(Option.valueOf("authentication"), "ActiveDirectoryPassword") .build();

log.trace("Connection Factory Details: " + options);
return ConnectionFactories.get(options);

} `

I'm getting the following error, only after I try to execute a sql query: Caused by: io.r2dbc.mssql.ExceptionFactory$MssqlNonTransientResourceException: Cannot open server "my-app.cloud.com" requested by the login. The login failed.

This is an Azure Active Directory Acccount which i'm providing the service account username and password

I am running into the same issue you are. Were you able to come up with a solution for Active Directory authentication?