I am trying to create a 64-bit ODBC data source using the Snowflake ODBC driver in Windows 10 via the command line (for inclusion in a script). Is this possible using either a command-line tool such as odbcconf.exe or a PowerShell cmdlet such as Add-OdbcDsn, and if so, could anybody provide an example of valid syntax they have used to do so?
I have done this successfully in the past with other ODBC drivers, such as:
Oracle Instant Client via odbcconf.exe:
C:\Windows\System32\odbcconf.exe /A {CONFIGSYSDSN "Oracle in instantclient64" "DSN=Example_DSN|Server=Example_Server"}
SQL Server via Add-OdbcDsn:
Add-OdbcDsn -Name "Example_DSN" -DriverName "SQL Server" -DsnType "System" -Platform "32-bit" -SetPropertyValue @("Server=Example_Server","Trusted_Connection=Yes","Database=Example_Database")
However, I have been unsuccessful in using similar commands to create a DSN using the Snowflake ODBC driver.
I am using the following article for guidance: https://docs.snowflake.com/en/user-guide/odbc-windows.html
For reference, I can create a DSN via the "ODBC Data Source Administrator (64-bit)" GUI by selecting the "SnowflakeDSIIDriver" driver and filling in the data source, user, and server attributes in the "Snowflake Configuration Dialog" window, so I know it's possible create a DSN using this driver; I just can't figure out how to do it via the command line.
In the case of either of these odbcconf.exe commands:
C:\Windows\System32\odbcconf.exe /A {CONFIGSYSDSN "SnowflakeDSIIDriver" "DSN=Example_DSN"}
C:\Windows\System32\odbcconf.exe /A {CONFIGSYSDSN "SnowflakeDSIIDriver" "DSN=Example_DSN|Server=EXAMPLE.us-east-1.snowflakecomputing.com Database=Example_Database Schema=Example_Schema Warehouse=Example_WH Role=Example_Role Tracing=4 Authenticator=externalbrowser"}
Instead of creating the DSN, the "Snowflake Configuration Dialog" windows pops up as if I had attempted to create the DSN via the "ODBC Data Source Administrator (64-bit)" GUI. Only the "Data source" field is populated with the value I placed after "DSN=". All other fields are blank, regardless of how many additional attributes I try to pass in the command:
Snowflake Configuration Dialog
If I add the user and server manually when the dialog window pops up and then click "OK," the DSN does get created; however, I do also receive an error message (I get this same error if I select "cancel" on the dialog window as well):
"CONFIGSYSDSN: Unable to create a data source for the 'SnowflakeDSIIDriver' driver: Driver's ConfigDSN, ConfigDriver, or ConfigTranslator failed with error code -2147467259."
(Obviously, this doesn't solve the problem, as my intent would be for the command alone to create the DSN without user interaction.)
In the case of Add-OdbcDsn via PowerShell:
Add-OdbcDsn -Name "Example_DSN" -DriverName "SnowflakeDSIIDriver" -DsnType "System" -Platform "64-bit"
The command just seems to hang indefinitely until I cancel it. I never see any visible error messages, and I don't get the "Snowflake Configuration Dialog" window.
Does anybody have any suggestions I could try, and/or does anybody know for sure whether this is possible or if it's simply not supported by the Snowflake ODBC driver?
Yes, you can do this but the Snowflake driver configurations must be defined using registry keys.
The answers above are correct but not very descriptive on the solution. They led me to be able to script this in Powershell.
Issues:
Solution: Here is a full solution that worked for me (specifically tested on Windows 10 with driver snowflake64_odbc-3.2.0). It uses the registry to configure the ODBC User DSN but could be modified for a System DSN.
Description: Programmatically configure Snowflake ODCB DNS for Windows instance
Prerequisite: Install Snowflake Driver (SnowflakeDSIIDriver). This can be downloaded from Snowflake
HKCU:\SOFTWARE\ODBC\ODBC.INI\Snowflake is the registry path where the User DSN is configured HKLM:\SOFTWARE\ODBC\ODBC.INI\Snowflake is the registry path where the System DSN is configured
new-itemproperty -path 'HKCU:\SOFTWARE\ODBC\ODBC.INI\Snowflake' -name "UID" -value %user_name% | out-null
new-itemproperty -path 'HKCU:\SOFTWARE\ODBC\ODBC.INI\Snowflake' -name "SERVER" -value "%server_name.snowflakecomputing.com%" | out-null
new-itemproperty -path 'HKCU:\SOFTWARE\ODBC\ODBC.INI\Snowflake' -name "DATABASE" -value "%data_base%" | out-null
new-itemproperty -path 'HKCU:\SOFTWARE\ODBC\ODBC.INI\Snowflake' -name "SCHEMA" -value "%schema%" | out-null
new-itemproperty -path 'HKCU:\SOFTWARE\ODBC\ODBC.INI\Snowflake' -name "WAREHOUSE" -value "%warehouse%" | out-null
new-itemproperty -path 'HKCU:\SOFTWARE\ODBC\ODBC.INI\Snowflake' -name "TRACING" -value "%number%" | out-null
new-itemproperty -path 'HKCU:\SOFTWARE\ODBC\ODBC.INI\Snowflake' -name "AUTHENTICATOR" -value "%authenticator%"
Note: Substitute %value% for your values. The registry keys are REG_SZ (String Values) and the -names are case sensitive. If you delete the ODBC DSN then the registry entries will be removed.
Once executed you can verify configuration by launching ODBC and checking/testing the configuration.