0

I wish to transfer data between two different databases on the same Azure SQL server (not managed instance). I have an admin account. When i goto setup Data Sync within the Azure portal i cannot see the tables after i created the Sync Group.

I followed the instructions here https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell&view=azuresql#create-contained-database-users-in-your-database-mapped-to-azure-ad-identities. The error i get is

SqlError Number:18456, Message: Login failed.

Ill use the following as examples

MasterDatabaseName = Master

HUBDatabaseName = DatabaseSend

ChildDatabaseName = DatabaseRecieve

Azure SQL Server Admin = [email protected]

However this is where i think the error lies, I login to the Azure Data Portal as [email protected] and i created the Server and databases with this company email login, however if i look in the overview inside the Azure Portal of the SQL server my server admin name is CloudS******** where * = random letters and numbers

If i do a assigned roles check on my databases using the following code

SELECT u.name AS UserName, u.type_desc AS UserType, r.name AS RoleName FROM sys.database_principals AS u LEFT JOIN sys.database_role_members AS rm ON rm.member_principal_id = u.principal_id LEFT JOIN sys.database_principals AS r ON r.principal_id = rm.role_principal_id WHERE u.type NOT IN('R', 'G') ORDER BY UserName , RoleName;

I get the Following for Master

UserName UserType RoleName
CloudS******** SQL_USER dbmanager
CloudS******** SQL_USER loginmanager
[email protected] EXTERNAL_GROUP dbmanager
[email protected] EXTERNAL_GROUP loginmanager
dbo SQL_User db_owner

DatabaseSend

UserName UserType RoleName
[email protected] EXTERNAL_USER db_ddladmin
[email protected] EXTERNAL_USER db_backupoperator
[email protected] EXTERNAL_USER db_accessadmin
[email protected] EXTERNAL_USER db_securityadmin
[email protected] EXTERNAL_USER db_owner
dbo SQL_User db_owner

DatabaseRecieve

UserName UserType RoleName
[email protected] EXTERNAL_USER db_ddladmin
[email protected] EXTERNAL_USER db_backupoperator
[email protected] EXTERNAL_USER db_accessadmin
[email protected] EXTERNAL_USER db_securityadmin
[email protected] EXTERNAL_USER db_owner
dbo SQL_User db_owner

If i try to add the user CloudS******** to DatabaseSend or DatabaseRecieve with CREATE USER i get the following error

The login already has an account under a different user name.

If i try to add the role dbmanager or loginmanager to [email protected] in DatabaseSend or DatabaseRecieve i get

Cannot alter the role 'dbmanger', because it does not exist or you do not have permission.

And if i try to add [email protected] to the role db_owner in Master i get the following error

Cannot alter the role 'db_owner', because it does not exist or you do not have permission.

Could some kind person please help this SQL Admin noob out? I think its because its not a uniform role across the databases, however i am unsure.

4
  • Check if the login [email protected] is the owner of the database. if so transfer the role to SA or a dedicated login for ownership and then create your user and add him to the correct roles.
    – Spörri
    Commented Feb 23, 2023 at 9:10
  • [email protected] is the owner of both databases and admin. Commented Feb 28, 2023 at 2:03
  • Change the owner on the database and try again: ALTER AUTHORIZATION ON DATABASE::<database> TO sa;
    – Spörri
    Commented Mar 1, 2023 at 10:50
  • Thanks Sporri I got it too work by turning off AAD Auth Only. Sucks kind of wanted the users to use MFA Commented Mar 2, 2023 at 1:54

1 Answer 1

0

I ended up having to turn off AAD Auth only and it worked.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.