Azure SQL Database and SQL Server CREATE USER syntax usage

PeterDanielsCRB

We use SQL Server 2019 Developer Edition for our local dev sandbox DBs, and we use Azure SQL Database for our test and prod DBs.  I want to make a migration that will create a USER from a windows (and AAD) group.  The syntax is different in Azure SQL DB and SQL Server, so the Azure SQL syntax of "FROM EXTERNAL USER" fails in our local dev SQL Servers....and the syntax that works in SQL Server fails in Azure.  Since it's a syntax issue, I can't just write some code to determine if the target DB is Azure SQL or SQL Server.

How are folks getting around this?

Thank you,

-Peter
 

Kendra_Little

Hi Peter,

As you've found, this is a limitation from Microsoft -- syntax for code that is specific to Azure SQL Database features can only be executed successfully against Azure SQL Database. 

This requirement means that if you do want to use Azure SQL Database-only features and be able to validate them well, the simplest solution is to use an Azure SQL Database to host your development infrastructure as well. (I suspect from the Microsoft perspective, if one wanted a local dev environment for Azure SQL Database their answer is that this is becoming available from an Azure Arc perspective. But that's not nearly as cost effective (free) as traditional Developer Edition.)

It is potentially possible to work around this with Dynamic SQL in some situations. For example in our CreateDatabase.sql script (in the provisioning folder), we use this code:

PRINT N'CUSTOM: Creating database [$(DatabaseName)]...';

GO

IF SERVERPROPERTY('EngineEdition') = 5

  EXEC sp_executesql N'CREATE DATABASE [$(DatabaseName)] (EDITION = ''Basic'');';

ELSE

  CREATE DATABASE [$(DatabaseName)];

GO

One could do something similar for External Table code potentially. Exactly how you would approach it might be dependent on whether you have things like views that depend on the external table or not.

Options might be leaving the External Table out of local environments, or perhaps creating a view instead of an external table in text environments?

One extra bit of info is that our SQL Compare team has just released support for External Tables, and this support will be moving into SQL Source Control and SQL Change Automation soon. If you are using SCA to generate migrations against a non-Azure SQL Database dev environment then I don't think this will impact you, but wanted to mention it just in case it comes up in some way and causes you some confusion.

Cheers,
Kendra

Kendra_Little

And sorry, reading this I see that it's EXTERNAL USER and not external tables.

I clearly have external tables on my mind!

I think overall the general notes /approach is the same. It might be complex if those user accounts own objects, but otherwise might be relatively simple.

PeterDanielsCRB

Thank you, Kendra.  I appreciate your reply and jiggling my noggin' by suggesting dynamic SQL.  I'm a little embarrassed that I didn't think of that. I'm going to roll with that method for now.  Thank you!

PeterDanielsCRB

Hey, @Kendra_Little - Quick follow-up. I started making some progress by using dynamic SQL that tested whether the SQL was executing in Azure SQL DB vs SQL Server by using:

IF SERVERPROPERTY('Edition') = 'Sql Azure' BEGIN

I tested it (with rollback) on my local dev SQL Server 2019 DB and on an Azure SQL DB.  However, when I committed my code, the CI build process (Azure DevOps Pipeline + Redgate SCA extensions) barfed on the fact that it couldn't recognize the AAD group.  I hacked the script even more by just skipping the CREATE USER code if this was just build validation on the CI server:

IF DB_NAME() NOT LIKE 'sql_change_automation_%' BEGIN (...)

That got me past the build, only to fail in the release deployment:

Principal '<mySecurityPrincipal>' could not be created. Only connections established with Active Directory accounts can create other Active Directory users.

 So now I'm faced with the task of figuring out how to adjust my deployment agent service account...or just giving up and getting this migration out of the project and handling it manually.  Leaning towards "B".

Thanks again for your help!

Cheers,

-Peter

Kendra_Little

Hi Peter,

Aha, I see! So to make this work, I believe you would need the ability to have Azure Active Directory authentication on the build task. 

We do support Azure Active Directory password authentication on building, but currently this option isn't available in the graphical plugin in Azure DevOps. If you are open to switching that step to a powershell step to do the build, you could test it out that way.

The powershell build would use New-DatabaseConnection to specify the authentication when connecting and pass this connection into Invoke-DatabaseBuild. Then you'd need to call New-DatabaseBuildArtifact followed by Export-DatabaseBuildArtifact.

Is that of interest to you? Totally  understand if you decide to go with plan b instead.

For longer term, we do have a prioritized ticket open to add Azure Active Directory Password authentication to the Azure DevOps build task itself based on a separate customer request. I added a comment to the ticket mentioning that you are an additional customer who would find this useful.

Cheers,
Kendra

SebastianHolc

Peter, just to double check: I understand that "Principal '<mySecurityPrincipal>' could not be created. [...]" error occurs during execution of the "Redgate SQL Change Automation: Release" task on your pipeline?

If so, what's the authentication method you've set for this task?

Adding Azure AD Password auth method to the Build task Kendra mentioned will make the

IF DB_NAME() NOT LIKE 'sql_change_automation_%'

workaround obsolete (assuming you actually want to create the users on a temporary database), however from what I understand you're having issue with the Release task at the moment.

PeterDanielsCRB

Thank you, Kendra and Sebastian. I'm rolling with plan B at this point, but happy to continue the conversation.

The initial error in the "Redgate SQL Change Automation Build" extension tasks execution was:

" Windows NT user or group '<myDomain>\<MyAADGroup>' not found. Check the name again."

(Note that the build extension config is using SQL LocalDB)

I do not see a place to change the auth method in the Build task...which agrees with Kendra's "...but currently this option isn't available in the graphical plugin in Azure DevOps".

To answer your question, Sebastian, yes - is was within the release extension that I hit the "Principal '<myAADgroup>' could not be created. Only connections established with Active Directory accounts can create other Active Directory users." error.

I do agree that switching the release extension task to AAD Password Auth would probably fix that issue, Sebastian.

Getting back to Kendra's comments...I used to do a lot of SCA CI (Bamboo and Jenkins) using the SCA powershell functions, but haven't done that in Azure DevOps yet. I may do a POC on a demo/POC project top see if I can make some progress in that direction.  

Thanks for all your help, team!  

Cheers!

-Peter

PeterDanielsCRB

I'm going to learn YAML, aren't I?