How to connect to Azure Database from FME Desktop by using Active Directory Integrated authentication

Hi @sqwang, Thanks for your question. The SQL Server Reader/writer doesn't support Active Directory authentication yet. We do have a request to add this in the future (we'll update here, when that's available). In the meantime, According to Mark in this other thread, you may be able to try the JDBC reader/writer, and formulate a JDBC Connection string to use Active Directory. Hope the links in Mark's answer helps.

Hello, has anyone managed to get this working with the JDBC reader? I'm trying to connect to an Azure SQL database (PaaS) server using AD Integrated authentication, but am running into issues. I can connect successfully on MS SQL Server Management Studio. I have grabbed the following JDBC connection string from Azure Portal:

jdbc:sqlserver://{myServerShortName}.database.windows.net:{myPort};database={myDatabase};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;Authentication=ActiveDirectoryIntegrated

I am seeing the following error when I try to connect:

java.lang.UnsatisfiedLinkError: com.microsoft.sqlserver.jdbc.AuthenticationJNI.ADALGetAccessTokenForWindowsIntegrated(Ljava/lang/String;Ljava/lang/String;Ljava/lang/String;Ljava/lang/String;JLjava/util/logging/Logger;)Lcom/microsoft/sqlserver/jdbc/FedAuthDllInfo;
    at com.microsoft.sqlserver.jdbc.AuthenticationJNI.ADALGetAccessTokenForWindowsIntegrated(Native Method)
    at com.microsoft.sqlserver.jdbc.AuthenticationJNI.getAccessTokenForWindowsIntegrated(AuthenticationJNI.java:84)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.getFedAuthToken(SQLServerConnection.java:4200)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.onFedAuthInfo(SQLServerConnection.java:4158)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.processFedAuthInfo(SQLServerConnection.java:4123)
    at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onFedAuthInfo(tdsparser.java:289)
    at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:125)
    at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:37)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:5067)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3731)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:94)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3675)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2979)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2488)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2142)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1993)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1164)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:760)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:208)
    at COM.safe.fme.jdbc.JdbcCommon.openConnection(JdbcCommon.java:457)
    at COM.safe.fme.jdbc.JdbcReader.openHelper(JdbcReader.java:187)
    at COM.safe.fme.jdbc.JdbcReader.open(JdbcReader.java:157)

As per

https://docs.microsoft.com/en-us/sql/connect/jdbc/connecting-using-azure-active-directory-authentication?view=sql-server-2017

and

https://docs.microsoft.com/en-us/sql/connect/jdbc/feature-dependencies-of-microsoft-jdbc-driver-for-sql-server?view=sql-server-2017,

I have added sqljdbc_auth.dll to the FME install folder, the path to which is specified on the PATH and CLASSPATH system environment variables. But no dice :( I am on Windows, so I would like to avoid setting up a Kerberos ticket if possible.

I must be missing something... If anyone has any suggestions, I'd really appreciate it, cheers!

jdbc:sqlserver://{myServerShortName}.database.windows.net:{myPort};database={myDatabase};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;Authentication=ActiveDirectoryIntegrated

java.lang.UnsatisfiedLinkError: com.microsoft.sqlserver.jdbc.AuthenticationJNI.ADALGetAccessTokenForWindowsIntegrated(Ljava/lang/String;Ljava/lang/String;Ljava/lang/String;Ljava/lang/String;JLjava/util/logging/Logger;)Lcom/microsoft/sqlserver/jdbc/FedAuthDllInfo;
    at com.microsoft.sqlserver.jdbc.AuthenticationJNI.ADALGetAccessTokenForWindowsIntegrated(Native Method)
    at com.microsoft.sqlserver.jdbc.AuthenticationJNI.getAccessTokenForWindowsIntegrated(AuthenticationJNI.java:84)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.getFedAuthToken(SQLServerConnection.java:4200)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.onFedAuthInfo(SQLServerConnection.java:4158)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.processFedAuthInfo(SQLServerConnection.java:4123)
    at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onFedAuthInfo(tdsparser.java:289)
    at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:125)
    at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:37)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:5067)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3731)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:94)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3675)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2979)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2488)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2142)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1993)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1164)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:760)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:208)
    at COM.safe.fme.jdbc.JdbcCommon.openConnection(JdbcCommon.java:457)
    at COM.safe.fme.jdbc.JdbcReader.openHelper(JdbcReader.java:187)
    at COM.safe.fme.jdbc.JdbcReader.open(JdbcReader.java:157)

I copied the mssql-jdbc-6.5.0.jre8-preview driver from the install\plugins folder to my documents\FME\Plugins\Java folder, and it works!

Update - we end up using SQL Creator with JDBC connection. we have to exposure AccessToken as a parameter, and during run time to obtain the token and pass it to the workspace. It is very much painful to have this workaround.

For posterity, I didn't need to move any drivers. What fixed the issue was replacing the sqljdbc_auth.dll that is shipped with FME with the latest version. The one in FME 2019.1 was version 4.1.5605.100 and I replaced it with version 7.4.1.0.

Are AD integrated logins on the road map? The lack of this functionality is a show-stopper for my team's adoption of FME.

Hi @palley, thanks for your valuable input. I've added your comments to our internal enhancement request (internal reference FMEENGINE-57594). I don't have any news about this at the moment. We will provide updates here on this thread.

Is using the JDBC Reader/Writer solution described in previous comments by @vorad and @xiaomengatsafe feasible for your team?

We were able to do this with ODBC connection.

Let me know if you are interested and I will provide details.

Yarko

Hi. Is there a built-in solution to this yet? I have the exact same issue as the original post - i need to access an Azure SQL DB via Active Directory and have no other way to access the database. I will look at some of the workarounds posted here, but it doesn't seem like this comes out the box with FME yet?

We ended up building ODBC connection for this. In FME we use SQLExecutor with ODBC 3.x format

@michaelwallace​ , unfortunately there's no update yet. As you've also noticed there seems to be a couple of workarounds that you can try in the meantime:

1. using the ODBC 3.x format through a Reader, Writer, SQLCreator, SQLExecutor as @ukrsolid​ mentions above (you'll might need that the ODBC driver you have supports AD; if not, you may have to grab the latest ODBC driver).
2. you should also be able to use the JDBC format as well as mentioned by @dhavanv2​ earlier in the thread

Thanks, this worked for me. Just wondering, is there a way to bypass having to enter Microsoft Azure password? I am having to enter password each time the data is read which makes automating via FME Server problematic. Is it a case of following this article: https://community.safe.com/s/article/Create-an-FME-Server-Azure-Active-Directory-Web-Connection-in-FME-Desktop? Is there another way?

Hi there, are there any updates on when we can expect to see this functionality? Our team is moving towards Azure AD auth for all Azure SQL DB's and this is an impediment to using FME in some ETL workflows. Thanks.

Hello @mgg_beca​, hm.. unfortunately, I don't have any further news on this, I apologize for the inconvience. Are you having issues implementing one of the suggested workarounds? Let me know, Kailin.

Hi there Yarko!

I am struggling to get our db connection to work, where we must use AD MFA to connect to Azure SQL Server. Would you be able to walk me through the steps to work around this please?

Thanks so much!

Caroline