How to find Snowflake service users without key-pair auth: Queries to find, fix and verify password-only service users

Snowflake service users that still sign in with a password are the exact weakness behind the 2024 customer data thefts, and Snowflake is now blocking those sign-ins account by account. This page gives the queries to find every non-human user that has no key pair or other strong credential, the login evidence that shows which integrations still send passwords, and the steps to move each one to key-pair authentication and TYPE = SERVICE without breaking pipelines. It is written for Snowflake administrators and data platform engineers working through the password deprecation. The wider design of service users, including workload identity federation and programmatic access tokens, is in the Service Accounts in Snowflake Guide; this page is the audit and fix runbook.

Key takeaways

• Combine three views. SNOWFLAKE.ACCOUNT_USAGE.USERS says what credentials a user has, CREDENTIALS lists named key pairs and tokens, and LOGIN_HISTORY shows how the user actually authenticated. You need all three to be sure.
• Behaviour beats configuration. A user with both a password and a public key may still be signing in with the password. Look at FIRST_AUTHENTICATION_FACTOR over the last 90 days before declaring a user migrated.
• The deadline is rolling. Snowflake's estimated phase 3 (August to October 2026) blocks password authentication for non-human users and migrates LEGACY_SERVICE users to TYPE = SERVICE. Check your own account's enforcement notice and the Trust Center Strong authentication hub.
• Add the key before you change the type. Register a named key pair, switch the client, confirm key-pair logins, then set TYPE = SERVICE, which hides the password.
• Account usage lags. Both USERS and LOGIN_HISTORY can be up to two hours behind; use the LOGIN_HISTORY_BY_USER table function (last 7 days) for immediate checks after a change.

When you need this

• Your account has LEGACY_SERVICE users or untyped automation users. These can still hold passwords. Users with TYPE of PERSON or NULL that run pipelines are the hardest to spot, because they look human.
• The Strong authentication hub lists findings. Snowsight, Governance and security, Trust Center, Overview, Strong authentication progress, View hub, flags LEGACY_SERVICE users and users logging in with only a password from applications such as Power BI in the last 90 days. Viewing it requires the SNOWFLAKE.TRUST_CENTER_ADMIN or SNOWFLAKE.TRUST_CENTER_VIEWER application role.
• You are responding to credential theft. In the Snowflake breach, attackers replayed infostealer-harvested usernames and passwords, some from 2020, against accounts with no MFA and no network allow list; about 165 organisations were notified.

Before you start

Step-by-step

1. List non-human and suspect users and their configured credentials.

   SELECT name, type, has_password, has_rsa_public_key, has_pat,
          has_workload_identity, has_mfa, default_role, owner,
          last_success_login, comment
   FROM snowflake.account_usage.users
   WHERE deleted_on IS NULL
     AND (disabled IS NULL OR disabled::string = 'false')
     AND (type IN ('SERVICE', 'LEGACY_SERVICE')
          OR (COALESCE(type, 'PERSON') = 'PERSON' AND has_mfa = FALSE))
   ORDER BY type, name;

   HAS_PASSWORD is NULL for TYPE = SERVICE users, because Snowflake does not allow them passwords. Human-typed users without MFA are included because many pipelines run under them.
2. Add named key pairs and other credentials from the CREDENTIALS view.

   SELECT user_name, type, name, status, created_on
   FROM snowflake.account_usage.credentials
   WHERE domain IN ('KEYPAIR', 'PROGRAMMATIC_ACCESS_TOKEN', 'WORKLOAD_IDENTITY_FEDERATION_METHOD')
     AND status = 'ACTIVE';

   The view's TYPE values include KEYPAIR, PAT, and AWS, AZURE, GCP or OIDC for workload identity federation. Join it to step 1 on user_name = name. A user is a candidate when it has no active KEYPAIR row, HAS_RSA_PUBLIC_KEY is false, and it has no workload identity.
3. Find how each user actually signs in.

   SELECT user_name, first_authentication_factor, reported_client_type,
          COUNT(*) AS logins, MAX(event_timestamp) AS last_seen,
          ARRAY_AGG(DISTINCT client_ip) AS source_ips
   FROM snowflake.account_usage.login_history
   WHERE event_timestamp > DATEADD(day, -90, CURRENT_TIMESTAMP())
     AND is_success = 'YES'
   GROUP BY 1, 2, 3
   ORDER BY user_name, logins DESC;

   Run SELECT DISTINCT first_authentication_factor on the view first to see the exact values in your account; Snowflake's PAT documentation confirms PROGRAMMATIC_ACCESS_TOKEN as one of them. Any non-human user whose first factor is the password value is an integration that will break at enforcement. reported_client_type tells you which driver or tool to change.
4. Triage the list. Sort candidates into: no logins in 90 days (disable and plan removal, see the NHI Dormancy Detection guide); active with password (migrate now); active with a key already but password still set (switch type); human users running automation (create a new TYPE = SERVICE user rather than converting a person).
5. Generate and register a key pair for each active user. Use RSA of at least 2048 bits; Snowflake recommends an encrypted PKCS#8 private key with AES-256-CBC rather than Triple DES. Then:

   GRANT MODIFY PROGRAMMATIC AUTHENTICATION METHODS ON USER etl_orders_svc
     TO ROLE svc_provisioner;
   ALTER USER etl_orders_svc ADD KEY PAIR orders_2026
     PUBLIC_KEY = '<public_key_body>';

   Named key pairs support role restriction and expiry; the legacy SET RSA_PUBLIC_KEY property supports neither.
6. Switch the client. Update the connector to key-pair authentication with the private key loaded from the secrets manager. Keep the password in place until key-pair logins are confirmed.
7. Change the user type.

   ALTER USER etl_orders_svc SET TYPE = SERVICE;

   Snowflake keeps incompatible properties such as the password but hides them; switching back to PERSON restores them. After phase 2 enforcement, LEGACY_SERVICE is no longer a valid value for CREATE USER or ALTER USER, so this change is one way for those users.

Choosing the target credential for each finding

Smaller accounts with a handful of integrations can work through the list by hand in a day. Large accounts with hundreds of users should export steps 1 to 3 into a tracker, assign each row to the owning team, and migrate by connector type so one fix (for example a BI gateway setting) clears many users at once.

Verify it worked

• Fingerprint check: compare RSA_PUBLIC_KEY_FP from DESC USER (for legacy keys) or the output of SHOW USER KEY PAIRS FOR USER etl_orders_svc with the SHA-256 fingerprint of your public key.
• Immediate login evidence:

  SELECT event_timestamp, first_authentication_factor, client_ip, is_success
  FROM TABLE(information_schema.login_history_by_user(
         USER_NAME => 'ETL_ORDERS_SVC', RESULT_LIMIT => 50))
  ORDER BY event_timestamp DESC;

  The table function covers the last 7 days without the account usage delay. You want only the key-pair factor after the cut-over.
• Configuration check: after up to two hours, rerun step 1; the user should show TYPE = SERVICE, HAS_PASSWORD NULL, and an active KEYPAIR row in CREDENTIALS.
• Hub check: the user disappears from the Trust Center Strong authentication hub when the scanners next update.

Rollback and common errors

Rollback plan: keep the password unchanged and the type unchanged until the key-pair login is proven in production. The type change is the commit point; until then, reverting the connector configuration restores the old behaviour. Remove a mistaken key with ALTER USER ... REMOVE KEY PAIR <name>.

Hardening afterwards

• Fence every service user with a network policy. A stolen private key should not work from outside the workload's network. Snowflake already requires a network policy before SERVICE or LEGACY_SERVICE users can generate or use a programmatic access token.
• Prefer workload identity federation where the workload runs in a cloud or CI system. It removes the private key altogether. See the Workload Identity Federation Guide.
• Restrict each key to one role and set expiry on named key pairs, and never grant service users ACCOUNTADMIN or SECURITYADMIN.
• Control who can mint credentials. Grant MODIFY PROGRAMMATIC AUTHENTICATION METHODS to a small provisioning role; anyone who holds it can give themselves a working credential for the service user.
• Schedule the queries. Run steps 1 to 3 weekly as a task and alert on any non-human password login or new user without a strong credential.

Practitioner checklist

• Run the user, credential and login history queries and save the output as the baseline.
• Check the Trust Center Strong authentication hub and your account's enforcement date.
• Disable non-human users with no logins in 90 days after confirming with owners.
• Register named, encrypted key pairs for every active password-using service user.
• Switch each client, confirm key-pair logins, then set TYPE = SERVICE.
• Replace human users that run automation with new TYPE = SERVICE users.
• Attach a network policy and a single-role restriction to every service user.
• Schedule the queries weekly and alert on any non-human password login.

Standards and references

• Snowflake Documentation: USERS view
• Snowflake Documentation: LOGIN_HISTORY view
• Snowflake Documentation: CREDENTIALS view
• Snowflake Documentation: Key-pair authentication and key-pair rotation
• Snowflake Documentation: Planning for the deprecation of single-factor password sign-ins
• Snowflake Documentation: ALTER USER
• Snowflake Documentation: Strong authentication hub
• OWASP: Non-Human Identities Top 10

Related NHI Mgmt Group resources: Service Accounts in Snowflake Guide · Snowflake Security Guide · NHI Dormancy Detection · Database Credential and Access Security Guide