Can't create linked server to Snowflake

Question

I followed all the instructions I could find:

1. Installed latest Snowflake 64bit ODBC driver to my SQL Server VM
2. Created system dsn for it (filled in User, Server, Database, Schema, Warehouse, Role and Tracing fields)
3. In SQL server, for the MSDASQL linked server provider, enabled following parameters: Nested queries, Level zero only, Allow inprocess, Supports 'Like' Operator
4. Created linked server following instructions
   • Provider=Microsoft OLE DB Provider for ODBC drivers
   • Data source: name of system DSN I created
   • Security:
   • mapped local login 'NT AUTHORITY\SYSTEM' to remote username and password (they work when I connect to Snowflake via web browser)
   • click 'Be made using the login's current security context' when clicking ok, I get the following error

Answer 1

In the security tab of the linked server:

1. you have mapped 'NT AUTHORITY\SYSTEM' (is this the sql service account?) to remote username and password.

2. For any other login not in the list you have selected Be made using the login's current security context

You connect to sql server using a windows login or a sql server login. Assume you either use domainxyz\rick or username: sqlrick and password: rick1212. When you try to use the linked server, both possible logins (domainxyz/rick or sqlrick) are not mapped to a remote[snowflake] username and password. So sql server passes through the current security context which is either your domainxyz/rick token or the sqlrick username&password. Both do fail for an external server(can they even work?).

From https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine

Be made using the login's current security context Specify that a connection will be made using the current security context of the login for logins not defined in the list. If connected to the local server using Windows Authentication, your windows credentials will be used to connect to the remote server. If connected to the local server using SQL Server Authentication, login name and password will be used to connect to the remote server. In this case a login with the exact same name and password must exist on the remote server.

You could either map your login to the remote snowflake username&password, or choose

For a login not defined in the list above, connections will: Be made using this security context and fill in the username&password for snowflake in the boxes.

/*
snowflake server=xyz12345.west-europe.azure.snowflakecomputing.com
snowflake user= testuser
snowflake password = Testuser1
*/

--system DSN for snowflake named SNOWFLAKEDSN [SnowflakeDSIIDriver, User: testuser, Server:xyz12345.west-europe.azure.snowflakecomputing.com, Database:DEMO_DB, Tracing:4 (default)]
--Note: Attempting any of the commands with a wrong password could lock temporarily the snowflake account. tread carefully


--snowflake without linked server
--openrowset using dsn
SELECT *
FROM OPENROWSET('MSDASQL', 'DSN=SNOWFLAKEDSN;UID=testuser;PWD=Testuser1', 'select cast(TABLE_NAME as varchar(100)) as tablename from demo_db.information_schema.tables');

--openrowset using dsn2 , format= provider, DSN;uid;password, query
SELECT *
FROM OPENROWSET('MSDASQL', 'SNOWFLAKEDSN';'testuser';'Testuser1', 'select cast(TABLE_NAME as varchar(100)) as tablename from demo_db.information_schema.tables');


--openrowset without dsn
SELECT *
FROM OPENROWSET('MSDASQL', 'DRIVER=SnowflakeDSIIDriver;SERVER=xyz12345.west-europe.azure.snowflakecomputing.com;UID=testuser;PWD=Testuser1', 'select cast(TABLE_NAME as varchar(100)) as tablename from demo_db.information_schema.tables');

GO


--linked server using DSN
EXEC master.dbo.sp_addlinkedserver @server = N'SNOWFLAKE_WITH_DSN', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'SNOWFLAKEDSN';
 /*every login of the sql instance connects to snowflake as testuser*/
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SNOWFLAKE_WITH_DSN',@useself=N'False',@locallogin=NULL,@rmtuser=N'testuser',@rmtpassword='Testuser1';


--linked server without DSN, name of linked server is SNOWFLAKE_WITHOUT_DSN
EXEC master.dbo.sp_addlinkedserver @server = N'SNOWFLAKE_WITHOUT_DSN', @srvproduct=N'', @provider=N'MSDASQL', @provstr=N'DRIVER=SnowflakeDSIIDriver;SERVER=xyz12345.west-europe.azure.snowflakecomputing.com';
 /*every login of the sql instance connects to snowflake as testuser*/
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SNOWFLAKE_WITHOUT_DSN',@useself=N'False',@locallogin=NULL,@rmtuser=N'testuser',@rmtpassword='Testuser1';


--linked server without DSN, hardcoded snowflake creds in the provider string (at own risk)
/*
EXEC master.dbo.sp_addlinkedserver @server = N'SNOWFLAKE_WITHOUT_DSN_HARDCODED_CREDS', @srvproduct=N'', @provider=N'MSDASQL', @provstr=N'DRIVER=SnowflakeDSIIDriver;SERVER=xyz12345.west-europe.azure.snowflakecomputing.com;UID=testuser;PWD=Testuser1';
 /*creds hardcoded in the connection string, logins use their own security context */
/* caveat: this worked ok for windows authenticated logins. sql authenticated logins could not use the linked server, not tested with msdasql in process... */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SNOWFLAKE_WITHOUT_DSN_HARDCODED_CREDS',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL;
*/