Does anyone know how to connect to an Azure SQL Se...
# replication-troubleshootingw
Walker Philips
12/07/2022, 9:44 PMDoes anyone know how to connect to an Azure SQL Server instance that requires ActiveDirectoryPassword? I have tried using the MSSQL connector, but have had no luck with the various combinations I have been trying. The ODBC connection string that works in python is 'DRIVER={ODBC Driver 18 for SQL Server};server='+server+';UID='+username+';PWD='+ password+';Authentication=ActiveDirectoryPassword;'
n
Nataly Merezhuk (Airbyte)
12/08/2022, 3:36 PMHi Walker, could you please give me bit more info:
1. Airbyte version
2. Connector versions
3. Any sync logs
Also - are you using any documentation for this?
Thanks!
w
Walker Philips
12/09/2022, 4:15 PM1. How do you locate the current version?
2. MSSQL 0.4.25
3. I haven't been able to connect to this source so there isnt any logs yet
4. I am unsure what you mean by documentation in this regard? I am following the instructions for adding an MSSQL source (not CDC but using Standard replication). I have also read through the MSSQL documentation, but was unable to identify a solution. The connection fails and says "non-json response" as the error message so I don't understand how to debug the issue that is happening.
n
Nataly Merezhuk (Airbyte)
12/09/2022, 7:32 PMThe current version should be in the bottom left instance of the UI or in the logs. I imagine it would be displayed on starting up Airbyte as well, but haven't tracked it down there yet.
Thanks for the details! From what I can see this feature isn't available yet, though you'd be able to login using sql login.
Could you please make a feature request for this on GitHub?
w
Walker Philips
12/09/2022, 7:55 PM0.40.22
Unfortunately this a vendor managed instance for a CRM and not sure if we can create SQL logins, I will see what the possibility of that looks like. I will make the feature request though, seems like it could be a relatively simple fix to the connector. I am working through the custom connector tutorial now and may be able to submit a pull request to the MSSQL connector with my suggested changes.
n
Nataly Merezhuk (Airbyte)
12/12/2022, 2:44 PMThat would be great! Feel free to tag me on GitHub @natalyjazzviolin , I'll help expedite the review process!
w
Walker Philips
12/23/2022, 3:35 PMI tried a different combination of login credentials and am now finally getting back some clearer errors.
Walker Philips
12/23/2022, 3:35 PMIf the "Authentication=ActiveDirectoryPassword" is not provided as a JDBC URL Params you will get the following message.
Walker Philips
12/23/2022, 3:38 PMState code: S0001; Error code: 18456; Message: Login failed: Request is not authenticated. RequestId: TDS;8637ac21-313d-4899-8199-a82a220cd330;1 Time: 2022-12-23T153354.1097435Z ClientConnectionId:################
If you do provide the JDBC URL Param, you will get the following error:
"Message: Failed to load MSAL4J Java library for performing ActiveDirectoryPassword authentication."
Ironically, I was having similar error messages trying to connect to the SQL Server using a JDBC connection from a Python program to try and get to the bottom of the issue. Perhaps it is an issue with the MSAL4J Java library itself. Java is one of the few languages I have little to no experience it so I am not much help at this point.
Walker Philips
12/23/2022, 3:59 PMI have opened an issue within the Github for the problem. This likely is a Driver problem, and may require an update to the docker image I will try and dig through the image I have and try placing an updated Driver file in the appropriate place and see if I can work around the issue.
n
Nataly Merezhuk (Airbyte)
12/27/2022, 6:23 PMGreat, thanks!