internal void renewSession()
{
RenewSessionRequest postBody = new RenewSessionRequest()
{
oldSessionToken = this.sessionToken,
requestType = "RENEW"
};
SFRestRequest renewSessionRequest = new SFRestRequest
{
jsonBody = postBody,
uri = BuildUri(SF_TOKEN_REQUEST_PATH,
new Dictionary <string, string> {
{ SF_QUERY_REQUEST_ID, Guid.NewGuid().ToString() }
}),
authorizationToken = string.Format(SF_AUTHORIZATION_SNOWFLAKE_FMT, masterToken),
sfRestRequestTimeout = Timeout.InfiniteTimeSpan
};
var response = restRequest.Post <RenewSessionResponse>(renewSessionRequest);
if (!response.success)
{
SnowflakeDbException e = new SnowflakeDbException("",
response.code, response.message, "");
logger.Error("Renew session failed", e);
throw e;
}
else
{
sessionToken = response.data.sessionToken;
}
}
/// <summary>
/// Generate the authenticator given the session
/// </summary>
/// <param name="session">session that requires the authentication</param>
/// <returns>authenticator</returns>
/// <exception cref="SnowflakeDbException">when authenticator is unknown</exception>
internal static IAuthenticator GetAuthenticator(SFSession session)
{
string type = session.properties[SFSessionProperty.AUTHENTICATOR];
if (type.Equals(BasicAuthenticator.AUTH_NAME, StringComparison.InvariantCultureIgnoreCase))
{
return(new BasicAuthenticator(session));
}
else if (type.Equals(ExternalBrowserAuthenticator.AUTH_NAME, StringComparison.InvariantCultureIgnoreCase))
{
return(new ExternalBrowserAuthenticator(session));
}
// Okta would provide a url of form: https://xxxxxx.okta.com
else if (type.EndsWith("okta.com") && type.StartsWith("https://"))
{
return(new OktaAuthenticator(session, type));
}
else if (type.Equals(OAuthAuthenticator.AUTH_NAME, StringComparison.InvariantCultureIgnoreCase))
{
return(new OAuthAuthenticator(session));
}
var e = new SnowflakeDbException(SFError.UNKNOWN_AUTHENTICATOR, type);
logger.Error("Unknown authenticator", e);
throw e;
}
private void VerifyPostbackUrl()
{
int formIndex = samlRawHtmlString.IndexOf("<form");
bool extractSuccess = formIndex == -1;
// skip 'action="' (length = 8)
int startIndex = samlRawHtmlString.IndexOf("action=", formIndex) + 8;
int length = samlRawHtmlString.IndexOf('"', startIndex) - startIndex;
Uri postBackUrl;
try
{
postBackUrl = new Uri(HttpUtility.HtmlDecode(samlRawHtmlString.Substring(startIndex, length)));
} catch (Exception e)
{
logger.Error("Fail to extract SAML from html", e);
throw new SnowflakeDbException(SFError.IDP_SAML_POSTBACK_NOTFOUND);
}
string sessionHost = session.properties[SFSessionProperty.HOST];
string sessionScheme = session.properties[SFSessionProperty.SCHEME];
if (postBackUrl.Host != sessionHost ||
postBackUrl.Scheme != sessionScheme)
{
var e = new SnowflakeDbException(
SFError.IDP_SAML_POSTBACK_INVALID,
postBackUrl.ToString(),
sessionScheme + ":\\\\" + sessionHost);
logger.Error("Different urls", e);
throw e;
}
}
/// <summary>
/// Generate the authenticator given the session
/// </summary>
/// <param name="session">session that requires the authentication</param>
/// <returns>authenticator</returns>
/// <exception cref="SnowflakeDbException">when authenticator is unknown</exception>
internal static IAuthenticator GetAuthenticator(SFSession session)
{
string type = session.properties[SFSessionProperty.AUTHENTICATOR];
if (type.Equals(BasicAuthenticator.AUTH_NAME, StringComparison.InvariantCultureIgnoreCase))
{
return(new BasicAuthenticator(session));
}
else if (type.Equals(ExternalBrowserAuthenticator.AUTH_NAME, StringComparison.InvariantCultureIgnoreCase))
{
return(new ExternalBrowserAuthenticator(session));
}
else if (type.Equals(KeyPairAuthenticator.AUTH_NAME, StringComparison.InvariantCultureIgnoreCase))
{
// Get private key path or private key from connection settings
if (!session.properties.TryGetValue(SFSessionProperty.PRIVATE_KEY_FILE, out var pkPath) &&
!session.properties.TryGetValue(SFSessionProperty.PRIVATE_KEY, out var pkContent))
{
// There is no PRIVATE_KEY_FILE defined, can't authenticate with key-pair
string invalidStringDetail =
"Missing required PRIVATE_KEY_FILE or PRIVATE_KEY for key pair authentication";
var error = new SnowflakeDbException(
SFError.INVALID_CONNECTION_STRING,
new object[] { invalidStringDetail });
logger.Error(error.Message, error);
throw error;
}
return(new KeyPairAuthenticator(session));
}
else if (type.Equals(OAuthAuthenticator.AUTH_NAME, StringComparison.InvariantCultureIgnoreCase))
{
// Get private key path or private key from connection settings
if (!session.properties.TryGetValue(SFSessionProperty.TOKEN, out var pkPath))
{
// There is no TOKEN defined, can't authenticate with oauth
string invalidStringDetail =
"Missing required TOKEN for Oauth authentication";
var error = new SnowflakeDbException(
SFError.INVALID_CONNECTION_STRING,
new object[] { invalidStringDetail });
logger.Error(error.Message, error);
throw error;
}
return(new OAuthAuthenticator(session));
}
// Okta would provide a url of form: https://xxxxxx.okta.com or https://xxxxxx.oktapreview.com
else if ((type.EndsWith("okta.com") || type.EndsWith("oktapreview.com")) &&
type.StartsWith("https://"))
{
return(new OktaAuthenticator(session, type));
}
var e = new SnowflakeDbException(SFError.UNKNOWN_AUTHENTICATOR, type);
logger.Error("Unknown authenticator", e);
throw e;
}
private void FilterFailedResponse(BaseRestResponse response)
{
if (!response.success)
{
SnowflakeDbException e = new SnowflakeDbException("", response.code, response.message, "");
logger.Error("Authentication failed", e);
throw e;
}
}
internal void FilterFailedResponse()
{
if (!success)
{
SnowflakeDbException e = new SnowflakeDbException("",code, message, "");
throw e;
}
}
private void VerifyUrls(Uri tokenOrSsoUrl, Uri sessionUrl)
{
if (tokenOrSsoUrl.Scheme != sessionUrl.Scheme || tokenOrSsoUrl.Host != sessionUrl.Host)
{
var e = new SnowflakeDbException(
SFError.IDP_SSO_TOKEN_URL_MISMATCH, tokenOrSsoUrl.ToString(), oktaUrl.ToString());
logger.Error("Different urls", e);
throw e;
}
}
internal static SFSessionProperties parseConnectionString(String connectionString, SecureString password)
{
logger.Info("Start parsing connection string.");
SFSessionProperties properties = new SFSessionProperties();
string[] propertyEntry = connectionString.Split(';');
foreach (string keyVal in propertyEntry)
{
if (keyVal.Length > 0)
{
string[] token = keyVal.Split(new string[] { "=" }, StringSplitOptions.None);
if (token.Length == 2)
{
try
{
SFSessionProperty p = (SFSessionProperty)Enum.Parse(
typeof(SFSessionProperty), token[0].ToUpper());
properties.Add(p, token[1]);
logger.Info($"Connection property: {p}, value: {(p == SFSessionProperty.PASSWORD ? "XXXXXXXX" : token[1])}");
}
catch (ArgumentException e)
{
logger.Warn($"Property {token[0]} not found ignored.");
}
}
else
{
string invalidStringDetail = String.Format("Invalid kay value pair {0}", keyVal);
SnowflakeDbException e = new SnowflakeDbException(SFError.INVALID_CONNECTION_STRING,
new object[] { invalidStringDetail });
logger.Error("Invalid string.", e);
throw e;
}
}
}
if (password != null)
{
properties[SFSessionProperty.PASSWORD] = new NetworkCredential(string.Empty, password).Password;
}
checkSessionProperties(properties);
// compose host value if not specified
if (!properties.ContainsKey(SFSessionProperty.HOST))
{
string hostName = String.Format("%s.snowflakecomputing.com", properties[SFSessionProperty.ACCOUNT]);
properties.Add(SFSessionProperty.HOST, hostName);
logger.Info($"Compose host name: {hostName}");
}
return(properties);
}
internal void cancel()
{
if (this.requestId == null)
{
logger.Info("No query to be cancelled.");
return;
}
UriBuilder uriBuilder = new UriBuilder();
uriBuilder.Scheme = sfSession.properties[SFSessionProperty.SCHEME];
uriBuilder.Host = sfSession.properties[SFSessionProperty.HOST];
uriBuilder.Port = Int32.Parse(sfSession.properties[SFSessionProperty.PORT]);
uriBuilder.Path = SF_QUERY_CANCEL_PATH;
var queryString = HttpUtility.ParseQueryString(string.Empty);
queryString[SF_QUERY_REQUEST_ID] = Guid.NewGuid().ToString();
uriBuilder.Query = queryString.ToString();
QueryCancelRequest postBody = new QueryCancelRequest()
{
requestId = this.requestId
};
SFRestRequest cancelRequest = new SFRestRequest();
cancelRequest.uri = uriBuilder.Uri;
cancelRequest.authorizationToken = String.Format(SF_AUTHORIZATION_SNOWFLAKE_FMT, sfSession.sessionToken);
cancelRequest.jsonBody = postBody;
NullDataResponse cancelResponse = restRequest.post(cancelRequest).ToObject <NullDataResponse>();
if (cancelResponse.success)
{
logger.Info("Query cancellation succeed");
}
else
{
SnowflakeDbException e = new SnowflakeDbException(
"", cancelResponse.code, cancelResponse.message, "");
logger.Error("Query cancellation failed.", e);
throw e;
}
}
internal void ProcessLoginResponse(LoginResponse authnResponse)
{
if (authnResponse.success)
{
sessionToken = authnResponse.data.token;
masterToken = authnResponse.data.masterToken;
database = authnResponse.data.authResponseSessionInfo.databaseName;
schema = authnResponse.data.authResponseSessionInfo.schemaName;
serverVersion = authnResponse.data.serverVersion;
UpdateSessionParameterMap(authnResponse.data.nameValueParameter);
}
else
{
SnowflakeDbException e = new SnowflakeDbException("", authnResponse.code, authnResponse.message, "");
logger.Error("Authentication failed", e);
throw e;
}
}
/// <summary>
/// Generate the authenticator given the session
/// </summary>
/// <param name="session">session that requires the authentication</param>
/// <returns>authenticator</returns>
/// <exception cref="SnowflakeDbException">when authenticator is unknown</exception>
internal static IAuthenticator GetAuthenticator(SFSession session)
{
string type = session.properties[SFSessionProperty.AUTHENTICATOR];
if (type == "snowflake")
{
return(new BasicAuthenticator(session));
}
// Okta would provide a url of form: https://xxxxxx.okta.com
else if (type.EndsWith("okta.com") && type.StartsWith("https://"))
{
return(new OktaAuthenticator(session, type));
}
var e = new SnowflakeDbException(SFError.UNKNOWN_AUTHENTICATOR, type);
logger.Error("Unknown authenticator", e);
throw e;
}
private void parseLoginResponse(JObject response)
{
AuthnResponse authnResponse = response.ToObject <AuthnResponse>();
if (authnResponse.success)
{
sessionToken = authnResponse.data.token;
masterToken = authnResponse.data.masterToken;
database = authnResponse.data.authResponseSessionInfo.databaseName;
schema = authnResponse.data.authResponseSessionInfo.schemaName;
serverVersion = authnResponse.data.serverVersion;
updateParameterMap(parameterMap, authnResponse.data.nameValueParameter);
}
else
{
SnowflakeDbException e = new SnowflakeDbException("", authnResponse.code, authnResponse.message, "");
logger.Error("Authentication failed", e);
throw e;
}
}
internal void renewSession()
{
UriBuilder uriBuilder = new UriBuilder();
uriBuilder.Scheme = properties[SFSessionProperty.SCHEME];
uriBuilder.Host = properties[SFSessionProperty.HOST];
uriBuilder.Port = Int32.Parse(properties[SFSessionProperty.PORT]);
uriBuilder.Path = SF_TOKEN_REQUEST_PATH;
var queryString = HttpUtility.ParseQueryString(string.Empty);
queryString[SF_QUERY_REQUEST_ID] = Guid.NewGuid().ToString();
uriBuilder.Query = queryString.ToString();
RenewSessionRequest postBody = new RenewSessionRequest()
{
oldSessionToken = this.sessionToken,
requestType = "RENEW"
};
SFRestRequest renewSessionRequest = new SFRestRequest();
renewSessionRequest.jsonBody = postBody;
renewSessionRequest.uri = uriBuilder.Uri;
renewSessionRequest.authorizationToken = String.Format(SF_AUTHORIZATION_SNOWFLAKE_FMT, masterToken);
renewSessionRequest.sfRestRequestTimeout = -1;
JObject response = restRequest.post(renewSessionRequest);
NullDataResponse sessionRenewResponse = response.ToObject <NullDataResponse>();
if (!sessionRenewResponse.success)
{
SnowflakeDbException e = new SnowflakeDbException("",
sessionRenewResponse.code, sessionRenewResponse.message, "");
logger.Error("Renew session failed", e);
throw e;
}
}
private static void checkSessionProperties(SFSessionProperties properties)
{
foreach (SFSessionProperty sessionProperty in Enum.GetValues(typeof(SFSessionProperty)))
{
// if required property, check if exists in the dictionary
if (IsRequired(sessionProperty, properties) &&
!properties.ContainsKey(sessionProperty))
{
SnowflakeDbException e = new SnowflakeDbException(SFError.MISSING_CONNECTION_PROPERTY,
sessionProperty);
logger.Error("Missing connetion property", e);
throw e;
}
// add default value to the map
string defaultVal = sessionProperty.GetAttribute <SFSessionPropertyAttr>().defaultValue;
if (defaultVal != null && !properties.ContainsKey(sessionProperty))
{
logger.Debug($"Sesssion property {sessionProperty} set to default value: {defaultVal}");
properties.Add(sessionProperty, defaultVal);
}
}
}
internal void Cancel()
{
SFRestRequest request = BuildCancelQueryRequest();
if (request == null)
{
return;
}
var response = _restRequest.Post <NullDataResponse>(request);
if (response.success)
{
logger.Info("Query cancellation succeed");
}
else
{
SnowflakeDbException e = new SnowflakeDbException(
"", response.code, response.message, "");
logger.Error("Query cancellation failed.", e);
throw e;
}
}
internal static SFSessionProperties parseConnectionString(String connectionString, SecureString password)
{
logger.Info("Start parsing connection string.");
SFSessionProperties properties = new SFSessionProperties();
string[] propertyEntry = connectionString.Split(';');
foreach (string keyVal in propertyEntry)
{
if (keyVal.Length > 0)
{
string[] tokens = keyVal.Split(new string[] { "=" }, StringSplitOptions.None);
if (tokens.Length != 2)
{
// https://docs.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbconnection.connectionstring
// To include an equal sign (=) in a keyword or value, it must be preceded
// by another equal sign. For example, in the hypothetical connection
// string "key==word=value" :
// the keyword is "key=word" and the value is "value".
int currentIndex = 0;
int singleEqualIndex = -1;
while (currentIndex <= keyVal.Length)
{
currentIndex = keyVal.IndexOf("=", currentIndex);
if (-1 == currentIndex)
{
// No '=' found
break;
}
if ((currentIndex < (keyVal.Length - 1)) &&
('=' != keyVal[currentIndex + 1]))
{
if (0 > singleEqualIndex)
{
// First single '=' encountered
singleEqualIndex = currentIndex;
currentIndex++;
}
else
{
// Found another single '=' which is not allowed
singleEqualIndex = -1;
break;
}
}
else
{
// skip the doubled one
currentIndex += 2;
}
}
if ((singleEqualIndex > 0) && (singleEqualIndex < keyVal.Length - 1))
{
// Split the key/value at the right index and deduplicate '=='
tokens = new string[2];
tokens[0] = keyVal.Substring(0, singleEqualIndex).Replace("==", "=");
tokens[1] = keyVal.Substring(
singleEqualIndex + 1,
keyVal.Length - (singleEqualIndex + 1)).Replace("==", "=");;
}
else
{
// An equal sign was not doubled or something else happened
// making the connection invalid
string invalidStringDetail =
String.Format("Invalid key value pair {0}", keyVal);
SnowflakeDbException e =
new SnowflakeDbException(SFError.INVALID_CONNECTION_STRING,
new object[] { invalidStringDetail });
logger.Error("Invalid string.", e);
throw e;
}
}
try
{
SFSessionProperty p = (SFSessionProperty)Enum.Parse(
typeof(SFSessionProperty), tokens[0].ToUpper());
properties.Add(p, tokens[1]);
logger.Info($"Connection property: {p}, value: {(secretProps.Contains(p) ? "XXXXXXXX" : tokens[1])}");
}
catch (ArgumentException e)
{
logger.Warn($"Property {tokens[0]} not found ignored.", e);
}
}
}
if (password != null)
{
properties[SFSessionProperty.PASSWORD] = new NetworkCredential(string.Empty, password).Password;
}
checkSessionProperties(properties);
// compose host value if not specified
if (!properties.ContainsKey(SFSessionProperty.HOST) ||
(0 == properties[SFSessionProperty.HOST].Length))
{
string hostName = String.Format("{0}.snowflakecomputing.com", properties[SFSessionProperty.ACCOUNT]);
// Remove in case it's here but empty
properties.Remove(SFSessionProperty.HOST);
properties.Add(SFSessionProperty.HOST, hostName);
logger.Info($"Compose host name: {hostName}");
}
return(properties);
}
internal SFBaseResultSet execute(string sql, Dictionary <string, BindingDTO> bindings, bool describeOnly)
{
if (requestId != null)
{
logger.Info("Another query is running.");
throw new SnowflakeDbException(SFError.STATEMENT_ALREADY_RUNNING_QUERY);
}
this.requestId = Guid.NewGuid().ToString();
UriBuilder uriBuilder = new UriBuilder();
uriBuilder.Scheme = sfSession.properties[SFSessionProperty.SCHEME];
uriBuilder.Host = sfSession.properties[SFSessionProperty.HOST];
uriBuilder.Port = Int32.Parse(sfSession.properties[SFSessionProperty.PORT]);
uriBuilder.Path = SF_QUERY_PATH;
var queryString = HttpUtility.ParseQueryString(string.Empty);
queryString[SF_QUERY_REQUEST_ID] = requestId;
uriBuilder.Query = queryString.ToString();
QueryRequest postBody = new QueryRequest()
{
sqlText = sql,
parameterBindings = bindings,
describeOnly = describeOnly,
};
SFRestRequest queryRequest = new SFRestRequest();
queryRequest.uri = uriBuilder.Uri;
queryRequest.authorizationToken = String.Format(SF_AUTHORIZATION_SNOWFLAKE_FMT, sfSession.sessionToken);
queryRequest.jsonBody = postBody;
queryRequest.httpRequestTimeout = -1;
try
{
JObject rawResponse = restRequest.post(queryRequest);
QueryExecResponse execResponse = rawResponse.ToObject <QueryExecResponse>();
if (execResponse.code == SF_SESSION_EXPIRED_CODE)
{
sfSession.renewSession();
this.execute(sql, bindings, describeOnly);
}
else if (execResponse.code == SF_QUERY_IN_PROGRESS ||
execResponse.code == SF_QUERY_IN_PROGRESS_ASYNC)
{
logger.Info("Query execution in progress.");
bool isSessionRenewed = false;
string getResultUrl = null;
while (execResponse.code == SF_QUERY_IN_PROGRESS ||
execResponse.code == SF_QUERY_IN_PROGRESS_ASYNC)
{
if (!isSessionRenewed)
{
getResultUrl = execResponse.data.getResultUrl;
}
UriBuilder getResultUriBuilder = new UriBuilder();
getResultUriBuilder.Scheme = sfSession.properties[SFSessionProperty.SCHEME];
getResultUriBuilder.Host = sfSession.properties[SFSessionProperty.HOST];
getResultUriBuilder.Port = Int32.Parse(sfSession.properties[SFSessionProperty.PORT]);
getResultUriBuilder.Path = getResultUrl;
SFRestRequest getResultRequest = new SFRestRequest()
{
uri = getResultUriBuilder.Uri,
authorizationToken = String.Format(SF_AUTHORIZATION_SNOWFLAKE_FMT, sfSession.sessionToken)
};
getResultRequest.httpRequestTimeout = -1;
execResponse = null;
execResponse = restRequest.get(getResultRequest).ToObject <QueryExecResponse>();
if (execResponse.code == SF_SESSION_EXPIRED_CODE)
{
logger.Info("Ping pong request failed with session expired, trying to renew the session.");
sfSession.renewSession();
isSessionRenewed = true;
}
else
{
isSessionRenewed = false;
}
}
}
if (execResponse.success)
{
return(new SFResultSet(execResponse.data, this));
}
else
{
SnowflakeDbException e = new SnowflakeDbException(
execResponse.data.sqlState, execResponse.code, execResponse.message,
execResponse.data.queryId);
logger.Error("Query execution failed.", e);
throw e;
}
}
finally
{
this.requestId = null;
}
}
