Azure SQL Server invoke-sqlcmd Login failed for user token-identified principal

Hoàng Lê Hiệp Minh 51

I'm trying setup Pipeline Release for automation to create User Managed Identity and add it to SQL, I have finished CREATE USER + ALTER ROLE with user AAD login to SQL Server on VM, but when I try setup on Release it stuck in auth with the error below (user AAD don't set MFA):

Anyone has ever met that before, please give me some method to resolve it. Thanks!

![enter image description here ]2

enter image description here

$ResourceGroup ='AAC-BYC-NNY'
$SQLServer = 'azcacaufd1devsql01.database.windows.net'
$Database = 'Core'
$MyIdentity = 'user15'

Create User Managed Identity

az identity create --resource-group $ResourceGroup --name $MyIdentity

add UMI to sql

$query = "

CREATE USER [$MyIdentity] FROM EXTERNAL PROVIDER;

ALTER ROLE db_datareader ADD MEMBER [$MyIdentity];

ALTER ROLE db_datawriter ADD MEMBER [$MyIdentity];

GRANT EXECUTE TO [$MyIdentity]

GO
"

write-output "Create DB Account named $MyIdentity"

$connectString="Data Source=tcp:$SQLServer,1433;Initial Catalog=$Ddatabase;Authentication=Active Directory Password;User ID='devops@hosting.com';Password='123456';Trusted_Connection=False;Encrypt=True;Connection Timeout=30"

Invoke-Sqlcmd -ConnectionString $connectString -Query $query

GeethaThatipatri-MSFT 27,102 Reputation points Microsoft Employee

2022-11-26T21:40:41.217+00:00

Hi, @Hoàng Lê Hiệp Minh Thanks for posting your question in the Microsoft Q&A forum.

Can you please confirm if you have set admin to the user as shown below
Hoàng Lê Hiệp Minh 51 Reputation points

2022-11-28T10:36:45.03+00:00

yeah, I did it before I asked on here, I ran the script above on Powershell VM and no issue happened maybe CI/CD needs more conditions or some setting. But I haven't found out yet, but I don't understand it running with the same script on the same agent that I have finished on local

2 answers

Alberto Morillo 32,886 Reputation points MVP

2022-11-24T19:44:23.84+00:00

Please make sure you create the user database first, you get connected to the user database after that (and you are not connected to the master database) and then you execute the CREATE USER statements. Please read this documentation for more details and requirements.
Please sign in to rate this answer.
Hoàng Lê Hiệp Minh 51 Reputation points

2022-11-28T14:20:07.953+00:00

I don't think that issue, because I ran the script on PowerShell in VM no issue happen, but I got the issue with Release run with the same script and the same VM
Sign in to comment
Hoàng Lê Hiệp Minh 51 Reputation points

2022-11-29T03:39:53.68+00:00

The script is correct, the root cause is the value variable I have setting incorrect.
Thanks!
Please sign in to rate this answer.
Eric Suen 1 Reputation point Microsoft Employee

2022-12-02T23:30:59.957+00:00

Hi @Hoàng Lê Hiệp Minh could you share more details what value variable was causing the trouble? I am hitting the exact same problem. Thanks.

Hoàng Lê Hiệp Minh 51 Reputation points

2022-12-03T12:29:07.533+00:00

In my case, it's about variable name User Manage Identity. You should write-out show for correct user want to add, and try it on PowerShell local make sure it's running as well.

Eric Suen 1 Reputation point Microsoft Employee

2022-12-04T18:08:59.147+00:00

Hi @Hoàng Lê Hiệp Minh could you add more details? I tried following variables and none of them worked for me.
"UserManagedIdentity='<MI Name>'"
"UserManageIdentity='<MI Name>'"
"User Managed Identity='<MI Name>'"
"User Manage Identity='<MI Name>'"

There is a chance the MI doesn't have enough permission, but I did add the MI as an admin to target SQL server and granted DB access following step 2 documented here (https://learn.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database?tabs=windowsclient%2Cef%2Cdotnet#grant-permissions-to-managed-identity), which is identical to what you described in your initial post. Anything else is missing?
CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
ALTER ROLE db_ddladmin ADD MEMBER [<identity-name>];
GO

Also, how did you figure out this variable needs to be added? I was thinking the problem might be related to a missing variable, but all it says in the documentation (https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps#-variable) is below. Is there a different wiki you followed? Thanks.

-Variable
Specifies, as a string array, a sqlcmd scripting variable for use in the sqlcmd script, and sets a value for the variable.

Hoàng Lê Hiệp Minh 51 Reputation points

2022-12-05T04:16:22.553+00:00

Step 1: I add a user AAD doesn't have set MFA to Admin SQL:

**Step 2: I connect to VM and login to SQL server with User AAD has to add before **

Step 3: I will open PowerShell on that VM to run script below if it can run as well I will setup on Azure Release

$DBServer = 'devsql01.database.windows.net'
$UserID = 'devops@hosting.ca'
$UserPassword = '4JfcSK'
$container = 'AAH'
$DatabaseSuffix = '15'
$UserIdentity = "a"+"user"+$DatabaseSuffix
$coredb = $container+"Core"+$DatabaseSuffix #name Database

$query = "

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE NAME = "+ "'" +$UserIdentity+ "'"+")

BEGIN

CREATE USER [$UserIdentity] FROM EXTERNAL PROVIDER;

END

ALTER ROLE db_datareader ADD MEMBER [$UserIdentity];

ALTER ROLE db_datawriter ADD MEMBER [$UserIdentity];

GRANT EXECUTE TO [$UserIdentity]

"

write-output "Create DB Account named $UserIdentity for $coredb"
$connectString="Data Source=tcp:$DBServer,1433;Initial Catalog=$coredb;Authentication=Active Directory Password;User ID=$UserID;Password=$UserPassword;Trusted_Connection=False;Encrypt=True;Connection Timeout=30"
Invoke-Sqlcmd -ConnectionString $connectString -Query $query
Sign in to comment