How can we help you today? How can we help you today?

SQL Data Compare CLI in Azure Pipeline

Greetings and Happy March!  

We are trying to use SQL Data Compare (V16) in an Azure pipeline and getting this error for connecting to target database. 

For context, we are using DbaTools.io to create a clone of the production database and it sets the  owner of the cloned db as the service account.  It also adds a user to the db as the service account and gives it db_owner permissions.

##[error]Error: Cannot open database "DBATestPRDBld" requested by the login. The login

failed. Login failed for user '[DomanName]\svc.account'.

Tried using the flag /ad2 with no luck.  Any advice would be appreciated.


David W
0

Comments

1 comment

Sort by
  • Jon Kirkwood
    Official comment

    Hi David W 

    Thank you for reaching out on the Redgate forums regarding your Data Compare login error.

    It may be worth checking whether you have a SID mismatch on the cloned database. 

    When DbaTools clones a DB, the internal user mappings may not carry over cleanly, which can leave the service account as an orphaned user — even if db_owner was granted.

    Run this to check:

    USE [DBATestPRDBld]; EXEC sp_change_users_login 'Report';

    If the service account shows up, try re-mapping it:

    ALTER USER [svc.account] WITH LOGIN = [DomainName\svc.account];

    A couple of other things to confirm while you're investigating:

    • /ad2 flag - make sure it's paired correctly with /s2, /db2, and your auth parameters
    • Azure SQL vs. SQL Server VM. If this is Azure SQL (PaaS), Windows/AD auth may not behave as expected and could need a different approach
    Jon Kirkwood

Add comment

Please sign in to leave a comment.