When setting the connections to Azure Database in FME Desktop, it does not have an option to use Active Directory Integrated authentication. It has only Windows and SQL Server Authentication. Is there possible to connect Azure Database by using Active Directory Integrate? Any help is very much appreciated.
How to connect to Azure Database from FME Desktop by using Active Directory Integrated authentication
+2
- Safer
- 327 replies
-
29 April 2019
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.
+3
- Contributor
- 6 replies
-
28 August 2019
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=ActiveDirectoryIntegratedI 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
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!
+3
- Contributor
- 6 replies
-
29 August 2019
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=ActiveDirectoryIntegratedI 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
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!
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.
+3
- Contributor
- 6 replies
-
25 September 2019
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!
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.
Userlevel 1
+10
- Safer
- 371 replies
-
20 November 2019
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?
+3
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?
+3
We ended up building ODBC connection for this. In FME we use SQLExecutor with ODBC 3.x format
Userlevel 1
+10
- Safer
- 371 replies
-
24 November 2021
@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:
- 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).
- you should also be able to use the JDBC format as well as mentioned by @dhavanv2 earlier in the thread
We ended up building ODBC connection for this. In FME we use SQLExecutor with ODBC 3.x format
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?
+2
- Contributor
- 53 replies
-
15 February 2023
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.
Userlevel 3
+13
- Safer
- 601 replies
-
21 February 2023
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.
We were able to do this with ODBC connection.
Let me know if you are interested and I will provide details.
Yarko
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
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.