To show details on how to connect to Snowflake Data Warehouse using Microsoft’s SQL Management Studio
Connecting SSMS to Snowflake
Download and install the Snowflake ODBC driver
The documentation around the ODBC driver can be found at the below link:
https://docs.snowflake.com/en/user-guide/odbc-download.html
We are interested into the installation and configuration of the ODBC driver for Windows which can be found at this link:
https://docs.snowflake.com/en/user-guide/odbc-windows.html
Under step 1, select the snowflake64_odbc… .msi and download
Once the file has downloaded, double click to run the install.
Configuration of the ODBC driver
Once the driver has been installed, it’s time to set up the ODBC DNS so that it can be used later on in SQL Management Studio.
From the start menu, search for ODBC Data Sources:
Open the application and navigate to the Drivers tab where you should see a reference to SnowflakeDSIIDriver:
Once we’re sure the driver is installed correctly, open the System DSN tab and click Add… - This will allow us to define the server that we want to connect to using the ODBC driver.
Select the SnowflakeDSIIDriver and click Finish which will open the Snowflake Configuration Dialog
The Snowflake documentation says to fill out most of these fields, however all you need to fill in is:
Data Source – The name for the DSN reference, lets call it SNOWFLAKE
Server – For example: gp78166.europe-west2.gcp.snowflakecomputing.com
Click OK and then your ODBC Driver is all set up.
Setting up the linked server
Once the ODBC driver is fully installed and configured, we can move onto opening up SQL Management Studio (SSMS) and adding a linked server.
Open up SSMS, connect to your server, in my example I am using SQL Express, navigate to Server Objects and then Linked Servers.
The first thing we need to do is open up the Providers folder and modify the properties for the MSDASQL provider.
And then make sure the following boxes are selected:
Click OK to save the changes to the settings.
Next we right click on Linked Servers and click “New Linked Server…”
In the Linked Server box, we add a name for our linked server, which we shall call SNOWFLAKE and from the Provider drop down we select "Microsoft OLE DB Provider for ODBC Drivers.":
And after selecting that, we can then add the Data Source which is the name we assigned to our DSN earlier:
Then we navigate to the Security tab on the left hand side:
We then add a new login, Local Login is the user that is used to connect to the server and the remote user and password are the credentials you have been given to establish your Snowflake connection.
Make sure to also select “Be made using the login’s current security context” and then click OK.
You can now run a new query on your server like:
SELECT*FROMOPENQUERY(SNOWFLAKE,'SELECT * FROM <DATABASE>.<SCHEMA>.salesanalysisdetail')
The DATABASE and SCHEMA fields will be supplied to you be Gladstone to replace the placeholders, and then you can view the results as a normal SQL query