Data migration between 2 databases - 1st using mixed mode and 2nd using AD SSO

[asilarow]

I have a database that is currently set up for mixed mode authentication.
I want to transfer its contents to a newly created database which has been set up with AD SSO authentication.

I do NOT want to do this manually (creating releases and transferring work queue contents in files, etc.), as I want to preserve all timestamps in the database (especially created and modified dates).

Can anyone share a SQL script that would do this without reverting the new DB to the old auth model?
Many thanks in advance.

------------------------------
Andrzej Silarow
Principal Consultant
Ignite IPA
Europe/London
------------------------------

[PabloSarabia]

Hi,

Did you try making a restore of the Data Base? You can create a back up for this, and restore it wherever you want.

For migrating the DB I think is the best.



Hope this helps you!

Bye 🙂

------------------------------
Pablo Sarabia
Architect
Altamira Assets Management
Madrid
634726270
------------------------------

[asilarow]

Hi Pablo,

Doing a restore will transfer the entire schema - including the mixed mode authentication settings.

What i want to do, is to move  transactional data only, retaining all the FK Constraints and dependencies.

------------------------------
Andrzej Silarow
Principal Consultant
Ignite IPA
Europe/London
------------------------------

[PabloSarabia]

Hi,

Exists a special function in SQL Server to create all the scripts. It's calls "Generate Scripts". To find this option.... right click on the database name >> Tasks >> Generate Scripts.

In the "Choose Objects" select all that you need. I mean in this case you need to move all.

Then... in the Set Scripting Options is important to choose schema and data. Just clic on "Advanced" and find "Type of data to script". Need to put here "Schema and Data".

Continue by click on Next and wait the results.


Hope this helps you!! If this solves the issue, remember mark this as the best answer 🙂


Bye 🙂

------------------------------
Pablo Sarabia
Architect
Altamira Assets Management
Madrid
634726270
------------------------------

[asilarow · Accepted solution]

Hi,

I've now found a solution for this...

Step 1. - Configure the security groups within Active Directory to match the BP roles you need 
Step 2.  - Create a new DB and set it up to use single-authentication environment
Step 3. - Provide the relevant Active Directory information in the setup wizard
Step 4. - Connect to the newly created DB using SSMS
Step 5. - Restore data from old DB to new DB*
Step 6. - Edit the below tables/columns in new DB:

[dbo].[BPAUserRole].[ssogroup] <-- --POPULATE WITH sso ids
[dbo].[BPASysConfig].[ActiveDirectoryProvider]  <--PUT IN THE ACTIVE DIRECTORY DOMAIN INFO
[dbo].[BPASysConfig],[EnableMappedActiveDirectoryAuth] <--CHANGE TO FALSE
Step 7. Commit changes
Step 8. Restart the app server service

*I recommend using your favorite tool or sql scripts to do this


------------------------------
Andrzej Silarow
Principal Consultant
Ignite IPA
Europe/London
------------------------------