Oracle GoldenGate for MS SQL Server: Managing the Credential Store
Microsoft SQL Server is a popular database platform that needs a robust and feature rich replication product that can be used in both a homogeneous and heterogeneous configuration. I have been involved in a number of SQL Server to SQL Server and SQL Server to Oracle replication projects using Oracle GoldenGate. Oracle GoldenGate works well with MS SQL Server and the new OGG 12.2 version offers new features that enhance that support.
In the next few months I will be presenting a series of blogs on how to best implement Oracle GoldenGate in a MS SQL Server environment and how to take advantage of its features. In this entry I will focus on SQL Server user connectivity.
The Oracle Credential Store is a great way to avoid having to embed plain text passwords in parameter files and obey files by storing the userid and password into a combined useridalias within an Oracle Wallet, however, there are a few issues that can be problematic.
The Credential Store is created and maintained via GGSCI using the ADD CREDENTIALSTORE command. Once the credential store is created, the ALTER CREDENTIALSTORE command is used to create the userid aliases as shown here:
Once the credential has been added to the Credential Store and the alias is available, the parameters USERID and PASSWORD are replaced by USERIDALIAS. The password never needs to be visible in parameter or obey files again.
So, what does this have to do with Windows and SQL Server? Due to Windows security, the Credential Store can only be used by the owner, in this case Administrator, who first creates it.
In this example, user ewhalen logs into the system and runs GGSCI. The ewhalen user attempts to access the credential store.
Any attempt to access the Credential Store that was created by another user will be met by the following error:
*** Could not open error log ggserr.log (error 13,Permission denied) ***
ERROR: Unable to open credential store. Error code 28,759..
In order for the Credential Store to be accessible by other users, permissions to the Credential Store wallet file must be modified.
To do this, navigate to the folder where GoldenGate was installed and open the dircrd folder.
Right click on cwallet.sso and select Properties.
Click on the Security tab.
In order to add additional access, click the Edit… button.
Click Add… to enable additional access to the user.
Enter the username and click Check Names in order to validate the user.
Click OK to add the user.
By default, Read & execute and Read are enabled. If you want this user to be able to add credentials too, then Modify must be added. When you are satisfied, click OK.
Click OK to apply the changes.
Click OK to exit the cwallet.sso properties screen.
Now the access to the Credential Store is allowed for other users as well as the one who first created it:
Access to the credential store is accessible by the ewhalen user now and there was no need to exit and re-enter GGSCI.
I recommend using the Credential Store in order to eliminate the need to use passwords in obey and parameter files. This improves security and is easy to use.