Automate the migration of the DocsVision database

Preamble

It would seem - if the system is closed, then there should be comfortable tools? Well, or at least an API for writing these handy tools on your own.

Unfortunately, usually everything is bad: the tools are there, but so uncomfortable that there is no happiness from their presence. It is necessary to get out.
')
So, given - the system DocsVision (hereinafter DV) version 4.5 of the SR1. And the task is to move the database from one server to another (for example, customers bought a new one). The problem that arises in this case is exactly one.

The rights to objects for local accounts when transferring the base to a new location will turn into a pumpkin. And since the standard DV groups are local, problems cannot be avoided.

Who is interested - please under the cat.

Standard tools

To begin, consider the tool that DocsVision offers us for this purpose.
It is described by the link dvprofessionals.blogspot.com/2010/03/blog-post_22.html

It would seem that this is what we need, but only at first glance.



As can be seen from the screenshot - only one record at a time, which, even in the case of a couple of dozen local accounts, is very inconvenient. And, based on real experience - clients without a domain are found at least as often as clients with a domain. And in this case - all users are set up on the server locally. And with your hands to process a hundred or two uchetok - does not smile at all.

Therefore, it was decided to write something of their own.

Study of the database structure

DocsVision is a closed platform and is documented only within the framework provided by the API developers. But, some information on the Internet still walks, and it was enough.

The main tables of interest are dvsys_instances and dvsys_security . The first contains information about all the objects of the system (cards, folders, directories, files, etc.), the second. Without further ado, stores security descriptors. The structure of the tables is given below.



Tables are linked by the dvsys_instances.SDID and dvsys_security.ID fields . Also, it should be noted that the number of records in these tables varies greatly, thanks to the inheritance of rights. For example, for one of the clients, these values ​​are 277571 and 6639, respectively.

So, the task has been set - it is necessary to go through all the rows of the second table, decode the security descriptors, replace the old SIDs for the necessary entries with new ones and pre-encode them, write them back. Well, let's get started.

Implementation of the conceived

PowerShell was chosen as the development language, since most of the necessary tasks can be solved easily and naturally with its help.

For ease of use, all settings were moved to a separate file. These include account matching table and database access settings.

$SIDReplacement = @{ "S-1-5-21-2179127525-659978549-3108502893-1019" = "a31003\akushsky"; #akushsky "S-1-5-21-2179127525-659978549-3108502893-1016" = "a31003\ASPNET"; #ASPNET "S-1-5-21-2179127525-659978549-3108502893-1008" = "a31003\DocsVision Administrators"; #DV Administrators "S-1-5-21-2179127525-659978549-3108502893-1010" = "a31003\DocsVision Power Users"; #DV Power Users "S-1-5-21-2179127525-659978549-3108502893-1012" = "a31003\DocsVision Users"; #DV Users "S-1-5-21-2179127525-659978549-3108502893-1026" = "a31003\dobriy"; #DV Editors } $SQLServerName = "a31003" $SQLDatabaseName = "DV-BASE" 

It seemed to me more logical to run the script on the target server, so the old accounts are present as SIDs (you can also use PowerShell to get them, Google knows about this). And we will indicate the new ones in the form of accounts and their SIDs will be obtained in the process of script operation.

Also, as it turned out during the development process, PowerShell does not know how to create instances of generic objects, so for this purpose, Google had to get the New-GenericObject function from the depths of Google. I will not give her code, at the end of the article there will be a link to the repository - everything is there.

For convenience, when the script is running, a log is displayed in the console, so it was decided to create a structure for more convenient accumulation of information.

 # Struct for log add-type @" public struct DVObject { public string ID; public string Description; public string Accounts; } "@ 

For the most complete logging, the query, of course, returns much more information than it could, but - beauty requires sacrifice.

 SELECT I.InstanceID, I.Description, S.ID, S.SecurityDesc FROM [DV-BASE].[dbo].[dvsys_security] S LEFT JOIN [DV-BASE].[dbo].[dvsys_instances] I ON I.SDID = S.ID 

Therefore, in order not to process the same descriptor many times - identifiers are recorded.

 # Replace SDDL in each row only once $IDList = New-Object System.Collections.Generic.HashSet[Guid] ... # We need only one SQL-request for each ID if ($IDList.Contains($row["ID"])) {continue} else {$isOk = $IDList.Add($row["ID"])} 

And then the fun begins:

1. The resulting SecurityDesc is a Base64 encoded binary sequence of characters. And, to replace SIDs, we need to get an SDDL string. The format description is on MSDN . And now the same thing, just with the code:
   
   

     # Convert SDDL from Base64 to binary form $ObjectWithSDDL = ([wmiclass]"Win32_SecurityDescriptorHelper") .BinarySDToSDDL([System.Convert]::FromBase64String($row["SecurityDesc"])) $sddl = $ObjectWithSDDL.SDDL 

   
   
2. Next, extracting the SIDs from the SDDL lines using a regular schedule, we check them according to the correspondence table and, in case of coincidence, we substitute them.
   
   

     # Match all SIDs and replace [regex]::Matches($sddl,"(S(-\d+){2,8})") | sort index -desc | % { if ($SIDReplacement.ContainsKey($_.ToString())) { # Translate NT account name to SID $objUser = New-Object System.Security.Principal.NTAccount($SIDReplacement[$_.ToString()]) $strSID = $objUser.Translate([System.Security.Principal.SecurityIdentifier]) # Replace it in SDDL $sddl = $sddl.Remove($_.index,$_.length) $sddl = $sddl.Insert($_.index,$strSID.Value) # Add to list of current object accounts $dvobject.Accounts += $SIDReplacement[$_.ToString()] $dvobject.Accounts += "`n" # Set replace completed $replaceComplete = $true } } 

   
   
3. If a replacement has been made, then it is necessary to perform the reverse actions and record this change in the database:
   

     if ($replaceComplete) { # Add current info object to list $DVObjectList.Add($dvobject) $binarySDDL = ([wmiclass]"Win32_SecurityDescriptorHelper").SDDLToBinarySD($sddl).BinarySD $ret = [System.Convert]::ToBase64String($binarySDDL) ##### Update database ##### # Update query for currently replaced SDDL $SqlQuery = "UPDATE [dbo].[dvsys_security] SET Hash = '" + $binarySDDL.GetHashCode() + "', SecurityDesc = '" + $ret + "' WHERE ID = CONVERT(uniqueidentifier, '" + $row["ID"] + "')" # Attach query to command $UpdateSqlCmd.CommandText = $SqlQuery # Execute update query if ($UpdateSqlCmd.ExecuteNonQuery()) { Write-host "Update true for ID: " $row["ID"] } else { Write-host "Update false for ID: " $row["ID"] } } 

   
   

Thus, a universal mechanism was obtained for any migration - between the customer’s servers, between the development server and the customer, and how else?

In the end, as promised: a link to the repository with the described code.