How to write ODBC connection string for Snowflake

Question

How can I write an ODBC connection string for Snowflake? I can't seem to figure it out. Below is the sample code I put together in .NET Core:

using System;
using System.Data.Odbc;

namespace SnowflakeTest
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello World!");

            string connetionString = null;
            OdbcConnection cnn;
            connetionString = "Driver={SnowflakeDSIIDriver};Server=xxxxxxx.snowflakecomputing.com;Database=SNOWFLAKE_SAMPLE_DATA;User=xxxxxx;Password=xxxxxxxxx";
            cnn = new OdbcConnection(connetionString);

            try
            {
                cnn.Open();
                Console.Write("Connection Open ! ");
                cnn.Close();
            }
            catch (Exception ex)
            {
                Console.Write("Can not open connection ! " + ex.Message);
            }

            Console.ReadKey();
        }
    }
}

Answer 1

Here is how you can build an ODBC connection.

1. Create a file on the desktop, called foo.udl. Double Click on it
2. In the Provider tab, select `Microsoft OLE DB Provider for ODBC Drivers'
3. In the Connection tab, click Use connection string, then the 'Build` button.
4. Under Machine Data Source pick your driver, then fill out the rest (e.g. user & password).
5. Then click OK.
6. Open the file in Notepad and boom - there is your connection string.

Answer 2

I'm coding in Excel, with the VBA project unlocked, so don't want to use an UID/PWD combination.

If you're in my situation, I was able to drop the PWD and added "Authenticator=ExternalBrowser;" in its place, then use Windows API to get current user's login followed by "@" and our domain, and use it for the UID. The user will see a web browser (tab) open briefly only on the first call. I'm assuming there's a token being given that lasts for some time, though I've not tested how long - likely a setting by the DB Admins.

Here's my full function:

Public Function sSnowflakeCnxnStr() As String   
    sSnowflakeCnxnStr = Replace("Driver={SnowflakeDSIIDriver};" _
    & "Server=OurDBServer.snowflakecomputing.com;" _
    & "Database=OurDB_
    & "Schema=OurSchema;" _
    & "Authenticator=ExternalBrowser;" _
    & "UID=<UserName>@OurDomain.com" _
    , "<UserName>", GetUserName)
End Function

Where GetUserName is a wrapper for the API call:

Private Declare PtrSafe Function wu_GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
    (ByVal lpbuffer As String, nSize As Long) As Long

Function GetUserName() As String
    Dim sUserName As String
    Dim lLength As Long
    Dim lResult As Long

    '-- Set up the buffer
    sUserName = String$(255, 0)
    lLength = 255

    '-- Make the call
    lResult = wu_GetUserName(sUserName, lLength)

    '-- Clean up and Assign the value
    GetUserName = Left(sUserName, InStr(1, sUserName, Chr(0)) - 1)
End Function

I've barely started using it, but was able to pull the full list of tables in the DB so it seems to be working.

Also: For 32-bit Excel, I had to install the 32-bit SnowFlake driver.

Answer 3

ODBC Configuration and Connection Parameters

Required Connection Parameters

• name (Data Source)
• port (Port)
  • You do not need to change the default Port value of 443.
• pwd (Password)
• server (Server)
• uid (User)

Optional Connection Parameters

• CLIENT_SESSION_KEEP_ALIVE=Boolean
• database (Database)
• role (Role)
• schema (Schema)
• SecondaryRoles (Role)
• tracing (Tracing)
• warehouse (Warehouse)

Related: ODBC Driver

Answer 4

All you need to do is to enter this connection string:

connetionString = "Driver={SnowflakeDSIIDriver};Server={accountid}.snowflakecomputing.com;Database=SNOWFLAKE_SAMPLE_DATA;uid=xxxxxxxxx;pwd=xxxxxxxxx";