SolidQ.BCP
SolidQ.BCP is a utility to copy data efficiently between different SQL Server instances (including SQL Azure instances).
This tool is a replacement of the bcp.exe program that comes with SQL Server and has the same performance.
The benefits of using this tool are:
- The ability to encrypt and use multiple passwords
- The ability to use an XML file for communicating configuration options

An example of a typical xml file is as follows:


<?xml version="1.0" encoding="utf-8" ?>
<SolidQBCP ContinueOnError ="False"
BatchSize="4096"
NotifyAfter="10000"
Hints="TABLOCK,CHECK_CONSTRAINTS"
AutomaticCollationResolution=”true”
TimeOut="0">
<credentials forcecolumnmapping="true">
<source
SqlInstance ="10.1.1.99\sql2008r2"
DatabaseName ="DWrendimiento"
IntegratedSecurity="false"
User="sa"
IsPasswordEncrypted="false"
Password ="password"
UseQueryAsSource="true"
TableName="snapshots.performancecountervalues"
Query="select * from snapshots.performancecountervalues"
ColumnNames="performancecounterinstanceid,snapshotid,collectiontime,formattedvalue,rawvaluefirst,rawvaluesecond"
/>

<destination
SqlInstance ="(local)\sql2008r2_2"
DatabaseName ="borrame"
IntegratedSecurity="true"
User="enriquecatala"
IsPasswordEncrypted="false"
Password ="n5SoiTel+ulzJuffz83IUkBA=="
TableName="dbo.performancecountervalues"
ColumnNames="performancecounterinstanceid,snapshotid2,collectiontime,formattedvalue,rawvaluefirst,rawvaluesecond2"
/>

<key>
<!CDATA[example: D zx3=v-Pd|}gq?'\8Qs{i}1DypEG[+'0o7kf+h,Iz@{g2V+w="sjg9\o!H0&T8 y7C0gb*8J4.ec-I'f|&>:.R}9N797@TGR,8h)m(b00='_~?>S1A\@]]>
</key>
</credentials>
</SolidQBCP>


Configuration parameters
• BatchSize
• NotifyAfter
• Hints
• AutomaticCollationResolution
If this flag is active, the app detects the destination collation and writes the select against the source table with the collate clause to avoid mismatch collation resolutions.
This flag only takes effect when UseQueryAsSource=”false”
• TimeOut
• Forcecolumnmapping
• SqlInstance
• DatabaseName
• IntegratedSecurity
• User
• IsPasswordEncrypted
This attribute indicates if the Password attribute is encrypted or is in plain text
• Password
Password of the sql server instance user. The password could be encrypted and the encryption process will be discussed later in this document.
• UseQueryAsSource
This flag can accept true or false values and specifies if the app should use the “Query” attribute as a source.
• TableName
• Query
• ColumnNames
• Key node
This internal xml node includes a CDATA node with the key password recovery obfuscated internally. It´s generation will be discussed later in this document

SolidQ.BCP input arguments
There are 4 execution modes described in the following syntax:
SolidQ.BCP.exe
   [    File.xml
      | -?
      | -GenerateKeyEncryption -Output pathFile
      | -EncryptPassword password -Key pathFileKey -Output pathFileOutput
   ]

1. If we don’t pass arguments, the program will try to read SolidQBCPConfig.xml file from the directory where the executable SolidQ.BCP.exe resides and to perform the operations specified by the XML.
2. If we pass a single argument, this must be a valid SolidQ.BCP configuration .xml file.
3. In another case, we want to generate encryption keys or encrypt a text string, to configure the password encryption, which requires an encrypted key and an encryption key. This process is detailed later in this document.

Password encryption
As we described earlier in this document, we can write the SQL password encrypted or in plain text.
We must write the IsPasswordEncrypted attribute accordingly to what we want.
If we want to put the password encrypted, we must follow this two steps:

1. Creation of a randomly generated encryption key:

The process to create a 256 byte random encryption key is:
SolidQ.BCP.exe -GenerateKeyEncryption -Output pathFile

pathFile must be the file on which we want the encryption key will be stored (for example c:\key.txt)


2. Encryption of the password
After the encryption key has been generated, the password can be encrypted thus:

SolidQ.BCP.exe –EncryptPassword password –Key pathFileKey
                  -Output pathFilePasswordOutput

Where “password” is the password string, “pathFileKey” is the file previously generated (c:\key.txt for example), and “pathFilePasswordOutput” is the file in which we want the encrypted password to be stored after creation
The final step is to augment the SolidQBCPConfiguration.xml file with the values within the generated files (key = content of  “pathFileKey”  and password = contents of “pathFilePasswordOutput” fields).
The use of an XML file for configuration allows us to generate multiple passwords in this fashion but you must use the same encryption key to encrypt all the passwords for a given configuration.



Last edited May 19, 2011 at 9:50 AM by enriqueatala, version 2

Comments

No comments yet.