Connect to Snowflake using Microsoft SQL Server

Seamlessly integrating diverse data management systems is essential for optimizing efficiency and enhancing decision-making. Snowflake’s cloud data warehousing solutions, combined with the robustness of Microsoft SQL Server, create a comprehensive environment for data storage, processing, and analytics. By connecting Snowflake with Microsoft SQL Server, you can leverage the strengths of both platforms, enhancing your data strategies with scalability and advanced features while maintaining familiarity.

This guide provides a step-by-step approach to integrating these two powerful data systems. The integration not only enables a hybrid data storage solution but also enhances your analytics with Snowflake’s unique capabilities, all within the reliable framework of Microsoft SQL Server.

In the following sections, we will explore the technical setup of a linked server, the configuration of Open Database Connectivity (ODBC) drivers, and the fine-tuning of system performance to ensure a versatile and future-ready data management strategy.

We will also guide you through integrating BrightGauge, a business intelligence platform, with Microsoft SQL Server, enabling effective creation of gauges and reports.

In this page:

Prerequisites

Permissions

  • Microsoft SQL Server:

    • Sysadmin or setupadmin server roles are needed to create and configure linked servers.

  • Snowflake:

    • Installation privileges for the ODBC driver, which usually require local administrator access on the server or workstation.

    • Appropriate roles in Snowflake with permissions to create, manage, and query databases and schemas.

Connecting to Microsoft SQL Server Using SSMS

  1. Sign in to your Microsoft SQL Server instance.

  2. Launch Microsoft SQL Server Management Studio (SSMS):

    1. In the search box, enter "SQL Server Management Studio" or "ssms.exe".

    2. Choose Microsoft SQL Server Management Studio from the search results.

    3. If the Connect to Server window does not open automatically, go to Object Explorer > Connect > Database Engine.

  3. In the Connect to Server dialog box:

    1. Select the Server type as Database Engine.

    2. Enter the Server name. Use localhost if connecting locally or specify the server and instance name for remote connections.

    3. For Authentication, use Windows Authentication or SQL Server Authentication as required. If available, you can also use Microsoft Entra authentication.

  4. Click Connect to establish the connection.

  5. Verify the connection in Object Explorer, where the server’s name, SQL Server version, and username are displayed.

    6. Login screen to Connect to Server Microsoft SQL Server displaying the server details with the Connect button highlighted.

Creating an ODBC data source in Microsoft SQL

Install the Snowflake ODBC driver on your system to continue. You can obtain the driver from the official Snowflake website or via the Snowflake web interface.

  1. Launch the ODBC Data Sources utility on your Microsoft Windows Server by searching for “ODBC Data Sources” in the Windows search bar.

    2. A list of the ODBC Data Sources, with the 64-bit version highlighted.

  2. Access your SQL Server:

    1. Sign in to your Microsoft SQL Server.

    2. Open Microsoft SQL Server Management Studio (SSMS). If the “Connect to Server” dialog box does not appear automatically, find it by navigating to Object Explorer > Connect > Database Engine in SSMS.

    3. Connect to your server instance using your Windows authentication credentials.

  3. Set up a new ODBC Data Source:

    1. In the ODBC Data Source Administrator, go to the System DSN (Data Source Name) tab and click ‘Add’ to create a new data source.

    2. Select the Snowflake ODBC driver from the list of installed drivers. If the driver is not present, install it beforehand.

      3. Dialog for Create New Data Source containing a list of the data sources drivers with the Snowflare OBDC driver highlighted.

  4. Configure your data source:

    • Provide a name for your data source, such as “Snowflake DSN”.

    • Enter your Snowflake credentials, including your username and password.

    • Fill in the connection details from Snowflake:

      • Server: The URL of your Snowflake instance.

      • Database: The specific database you want to access.

      • Schema: The schema within the database you are targeting.

      • Warehouse: The computing warehouse in Snowflake that will execute your queries.

        • Dialog for Snowflake Configuration displaying the various connection detail options.

  5. Test the connection: After configuring the settings, click ‘Test’ to ensure the connection to Snowflake is successful. A confirmation message should appear if the connection was correctly established.

Ensure your MS SQL server’s security settings allow connections to Snowflake. If you encounter connectivity issues, it may be necessary to update the security allowlist for your SQL Server.

Sign in to Snowflake: Use your credentials to access your account. After setting up the ODBC connection with the correct Snowflake ODBC driver configuration, perform a connection test. A successful test will confirm that you have established an active ODBC connection to Snowflake.

Login screen to Sign into your Snowflake instance. Test Results dialog showing a Successful connected to data source message.

Change the permissions on Microsoft Data Access Components OLE DB Provider for SQL Server (MSDASQL)

Changing your permissions allows Microsoft SQL Server to import tables from Snowflake SQL.

Right-click on MSDASQL and select Properties. Ensure the following options are enabled:

  • Nested Queries: This allows for the execution of SQL queries within other SQL queries.

  • Allow in process: This setting permits the OLE DB provider to run within the same process as the SQL Server.

  • Supports ‘Like’ Operator: This enables the use of the ‘LIKE’ SQL operator for pattern matching in queries.

Object Explorer path showing MSDASQL location under Server Objects, Linked Servers, Providers. Dialog for the Provider optiosn with Nested Queries, Allow in process and Support Like Operator checked.

Create a New Registry Key

  1. Open the Registry Editor on your Microsoft SQL Server and navigate to:

    2.  Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\YourDSNName

    Where YourDSNName i is the Data Source Name (DSN) you specified during the ODBC setup.

  2. Right-click in the white space and choose New > String Value.

  3. Name this new key  default_varchar_size  and assign it a value of 4000. This defines the default size for variable character (VARCHAR) columns.

The Windows Registry is a system database that stores settings for the operating system and applications. Incorrect changes can cause serious system issues. Always back up the registry before editing to ensure system recovery if needed.

Regedit screen showing the path to create the new default_varchar_size key. In this example, Software, OBDC, ODBC.INI, Snowflake DSN

Setting Up a Linked Server in Microsoft SQL

  1. Initiate the Linked Server Configuration:

    1. In SQL Server Management Studio (SSMS), navigate to Server Objects > Linked Servers.

    2. Right-click and select New Linked Server to begin the setup.

    2. Menu item for New Linked Server menu option under Server Objects, Linked Servers.

  2. Provide the Linked Server Details:

    1. Assign a descriptive name to the Linked Server. For example, “Snowflake” is used here for illustration.

    2. In the provider dropdown menu, choose Microsoft OLE DB Provider for ODBC Drivers (MSDASQL).

  3. Specify the Data Source: Enter the exact DSN you established during the ODBC configuration. Remember, it is case-sensitive. In this example, “Snowflake DSN” is used.

    4. Dialog for the New Linked Server showing the checked Other data source details radio with Microsoft OLE DB Provider for ODBC Drivers selected as the provider.

  4. Configure Security Settings:

    1. Click on the Security page to map your local Microsoft SQL Server logins to the Snowflake credentials.

    2. Select Add. Under Local Login, input the login you utilize for SSMS, such as “Azure-SQL-BG\nableadmin”.

    3. Avoid selecting Impersonate.

    4. Input your Snowflake credentials under the appropriate fields.

    5. Choose the option Be made using the login’s security context for the connection to be authenticated.

    5. Dialog for the New Linked Server with the Be made using the login’s security context login type selected.

  5. Complete the Setup:

    1. Click OK to finalize the creation of the Linked Server.

    2. If the setup is correct, “Snowflake” will now appear in the list of Linked Servers.

    3. Expand the Linked Server node to view and access the Snowflake table structures. Typically, you will find your data under a schema such as “SHARED_ANALYTICS_DATA”.

    6. Object Explorer showing server and Shared_Analytics_Data location. In this example under Providers, Snowflake, Catalogs.

  6. Ensure Database Availability: Confirm there is at least one database present on the server. It can be empty, but its existence is required for applications, like BrightGauge, to access the linked data.

Creating an SQL Query in Microsoft SQL Server

  1. Create an SQL Query:

    1. In SQL Server Management Studio (SSMS), locate the database added from Snowflake, referred to in this example as “SNOWFLAKE”.

    2. Expand the Views section to find the specific view you wish to query. For instance, look for SHARED_DATA.DIM_CUSTOMER_LATEST.

    3. Right-click on the desired view and select Script View as > SELECT To > New Query Editor Window. This action will generate a SQL query template in a new editor window.

  2. Construct and Test Your Query: In the new query editor window, you can modify the generated query or write a new one to retrieve data. Here are two example queries to get started:

    3. SELECT * FROM SHARED_DATA.DIM_CUSTOMER_LATEST
    SELECT * FROM SNOWFLAKE.SHARED_ANALYTICS_DATA.SHARED_DATA

    SQL query window showing the example select commands.

  3. Replace SNOWFLAKE.SHARED_ANALYTICS_DATA.SHARED_DATA with the full path to the view or table you are querying, ensuring all identifiers are correct.

  4. Execute and Review the Query Results:

    1. To run the query, click the Execute button, which may appear as a green arrow in the toolbar. This will process your SQL command.

    2. Upon successful execution, the live data from the view or table will be displayed in the results pane below the query editor.

    5. SQL query window showing the queries in the top pane and the resuts in the bottom

  5. Integrate Applications: With the linked server successfully returning data, you are now ready to integrate this SQL Server instance with Connect to Microsoft SQL Server using BrightGauge or any other application that requires access to your Snowflake data.