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

Nick_Foster

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

Sergio R

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 R

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,

Nick_Foster

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 R

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,

Nick_Foster

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_Foster

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 R

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,

Nick_Foster

I'm assuming "Ignore user's permissions and role memberships" will apply to all users, not just the Windows ones. Is that correct.  

Nick_Foster

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 R

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,