Connecting to MS SQL Server through ODBC from Windows

[Hideaki_Hayashi]

Hi!
Here is how I was able to set up ODBC DSN on Windows to connect to Microsoft SQL Server, to connect Exploratory to it.

1. Locate ODBC Data Source Administrator. You can find it by typing “odbc” to the search form of Windows.
   

   

2. Start it by clicking it. You will see the dialog box of ODBC Data Source Administrator like below.
   

   

3. Click “Add”, and you will see “Create New Data Source” dialog.
   

   

4. By default, there should be a driver for SQL Server. Select it and click “Finish”. You will see the following dialog.
   

   

5. Name your ODBC DSN and type in the hostname of your SQL Server like above. Click “Finish”.
   

   

6. Go through the wizard by typing in your SQL Server’s configuration and keeping clicking “Next” button.
   

   

7. Check appropriate options and click “Next”, to see the following dialog.
   

   

8. Again, check appropriate options and click “Next”, to see the following dialog.
   

   

9. At this point, you can test connection to your SQL Server. Click “Test Data Source…”. If things go well, you will see following popup saying “TESTS COMPLETED SUCCESSFULLY!”.
   

   

10. Click OK, and now you have ODBC DSN for your SQL Server!
    

    

11. Follow http://docs.exploratory.io/import/odbc.html to register this ODBC DSN to Exploratory so that you can start importing and analyzing you data!

[emmanuel_nanchen]

Hi,

thank you for this tutorial. I did create an ADBC datasource with Trusted_Connection= Yes (delegating the security aspects to the underlying Windows framework). However, when adding the connection to Exploratory, I can test the connection successfully; but at the time of finalizing the connection and clicking the Add button, Exploratory will keep asking for a username and prevent me from going any further.

Is there any other way I could go around this limitation?

Thanks,

Emmanuel

[Hideaki_Hayashi]

Hi Emmanuel,
Thank you for reporting this issue.
Let us try building a version that would not require username for ODBC connection.
I will get back to you shortly with the build.

[Hideaki_Hayashi]

Hi Emmanuel,
Here is the patched version of Exploratory that won’t require username for ODBC connection.
Let us know if this works for you.
https://exploratory.io/downloads/Exploratory_3.1.0.1_WIN.zip

[Hideaki_Hayashi]

This fix is included in Exploratory v3.2.
So, the latest Exploratory should not have this issue.

[jprins]

Are we sure that this bug is still fixed? I am using version 3.5.0.2_WIN and I’ve gotten a similar error:

Error in getDBConnection(“odbc”, NULL, NULL, NULL, username, password, :
argument “username” is missing, with no default

This happens if I create a DSN with the SQL Server Native Client 11.0 and the ODBC Driver 13 for SQL Server, both using Windows Auth.

Interestingly, Exploratory can view the databases and tables, but just not Preview the data. That’s when the error happens.

Note that when I Add a connection in Exporatory and click on “Test Connection”, the green confirmation bar at the top only says “Connect to”. It never says what it is connected to.

(Edit: I also tried a DSN with a SQL Server driver version 6.01…)

[Hideaki_Hayashi]

Hi, thank you for reporting the issue. Let me look into it.