How do I deploy a database into a different Active Directory domain?

So I have been charged with setting up a new dev/test environment for our development team.  For whatever reason this environment is not in the same Active Directory domain as the production environment (or the existing dev/test servers).

When I've tried running the deployment pipeline it fails as it can't validate the Windows users listed on the database as it can't see the production domain.

For example
"Windows NT user or group 'MyDomain\JohnSmith' not found. Check the name again."

Is there any way of substituting the domain on users?  Going forward this is going to be super awkward if the development team add Windows users from the Test environment and then the production deployment fails.

Cheers,
Nick

Tagged:

Best Answer

  • Sergio RSergio R Posts: 610 Rose Gold 5
    Answer ✓
    Yes, that's correct, there isn't an option to just the ignore the permission to Windows users.
    This option supports what we consider to be best practice when allocation permissions in databases that are part of a DevOps process: you should allocate permissions to roles rather than users and then at the various environments you can have environment specific users allocated to those roles.
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools

Answers

  • Sergio RSergio R Posts: 610 Rose Gold 5
    Hi Nick,

    If I understand correctly this is happening because you have Windows users on your database schema that are not valid in the SQL Server instance that you are deploying to?

    Are you using a SQL Change Automation or SQL Source Control project?

    Kind Regards,
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
  • Nick_FosterNick_Foster Posts: 17 Bronze 2
    Hi Sergio,

    Yes, our new dev/test environment is being built in it's own AD Domain.  This gives us problems in that the Windows users on the production database will be like Prod\JohnSmith and in the test database will be Test\JohnSmith.

    We are using SQL Change Automation projects.
  • Sergio RSergio R Posts: 610 Rose Gold 5
    Hi Nick,

    We recommend that when developing databases that are going to be part of a DevOps process is to allocate permissions to roles only.
    We also recommend using a filter that excludes users from import.
    You can then keep environment specific users in the databases.
    Since you have already imported those users into your migration scripts, the best option now is to edit the migration scripts to remove them.

    I hope this helps,

    Kind Regards,
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
  • Nick_FosterNick_Foster Posts: 17 Bronze 2
    Hi Sergio,

    Thanks for the suggestion.  Is there a straight-forward way of excluding only Windows users from the Change Automation project? 

    I would really like to keep the SQL logins in source control so I can keep tabs easily on permissions.  For example on the primary database I'm working on there are 10 Windows users and 40 sql logins to keep track of.

    Cheers,
    Nick
  • Nick_FosterNick_Foster Posts: 17 Bronze 2
    So I've been playing with the filters in SQL Compare 14 in order to create something I can use in the Change Automation projects.

    When I exclude users from the comparison they are still included in the role definitions, for example
    <div>-- Role</div><div>CREATE ROLE [EbCollSqlgen]</div><div>AUTHORIZATION [dbo]</div><div>GO</div><div>ALTER ROLE [EbCollSqlgen] ADD MEMBER [ATTENDA-HQ\EbCollSqlgen]</div><div>GO
    </div>
    Do these not get validated then?  Will the deploy still fail if the role membership includes Windows users not in the current domain?
  • Sergio RSergio R Posts: 610 Rose Gold 5
    Hi Nick,

    Regarding your first question, you could for example filter all out all the objects that contain "\" since only Windows users will have a slash in the name.

    Regarding the second question, Filters only exclude the user objects, they do not eliminate permissions or role memberships, to deal with that you can use Comparison Options.

    For example, to prevent SQL Change Automation from attempting to add user's role membership, you should set the "Ignore user's permissions and role memberships" SQL Compare option to true in your .sqlproj file, as explained in the following document:

    https://documentation.red-gate.com/sca/developing-databases/concepts/advanced-concepts/comparison-and-script-generation-options

    Kind Regards,
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
  • Nick_FosterNick_Foster Posts: 17 Bronze 2
    I'm assuming "Ignore user's permissions and role memberships" will apply to all users, not just the Windows ones. Is that correct.  
  • Nick_FosterNick_Foster Posts: 17 Bronze 2
    Thanks for the help Sergio, most apprciated.  It's a shame I can't just ignore the Windows users, I would love to control the SQL logins with the Change Automation project.

    I don't suppose I could raise it as a feature request for future versions?   :)
  • Sergio RSergio R Posts: 610 Rose Gold 5
    Hi Nick,

    If you wish you can suggest that on our Uservoice forum at https://redgate.uservoice.com/forums/267000-sql-change-automation

    Kind Regards,
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
Sign In or Register to comment.