This setup must be done on the SQL Server server.
Download the GnuPG installer from here.
Run the setup. The defaults answers are fine. Make note of the Destination Folder.
Open a command prompt.
Change Directory to the Destination Directory GnuPG was installed to.
In the command prompt window do the following step:
Run SettingsMaint.
Select Manage Other Control Tables.
Select PositivePay Control tab.
Set the GPGPLocation setting to the Destination Folder noted from the installation of GnuPG. The value should also include the gpg.exe (ie. c:\Program Files\GNU\GnuPG\gpg.exe)
Set the PGPRecipient setting to the Name noted from the import of the bank's PGP public key. (ie. Suntrust TMG)
Set the ServerFileOutputPath. This MUST be a physical location on the server. This location should also be accessible from the network by the person responsible for sending the positive pay file to the bank. Logically this location would be a directory in the public folder. So if the public folder on your server in d:\public, create a directory under that folder named PositivePay and set the setting the d:\public\PositivePay. Please note that this setting CANNOT be p:\PositivePay where p: is the mapped drive.
Set the OutputFileName setting to the file name to be used for the output positive pay file. This setting should include the extension for the file. (ie. PositivePay.txt)
Set the UseGnuPG setting to 1 to enable using positive pay file encryption.
Close SettingsMaint.
The next steps must be done from SQL Server Management Studio:
Positive Pay file encryption should now be setup and the next time the PositivePay program is run an encrypted postive pay file will be generated in the output directory with the filename and an additional extension of gpg. The .gpg file is the one that should be sent to the bank.
Download the GnuPG installer from here.
Run the setup. The defaults answers are fine. Make note of the Destination Folder.
Open a command prompt.
Change Directory to the Destination Directory GnuPG was installed to.
In the command prompt window do the following step:
Type gpg --gen-keyNow the positive pay settings must be set.
Select 1 (DSA and Elgamal (default))
Press enter to accept the default for the keysize
Press enter to accept the default expiration of never (0)
Type Y and press enter for Is this correct.
Enter the real name for your certificate. This should be the name of the trusteeship (ie. John Doe, Chapter 13 Trustee)
Enter a valid email address.
Enter a comment for the certificate. This can be anything.
Enter O for Okay.
Enter a passphrase. This should be something complex and secret and MUST be retained by the trusteeship.
Reneter the same passphrase.
The key will now generate and will it is working you are encouraged to move the mouse around to help with the randomness.
Once completed and the prompt is available again the bank's public key must be imported.
Type gpg --import filename (filename is the full path and file name of the file received from the bank with their public key inside)
You need to note the name of the public key from the import. In the following example the name is Suntrust TMG
To keep gpg happy that the key just imported is really ok. it needs to be signed.
Type gpg --edit-key (and the name of the key from the above step) (ie. gpg --edit-key "Suntrust TMG")
A prompt Comand) should appear
Type sign and press enter.
Enter Y for Really Sign?
Enter the passphrase used when you created the Trustee's key.
This will sign the certificate.
Type quit and press enter. This will take you back to the normal command prompt.
Type quit to close the command prompt.
Enter Y to Save the changes.
GnuPG is now setup.
Run SettingsMaint.
Select Manage Other Control Tables.
Select PositivePay Control tab.
Set the GPGPLocation setting to the Destination Folder noted from the installation of GnuPG. The value should also include the gpg.exe (ie. c:\Program Files\GNU\GnuPG\gpg.exe)
Set the PGPRecipient setting to the Name noted from the import of the bank's PGP public key. (ie. Suntrust TMG)
Set the ServerFileOutputPath. This MUST be a physical location on the server. This location should also be accessible from the network by the person responsible for sending the positive pay file to the bank. Logically this location would be a directory in the public folder. So if the public folder on your server in d:\public, create a directory under that folder named PositivePay and set the setting the d:\public\PositivePay. Please note that this setting CANNOT be p:\PositivePay where p: is the mapped drive.
Set the OutputFileName setting to the file name to be used for the output positive pay file. This setting should include the extension for the file. (ie. PositivePay.txt)
Set the UseGnuPG setting to 1 to enable using positive pay file encryption.
Close SettingsMaint.
The next steps must be done from SQL Server Management Studio:
use master
go
sp_configure 'clr enabled', 1
go
reconfigure
use master
go
grant external access assembly to [sql login] (sql login should be the valid sql user from the logins page and should be the user the will be doing the positive pay)
grant unsafe assembly to [sql login]
alter database ch1213Data set trustworthy on (replace Ch1213Data with the correct database name if the database is other than Ch1213Data
exec sp_changedbowner @loginame = 'sa'
use Ch1213Data
go
create assembly [Trustwin.PositivePaySQL]
from 'D:\PositivePay\PositivePaySQL.dll' (replace d:\positivePay\ with the correct location of the PositivePaySQL.dll)
with permission_set = unsafe
go
create proc procPositivePayPGP
as
external name [Trustwin.PositivePaySQL].[PositivePay].[PositivePayPGP]
Positive Pay file encryption should now be setup and the next time the PositivePay program is run an encrypted postive pay file will be generated in the output directory with the filename and an additional extension of gpg. The .gpg file is the one that should be sent to the bank.