Snowflake LEGACY_SERVICE Users: Find Password Logins Now

To find Snowflake LEGACY_SERVICE users, query SNOWFLAKE.ACCOUNT_USAGE.USERS for rows where the type is LEGACY_SERVICE or has_password is true. Then check SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY for successful logins that used a password with no second factor. Anything that appears in both lists is a pipeline credential that will fail when Snowflake finishes blocking single-factor password sign-ins. Both queries are below and run in under five minutes.

Most data teams find out about this the wrong way. The nightly dbt run fails with an authentication error, the loader that feeds the warehouse stops, and someone discovers that the credential was created three years ago by a person who has since left. This post covers detection first, then the migration, then the guardrail that stops it recurring.

Why Is Snowflake Blocking Password Logins for Service Accounts?

Snowflake is removing single-factor password authentication for both human and service users. The change follows a wave of attacks in 2024 that targeted more than 100 Snowflake customer environments without MFA. For service users the intended end state is that they are blocked from password-based login entirely, and only OAuth or key-pair authentication is allowed.

The dates have moved, so do not rely on a blog post's calendar, including this one. One recent summary says Snowflake revised its timeline in October 2025, with the final milestone for human MFA and the LEGACY_SERVICE deprecation falling between August and October 2026. Other summaries list different windows for individual milestones. The practical position is that the enforcement window is open now. Whether your account is already affected depends on which behaviour change bundles are enabled. Treat every password-authenticated pipeline user as a failure waiting to happen.

LEGACY_SERVICE is the transitional user type. It exists for scripts, ETL tools and BI connectors that still log in with a password. A pattern we see repeatedly in early-stage companies is that these users accumulate quietly. A connector gets set up, the password goes into a shared vault or a .env file, and nobody owns the credential afterwards.

How Do You Find Snowflake LEGACY_SERVICE Users With Password Logins?

Start with the inventory. This query lists every active user that still has a password set, with its type and last login. You need the SECURITY_VIEWER database role, or IMPORTED PRIVILEGES on the SNOWFLAKE database.

SELECT
    u.name,
    COALESCE(u.type, 'PERSON')          AS user_type,
    u.has_password,
    u.has_rsa_public_key,
    u.default_role,
    u.created_on,
    u.last_success_login
FROM snowflake.account_usage.users u
WHERE u.deleted_on IS NULL
  AND COALESCE(u.disabled::STRING, 'false') = 'false'
  AND (u.type = 'LEGACY_SERVICE' OR u.has_password::STRING = 'true')
ORDER BY user_type, u.last_success_login DESC;

This gives you the list of credentials that exist. It does not tell you which ones are in use. For that, query the login history. This version looks at the last 30 days of successful logins that used a password with no second factor:

SELECT
    l.user_name,
    COALESCE(u.type, 'PERSON')          AS user_type,
    l.reported_client_type,
    l.first_authentication_factor,
    l.second_authentication_factor,
    COUNT(*)                            AS successful_logins,
    MAX(l.event_timestamp)              AS last_login
FROM snowflake.account_usage.login_history l
LEFT JOIN snowflake.account_usage.users u
       ON u.name = l.user_name AND u.deleted_on IS NULL
WHERE l.event_timestamp >= DATEADD('day', -30, CURRENT_TIMESTAMP())
  AND l.is_success = 'YES'
  AND l.first_authentication_factor = 'PASSWORD'
  AND l.second_authentication_factor IS NULL
GROUP BY 1, 2, 3, 4, 5
ORDER BY successful_logins DESC;

Read the output like this:

• Driver clients with steady daily counts (Python, JDBC, ODBC, or your ELT tool) are pipelines. These are your migration priority.
• SNOWFLAKE_UI rows are humans. They need MFA enrolment, not key pairs.
• Users with has_password = true and no login in 90 days are dormant credentials. Disable them. Do not migrate them.

Account usage views lag real time, typically by a couple of hours, so run the check from a stable moment rather than straight after a change. If you are already auditing privileged access, this is the same view family we use in the Snowflake ACCOUNTADMIN audit.

Which Password Logins Will Actually Break Something?

Not every row needs the same urgency. When we run this triage with a client, we sort each user into one of four buckets:

1. Live pipeline, owner known. Migrate to key pair this sprint.
2. Live pipeline, owner unknown. The riskiest bucket. Find the client by joining reported_client_type with query history, or by checking which warehouse and role it uses. Then migrate.
3. Human on a password, no MFA. Enrol in MFA, or move them to SSO.
4. Dormant. Disable now and delete after a quarantine period.

In one engagement with a growth-stage payments company, the second bucket was bigger than anyone expected. Several loader credentials had no documented owner. Finding them took one afternoon with the queries above. Finding them after an outage would have meant hours of reading connector configs with finance waiting on the numbers.

Running those queries for one account is fine. Checking every repo is the other half of the problem, because passwords tend to sit in committed profiles.yml files and CI variables. fintel-scan is a free, open-source (MIT) CLI that runs fifteen checks across a dbt project locally, with no warehouse connection: uvx fintel-scan. It does not replace the warehouse-side queries above, which need an account connection.

How Do You Migrate Snowflake LEGACY_SERVICE Users to Key-Pair Authentication?

Do the migration in this order so there is never a moment when the pipeline has no valid credential.

Step 1: generate the key pair. Do this on a secure machine, not in the repo:

openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Step 2: attach the public key while the password still works:

ALTER USER etl_loader SET RSA_PUBLIC_KEY = 'MIIBIjANBgkq...';

Step 3: switch the client. For dbt, update the target in profiles.yml to use the private key and remove the password. Store the key and passphrase in your secrets manager and inject them at runtime:

my_project:
  target: prod
  outputs:
    prod:
      type: snowflake
      account: my_account
      user: etl_loader
      role: transformer
      warehouse: transforming
      database: analytics
      schema: dbt_prod
      private_key_path: /secrets/rsa_key.p8
      private_key_passphrase: "{{ env_var('SNOWFLAKE_KEY_PASSPHRASE') }}"

Step 4: verify. Re-run the login history query after a full pipeline cycle. The user should no longer appear with PASSWORD as its first factor.

Step 5: remove the password and convert the type:

ALTER USER etl_loader UNSET PASSWORD;
ALTER USER etl_loader SET TYPE = SERVICE;

Check Snowflake's current documentation for the exact conversion behaviour in your account before running step 5 in production. Rotate keys on a schedule, and give each pipeline its own user. Shared service users are what make the owner-unknown bucket in the first place.

How Do You Stop Password Logins Coming Back?

Migration without a guardrail is a one-off clean-up. Within two quarters someone will create a new user with a password because it is faster. Two controls stop this.

An authentication policy on service users. This blocks anything except key pair or OAuth, so a stray password cannot be used even if it is set:

CREATE AUTHENTICATION POLICY service_keypair_only
  AUTHENTICATION_METHODS = ('KEYPAIR', 'OAUTH')
  CLIENT_TYPES = ('DRIVERS');

ALTER USER etl_loader SET AUTHENTICATION POLICY = service_keypair_only;

Test it on one non-critical user first. A policy that locks out a production loader is just a self-inflicted outage.

A failing test. In dbt, a singular test turns the audit into something that runs on a schedule. Save this as tests/assert_no_legacy_service_users.sql:

SELECT name, created_on
FROM snowflake.account_usage.users
WHERE deleted_on IS NULL
  AND type = 'LEGACY_SERVICE'

This query hardcodes a system view rather than using a source(). That is a deliberate exception, because these views are not part of your project. Run it in its own scheduled job, since the role running your main build may not have access to account usage. Teams that treat security checks as code find this class of problem while it is still a ticket and not an incident.

Frequently Asked Questions

Q: What is a LEGACY_SERVICE user in Snowflake?

A: LEGACY_SERVICE is a transitional user type for programmatic clients, such as ETL tools and scripts, that still authenticate with a password. It is being deprecated. The replacement is the SERVICE type, which authenticates with key pair, OAuth or similar non-password methods.

Q: How do I find Snowflake users who log in with only a password?

A: Query SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY for successful logins where first_authentication_factor is PASSWORD and second_authentication_factor is NULL. Join to ACCOUNT_USAGE.USERS to see each user's type. Group by user and client type to separate pipelines from humans.

Q: Will my dbt Cloud or dbt Core jobs break when password logins are blocked?

A: They will if the connection uses a password with a LEGACY_SERVICE or password-only user. Switch the connection to key-pair authentication with a private key and passphrase held in your secrets manager. Then confirm the change in login history before removing the password.

Q: Do I need MFA for Snowflake service accounts?

A: No. Service accounts are not meant to use MFA. They should use key-pair, OAuth or another non-interactive method. MFA applies to human users who sign in with a password.

Q: How long does a Snowflake LEGACY_SERVICE migration take?

A: For a typical growth-stage account with a handful of pipeline users, the technical work is small: a key pair, a profile change and a verification run per user. Most of the time goes on finding who owns each credential, which is why the login history audit comes first.

What to Do This Week

Run the two queries, sort every password login into the four buckets, and migrate the live pipelines first. Then attach the authentication policy so the problem cannot return quietly. The only way the migration goes badly is if you discover your Snowflake LEGACY_SERVICE users from a failed run at 6am.

At Fintel Analytics, we have helped fintech, payments and e-commerce teams audit warehouse access, migrate pipeline credentials and put security checks into their dbt projects as tests. If you suspect your account has credentials nobody owns, an audit now is far cheaper than an outage later, and we can have the inventory in your hands within days.