dbt run fails with "The specified schema name userid@domain either does not exist or you do not have permission to use it

[jhoolachan]

Hello,

When I create a schema manually, add it to my profiles.yml, and then submit "dbt run", my model is built successfully. However, if the schema specified in my profiles.yml does not exist, the build fails with:

"The specified schema name 'userid@domain' either does not exist or you do not have permission to use it" "CREATE SCHEMA failed due to previous errors" when active directory integrated authentication is used. This feels like a DB permissions issue but I am able to log into SSMS using active directory integrated and create schemas without issue. I am also not sure why my userid and domain are being concatenated for the schema name when my profiles.yml contains "schema: staging".

I'm running dbt=0.19.2. What additional information can I provide for troubleshooting?

[dataders]

@jhoolachan thanks for putting in the work to get this set up!

more info:

1. what MSFT SQL product are you using? SQL Server (on-prem or IaaS) or Azure SQL?
2. what does a redacted version of your profiles.yml look like? It kinda seems like you have your user and schema` fields switched.

Full disclosure: I've never personally gotten active directory integrated authentication to work, as my company uses Azure Active Directory, and it is a lot of work to set that up in a demo environment. If you or your company uses is Azure Active Directory, I definitely recommend the cli auth method.

[jhoolachan]

@swanderz

I'm using Azure SQL and my profiles.yml is below:

azure-sql-demo:
  target: dev
  outputs:
    dev:
      type: sqlserver
      driver: 'ODBC Driver 17 for SQL Server'
      server: {server}
      port: 1433
      database: {db}
      schema: staging
      authentication: ActiveDirectoryIntegrated
      encrypt: true

I also see the same issue when using service principal authentication. In that scenario, the compiled schema is : client_id@tenant_id

As noted, both methods work without issue as long as the schema already exists. I have not tried the CLI auth method but can give it a shot.

[dataders]

I also see the same issue when using service principal authentication. In that scenario, the compiled schema is : client_id@tenant_id

weird! it might be that 'userid@domain' or client_id@tenant_idaren't properly quoted?

I highly recommend the cli auth option, then get started after that.

p.s. if you're interested in getting started with dbt & Azure SQL, you might be interested in this guide that @chaerinlee1 made.

[jhoolachan]

The guide is great; thanks for that!

I was able to successfully connect via the CLI auth but I'm still facing the same error. My updated profile.yml is below.

azure-sql-demo-cli:
  target: dev
  outputs:
    dev:
      type: sqlserver
      driver: 'ODBC Driver 17 for SQL Server'
      server: {server}
      port: 1433
      database: BGEHRPTDCLD-DB-01
      schema: new_schema
      authentication: CLI

The error is the same as when connecting via AAD Integrated:

"The specified schema name 'userid@domain' either does not exist or you do not have permission to use it" "CREATE SCHEMA failed due to previous errors"

[dataders]

The error is the same as when connecting via AAD Integrated:

"The specified schema name 'userid@domain' either does not exist or you do not have permission to use it" "CREATE SCHEMA failed due to previous errors"

1. is userid@domain' anywhere in your profiles.yml? I'm confused now.
2. when you call dbt debug does everything run ok?

[jhoolachan]

1. Nope, neither my userid or company domain are present in the profiles.yml - but those are the credentials I use during authentication
2. dbt debug looks good

Running with dbt=0.19.2
dbt version: 0.19.2
python version: 3.7.9
python path: c:\users\****\anaconda3\envs\dbt\python.exe
os info: Windows-10-10.0.18362-SP0
Using profiles.yml file at C:\Users\****\.dbt\profiles.yml
Using dbt_project.yml file at B:\****\dbt_project.yml

Configuration:
  profiles.yml file [OK found and valid]
  dbt_project.yml file [OK found and valid]

Required dependencies:
 - git [OK found]

Connection:
  server: ****
  database: ****
  schema: new_schema
  port: 1433
  UID: None
  client_id: None
  authentication: CLI
  encrypt: False
  trust_cert: False
  Connection test: OK connection ok

[dataders]

1. are you using the Azure SQL database that isn't master?
2. can you log-into the database using SSMS using the "MFA + interactive auth" option (the one the opens a browser pop up to login w/ your creds)
3. are you 100% confident that userid@domain doesn't show up anywhere in your dbt project folder?

[jhoolachan]

1. Edited: I can see master but have specified a different DB in my profiles.yml
2. I cannot. Per our cloud team, the only login we can use in SSMS is AAD - Integrated
3. Yes. It is a very basic project with only a handful of sources and models

Edit: I saw your note to move it to tsql-utils. Is that functionality available in GitHub or do you just mean re-post there?

[dataders]

Edit: I saw your note to move it to tsql-utils. Is that functionality available in GitHub or do you just mean re-post there?

yes, I think so, but let's have that discussion on the tsql-utls repo Issues page.

as for the main issue, now I'm rather stumped! if you DM me on the dbt Slack, I can give you test Azure SQL to play with (to see if the error persists).

Can you log into the db with SSMS, and execute CREATE SCHEMA new_schema without issue?

You might want to follow up with you cloud team's set up because in my view, using "Active Directory - Integrated" with Azure SQL must mean that your cloud team has:

• an on-premise Active Directory deployment,
• either Azure AD Connect or Active Directory Federation Services enabled
• someone who is the AAD admin of the Azure SQL server has already granted your identity the right permissions on the Azure SQL db

@alieus @NandanHegde15 any ideas what might be going on here?

[dataders]

another thing that would help is if you could go to the logs/dbt.log file and scroll down to the bottom where you're getting the error and share that here (or DM it to me) as a Gist. I'd be interested to see what SQL statement was trying to execute when you got the error message that you did.

[jhoolachan]

Ah! Running CREATE SCHEMA new_schema in SSMS fails with the same error. I forgot that I need to add CREATE SCHEMA new_schema AUTHORIZATION {DB user} where {DB user} is the security group that my login has access through. The schemas I've been using were created a while back and the AUTHORIZATION piece slipped my mind.

Is schema creation handled by a particular macro that I can modify to include the authorization clause?

[w0ut0]

We are having the same issue, but only when the AAD user is part of an AAD group, and is not the first user attempting this:

posted it on stackoverflow, can you confirm that you have the same setup @jhoolachan?

Also pasting my full error message, so this thread shows up on Google:

CREATE SCHEMA dbt_test

Msg 2760, Level 16, State 1, Line 1
The specified schema name "[email protected]" either does not exist or you do not have permission to use it.
Msg 2759, Level 16, State 0, Line 1
CREATE SCHEMA failed due to previous errors.

[w0ut0]

This happens because the user can not be created implicitly. If you configured your database like me, and provided permission to an Azure AD group (and not the user themselves), CREATE SCHEMA is not able to implicitly create the user.

It seems documented in the remarks section here.

[dataders]

updating scope bc #277 isn't working yet

[sdebruyn]

I did some testing for the use case of @w0ut0 and figured it out:

If we have #277, then we can set the authorization of the schema to the group you're in. That seems to work. You need a few permissions to create the schema and tables/views, but basically with #277 we should get there.

I documented the permissions in https://github.com/dbt-labs/docs.getdbt.com/pull/3378